Excel 2010 Estrapolare Solo Cognomi Ripetuti

[Ercolino22]

Salve.

Ho due colonne:

A campo cognome

B campo nome

Sono almeno 2000 record

In questi campi i cognomi si ripetono diverse volte

ovvero il sig. Rossi Mario si ripete almeno 20 volte

il sig, Bianchi Lucio si ripete almeno 15 volte

mentre la sig.ra Verdi Adriana appare 1 sola volta nel database

Vorrei, se possibile, estrapolare solo i cognomi e i nomi della stessa persona. che si ripetono più di 1 volta.

(E' vero che ci potrebbero essere diversi Bianchi Mario ma non è detto che siano le stesse persone, ma questo non mi importa, in seguito potrei fare un controllo su Codice fiscale).

Mentre le persone che compaiono 1 volta soltanto non dovrebbero essere estrapolate.

Mi potete aiutare a trovare la soluzione al caso ??

Grazie.

[blintz]

ciao

ti allego questa formula vedi un po' se fa al tuo caso

=SE.ERRORE(INDICE($B$2:$B$1000;PICCOLO(SE(VAL.NUMERO(CONFRONTA(RIF.RIGA($A$2:$A$1000)-1;CONFRONTA($B$2:$B$1000;$B$2:$B$1000;0);0));CONFRONTA($B$2:$B$1000;$B$2:$B$1000;0);"");RIGHE($A$1:$A1)));"")

naturalmente la devi adattare al tuo foglio

--LA FORMULA FA RIFERIMENTO ALLA COLONNA B
COPIARE FORMULA NELLA PRIMA CELLA DELLA COLONNA
CONFERMARE CON "CTRL+SHIFT(MAIUSC)+INVIO"
NB SHIFT è IL TASTO SOPRA CTRL
SELEZIONI LA PRIMA CELLA E TRASCINI VERSO IL BASSO

fonte:

http://freeforumzone.leonardo.it/discussione.aspx?idd=10590931

ciao

[Ercolino22]

Grazie a tutti.

Ho provato la formula di Blintz.

Ho creato una serie di cognomi in colonna A a partire da A1

A fianco ho creato dei nomi.

Ogni tanto ho ripetuto lo stesso cognome, ovviamente anche lo stesso nome, (per far capire che potrebbe essere la stessa persona)

Ho preso la formula di Blintz e l'ho copiata nella cella C1 e poi replicata fino in fondo, in corripondenza dell'ultimo record.

Purtroppo non funziona, tant'è vero che in corrispondenza dei cognomi e nomi ripetuti (stessa persona) ....... non vedo alcun risultato.

Scusa ma come dovrebbe funzionare ?

[Livio Orsini]

Ae hai un po' di conoscenza di VB non è difficile risolvere il problema con una macro in VB. Premetto che, essendo un algoritmo simil sorting (ordinamento) può anche richiedere un certo tempo di esecuzione, dipende dal numero di record da esplorare.

E' un algoritmo di tipo euristico. In pratica la macro andrà a leggere le 2 celle Nome, cognome, della prima riga, poi esegue la comparazione delle stringhe per tutte le righe successive marcando le righe uguali e memorizzando la stringa in un array. Ripeterà la ricerca "n" volte quante sono le righe -1.

[Ercolino22]

Grazie Livio, Mi spieghi come si fa?

[JumpMan]

E se usi una tabella pivot ?

[JumpMan]

Puoi creare un nuovo foglio (io l'ho nominato Ricerca duplicati)

In A1 scrivi: NomeCognome

In B1 scrivi: CF (codice fiscale)

In A2 scrivi: =Anagrafica!A2&Anagrafica!B2

In B2 scrivi: =Anagrafica!C2

Ovviamente queste ultime 2 formule le devi modificare per puntare al nome/cognome e al codice fiscale

Poi copia A2:B2 nelle successive righe

Poi crea una tabella pivot con la procedura guidata, nello spazio Riga trascina NomeCognome e CF, nello spazio Dati trascina NomeCognome .

Risultato:

[blintz]

ciao

@Ercolino22

vediamo di rispondere ai tuoi quesiti

Ho creato una serie di cognomi in colonna A a partire da A1

A fianco ho creato dei nomi.

Ogni tanto ho ripetuto lo stesso cognome, ovviamente anche lo stesso nome, (per far capire che potrebbe essere la stessa persona)

Ho preso la formula di Blintz e l'ho copiata nella cella C1 e poi replicata fino in fondo

ok

tu nella colonna A hai messo i Cognomi

nella colonna B i Nomi

nella colonna C la mia formula

bene a prescindere dal fatto che dovevi iniziare dal Nome (A) e poi Cognome non cambia nulla se hai copiato bene la formula lei ti dava i nomi univoci della colonna B

riproviamo

A B C

alfa gigi gigi

beta gigi topo

uno topo gigio

due gigio talpa

tre topo

quattro talpa

Questa formula ti tira fuori i doppioni

=SE.ERRORE(INDICE($B$2:$B$1000;PICCOLO(SE(VAL.NUMERO(CONFRONTA(RIF.RIGA($A$2:$A$1000)-1;CONFRONTA($B$2:$B$1000;$B$2:$B$1000;0);0));CONFRONTA($B$2:$B$1000;$B$2:$B$1000;0);"");RIGHE($A$1:$A1)));"")

--------------------------

se vuoi una cosa piu sofisticata devi

prima spiegare meglio il problema

allegare un file di quello che vorresti fare

......

se sai fare da solo ascolta Livio ti ha dato una dritta

altrimenti chiedi

ciao

[blintz]

sono curioso di sapere cosa vuole combinare Ercolino22

.......vedremo

[JumpMan]

ecco la pappa pronta sulla base di quanto ha detto Livio:

Private Sub CommandButton1_Click()
    FoglioDati = "Anagrafica"    'Nome foglio con dati anagrafici
    Col_Nome = 1 'Colonna Nome
    Col_Cognome = 2 'Colonna Cognome
    Col_CF = 3  'Colonna codice fiscale
    Dim Nome(9999) As String
    Dim Cognome(9999) As String
    Dim CF(9999) As String
    
    ' salva in un array nomi con indice = a nr.riga
    ' salva anche il codice fiscale corrispondente in un altro array
    Row = 1
    Do
        Row = Row + 1
        Nome(Row) = Sheets(FoglioDati).Cells(Row, Col_Nome)
        Cognome(Row) = Sheets(FoglioDati).Cells(Row, Col_Cognome)
        CF(Row) = Sheets(FoglioDati).Cells(Row, Col_CF)
    Loop While Nome(Row) <> ""
    
    ' Cancella dati precedenti sul foglio
    Columns("A:C").Select
    Selection.ClearContents
    Cells(1, 1) = "Nome"
    Cells(1, 2) = "Cognome"
    Cells(1, 3) = "CF"
    Cells(1, 4) = "Ripetizioni"
    
    
    ' Scansiona l'array alla ricerca di duplicati, se li trova li scrive nel foglio
    LastRow = Row
    Row = 2
    ResultRow = 2
    Do
        Cnt = 1
        Nome_1 = Nome(Row)
        Cognome_1 = Cognome(Row)
        CF_1 = CF(Row)
        For i = Row + 1 To LastRow
            If Nome(i) = Nome_1 And Cognome(i) = Cognome_1 And CF(i) = CF_1 Then Cnt = Cnt + 1
        Next i
        If Cnt > 1 Then
            Cells(ResultRow, 1) = Nome_1
            Cells(ResultRow, 2) = Cognome_1
            Cells(ResultRow, 3) = CF_1
            Cells(ResultRow, 4) = Cnt
            ResultRow = ResultRow + 1
        End If
        Row = Row + 1
    Loop While Row <= LastRow
    
End Sub

Devi mettere un tasto su un foglio vuoto e incollarci dentro il codice, ovviamente dovrai modificare le prime 3 righe di questo codice per adattarle al tuo xls.

Notte

[Ercolino22]

Grazie amici.

Le risposte sono varie e "solleticanti".

Datemi il tempo di provarle tutte (visto che qui in ufficio non ho nemmeno il tempo di distrarmi) e poi vi farò sapere.

Grazie ancora.

[JumpMan]

Il codice all'#11 ha un piccolo bug... se ho tempo te lo sistemo

[Ercolino22]

Salve

Ragazzi non va

1) ho un database di 10.000 e più record con 70 campi

non so se una tabella pivot andrebbe bene

In sostanza vale quello che ho detto prima per la ricerca dei cognomi e dei nomi, ma deve funzionare così:

dopo aver ordinato tutto il db in base alla colonna cognomi, e come secondo livello la colonna nomi, (lasciamo per adesso il C.F.)

ovviamente mi ritrovo con il primo cognome "aaaaa" e il "nome" corrispondente.

(Devo far notare che ho la colonna "cognomi" e poi la colonna "nomi" quindi non posso metterli in un unica colonna)

la formula o la funzione, dovrebbe nascondere tutta la riga (ricorda che ogni record è formato da 70 campi) dei cognomi singoli e far rimanere così solo i cognomi ripetuti, ovviamente della stessa persona. (Se poi dovesse capitare che un certo Bianchi Mario appare più di una volta ma non è la stessa persona....... per adesso non ha importanza)

probabilmente la funzione deve essere posta al di fuori del db, visto che devo nascondere le righe singole

Intanto ho riprovato la funzione di Blintz ma non funziona.

ho copiato tutta colonna "cognomi" dal db (che si trova in colonna AF) in altro foglio e quindi in colonna A

così ho fatto per i nomi

in colonna C ho messo la funzione che ho replicato fino in fondo al db

Scusa Blintz ma non capisco perché non funziona ! sono mortificato.

Ragazzi questo è quanto, Datemi una mano se potete.

Grazie ancora.

(p.s. a disposizione per altri aggiornamenti)

[JumpMan]

Devo far notare che ho la colonna "cognomi" e poi la colonna "nomi" quindi non posso metterli in un unica colonna)

Questo è vero, ma se hai letto bene l'#8 vedi che li mette insieme in automatico in un nuovo foglio...

Credo che con così tanti record ti convenga usare il codice VBA, ma non l'hai provato ?

Al comando nascondi non ci avevo pensato, è molto facile nascondere tutte le righe e con il VBA scoprire quelle duplicate, posso modificarti il codice, ma non ora, ciao

Dimmi come si chiama il tuo foglio con i nomi, in quale colonna si trova il nome e in quale colonna il cognome...

Modificato: 9 ottobre 2013 da JumpMan

[Livio Orsini]

1) ho un database di 10.000 e più record con 70 campi

1. Che il database abbia 10 o 10.000 record l'algoritmo è e deve essere il medesimo. E' un algoritmo di ricerca ed ordinamento. Il tempo di esecuzione....è quello che necessita. Sempre quando si eseguono algoritmi ricorsivi il tempo è proporzionale al numero delle reiterazioni. Alla fine quulunque sia il metodo "apparente" si dovranno confrontare tutti i record più volte.
2. Le due celle di ogni riga sono controllate contemporaneamente; al riconoscimento delle due eguaglianze (if (A= and (C=D)) si nasconde la riga. Questo processo dovraà essere ripetuto N volte per tutti i records. Dopo ogni ciclo di confronto il numero dei confronti diminuisce.

[JumpMan]

Ho semplificato il codice e aggiunto il trucchetto di nascondere le righe univoche:

Private Sub CommandButton1_Click()
    Dim NomeFoglio As String
    Dim PrimaRiga As Integer
    Dim Col_Nome As Integer
    Dim Col_Cognome As Integer
    Dim Nome_I As String
    Dim Cognome_I As String
    Dim Nome_J As String
    Dim Cognome_J As String
    Dim I, J As Long
    Dim Var As Variant
    
    
    ' Preimpostazioni
    NomeFoglio = "Anagrafica"    'Nome foglio con dati anagrafici
    PrimaRiga = 2   'Prima riga con dati
    Col_Nome = 1 'Colonna Nome
    Col_Cognome = 2 'Colonna Cognome
    
    ' Attiva il foglio con i dati
    Sheets(NomeFoglio).Select
    
    ' Rileva nr. ultima riga
    UltimaRiga = ActiveCell.SpecialCells(xlLastCell).Row
    
    ' Nasconde tutte le righe
    ActiveSheet.Rows("2:65535").Select
    Selection.EntireRow.Hidden = True

    
    ' Scansiona il foglio alla ricerca di duplicati, se li trova rende visibile la riga
    With ActiveSheet
    For I = PrimaRiga To UltimaRiga
        Nome_I = .Cells(I, Col_Nome)
        Cognome_I = .Cells(I, Col_Cognome)
        For J = I + 1 To UltimaRiga
            Nome_J = .Cells(J, Col_Nome)
            Cognome_J = .Cells(J, Col_Cognome)
            If Nome_I = Nome_J And Cognome_I = Cognome_J Then
                .Cells(I, 1).Select
                Selection.EntireRow.Hidden = False
                .Cells(J, 1).Select
                Selection.EntireRow.Hidden = False
            End If
        Next J
    Next I
    End With
    
    MsgBox "Sono visibili solo i nomi duplicati, per visulizzarli tutti usare l'apposito comando (Formato>Riga>scopri) dopo aver selezionato l'intero foglio (rettangolo sopra l'1)"
End Sub

[blintz]

@ercolino22

Scusa Blintz ma non capisco perché non funziona ! sono mortificato.

senti mandami una copia dl foglio di lavoro

sempre se non contiene dati sensibili

oppure creane uno tu con un facsimile e mandamelo ....../ chiab_****@****.it

ciao

Modificato: 10 ottobre 2013 da Livio Migliaresi
rimosso indirizzo mail

[Ercolino22]

JumpMan mi ha inviato un intero processo.

Adesso devo solo copiarlo..... dove?

Ti dispiace farmi tutti i passaggi per l'attivazione ?

(Qualche anno fa lavoravo in Lotus123 e creavo anche le macro, direi molto sfizioso, poi non c'era il mouse e con la sola tastiera sembrava molto veloce lavorarci)

Comunque, fammi sapere come devo procedere

I dati del db sono abbastanza sensibili.

Usate le poche informazioni che vi ho dato: 70 campi e 10.000 righe, anche se sono di più, (ma questo non dovrebbe essere un problema, come dice Livio.

In seguito se devo applicare la macro di JumpMan sul mio db penso che devo solo adeguarla.

Scusa Blintz, ma cerca di capirmi...

Grazie amici

Modificato: 10 ottobre 2013 da Ercolino22

[JumpMan]

Purtroppo io sono fermo a Excel 2000 quindi potresti trovare delle differenze...

1. Le macro sono contenute nel documento excel e vanno attivate (te lo chiede lui quando apri il file la volta successiva)

2. Una macro può essere associata a un pulsante, ma anche no (nel mio caso si)

3. Puoi disegnare delle form complete con vari controlli (quoesto per il momento non ti interessa

4. Le macro le puoi anche registrare (utile anche per vedere rapidamente come si scrive un determinato codice)

5. L'editor VBA contiene tutto il codice delle macro (ALT+F11)

6 ... (vado di fretta)

Puoi iniziare disegnando un pulsante (devi attivare la barra degli strumenti "casella degli strumenti, lì lo trovi)

Prendi il tuo pulsante e lo posizioni in un punto qualsiasi del foglio.

Sulla casella degli strumenti c'è un tasto (il primo) che passa dalla modalità progettazione alla modalità esecuzione e viceversa

Se sei in modalità progettazione puoi editare il pulsante, quindi fai tasto dx > visualizza codice:

Lì ci inserisci il codice che ti ho scritto eccetto la prima e l'ultima riga (ci sono già)

Poi passi in modalità esecuzione e premi il pulsante.

ciao, vado...

[Ercolino22]

ok ora ci provo e poi ti faccio sapere.

Grazie a tutti per adesso.

[blintz]

@Ercolino22

una soluzione piu' valida e pratica sarebbe quella di filtrare tutto mettendo la colonna A "dalla A-Z"

poi creare tanti fogli quante le lettere dell'alfabeto

copiare le "A" nel foglio ""A"", ecc

poi creare una maschera con dei criteri di ricerca sul singolo foglio

per aiutarti

dimmi solo:

colonna A: Nome?

Colonna B: Cognome?

Colonna C: CF?

Colonna D:

Colonna E:

ecc

mandami un messaggio privato se vuoi con le intestazioni di colonna

poi provvedo per un esempio

ciao