Formula generica
=IF(COUNTIF(A:A,A1)>1,IF(COUNTIF(A$1:A1,A1)=1,"x","xx"),"")
Sommario
Per contrassegnare il primo duplicato in un elenco, puoi utilizzare una formula basata sulla funzione CONTA.SE. Facoltativamente, puoi contrassegnare i duplicati successivi con un indicatore diverso. Nell'esempio mostrato la formula nella cella C4 è:
=IF(COUNTIF($B$4:$B$11,B4)>1,IF(COUNTIF($B$4:B4,B4)=1,"x","xx"),"")
Questa formula è stata copiata nella colonna, da C4 a C11.
Spiegazione
Fondamentalmente, questa formula è composta da due set della funzione CONTA.SE racchiusa nella funzione SE. L'IF + COUNTIF esterno verifica prima se il valore in questione (B4) appare più di una volta nell'elenco:
=IF(COUNTIF($B$4:$B$11,B4)>1
In caso contrario, la funzione IF esterna restituisce una stringa vuota ("") come risultato finale. Se il valore appare più di una volta, eseguiamo un'altra combinazione IF + COUNTIF. Questo fa il lavoro di contrassegnare i duplicati:
IF(COUNTIF($B$4:B4,B4)=1,"x","xx")
Questa parte della formula utilizza un riferimento in espansione ($ B $ 4: B4) che si espande man mano che la formula viene copiata nella colonna. (Il primo B4 nell'intervallo è assoluto (bloccato), il secondo è relativo, quindi cambia quando la formula viene copiata nell'elenco).
Ricorda che questa parte della formula viene eseguita solo se la prima CONTA.SE ha restituito un numero maggiore di 1. Quindi, ad ogni riga, la formula controlla il conteggio all'interno dell'intervallo fino alla riga corrente. Se il conteggio è 1, contrassegniamo il duplicato con "x", poiché è il primo che abbiamo visto. Se non è 1, sappiamo che deve essere un duplicato successivo e contrassegniamo con "xx"
Formula di base
Per contrassegnare il primo duplicato in un elenco solo con 0 o 1, puoi utilizzare questa formula ridotta, che utilizza un intervallo in espansione e la funzione CONTA.SE.
=(COUNTIFS($B$5:B5,B5)=2)+0
Questa formula restituirà 1 solo quando un valore è stato incontrato due volte: la prima occorrenza restituirà zero:
Per contrassegnare la seconda e tutte le occorrenze successive, la formula in F5 sopra è:
=(COUNTIFS($E$5:E5,E5)>=2)+0
Nota: in entrambi gli esempi, l'aggiunta di zero è solo un modo semplice per forzare i valori VERO e FALSO a 1 e 0.
Inoltre, l'utilizzo di COUNTIFS invece di COUNTIF rende possibile valutare i valori in altre colonne come parte del test per i duplicati. Ogni colonna aggiuntiva deve anche essere inserita come intervallo in espansione.
