Fórmula genérica
=IF(COUNTIF(A:A,A1)>1,IF(COUNTIF(A$1:A1,A1)=1,"x","xx"),"")
Resumen
Para marcar el primer duplicado en una lista, puede usar una fórmula basada en la función CONTAR.SI. Opcionalmente, puede marcar los duplicados posteriores con un marcador diferente. En el ejemplo que se muestra, la fórmula en la celda C4 es:
=IF(COUNTIF($B$4:$B$11,B4)>1,IF(COUNTIF($B$4:B4,B4)=1,"x","xx"),"")
Esta fórmula se ha copiado en la columna, de C4 a C11.
Explicación
En esencia, esta fórmula se compone de dos conjuntos de la función CONTAR.SI envuelto en la función SI. El IF + COUNTIF externo primero verifica si el valor en cuestión (B4) aparece más de una vez en la lista:
=IF(COUNTIF($B$4:$B$11,B4)>1
De lo contrario, la función IF externa devuelve una cadena vacía ("") como resultado final. Si el valor aparece más de una vez, ejecutamos otro combo IF + COUNTIF. Este hace el trabajo de marcar duplicados:
IF(COUNTIF($B$4:B4,B4)=1,"x","xx")
Esta parte de la fórmula utiliza una referencia de expansión ($ B $ 4: B4) que se expande a medida que la fórmula se copia en la columna. (El primer B4 del rango es absoluto (bloqueado), el segundo es relativo, por lo que cambia a medida que la fórmula se copia en la lista).
Recuerde que esta parte de la fórmula solo se ejecuta si el primer CONTAR.SI devolvió un número mayor que 1. Entonces, en cada fila, la fórmula verifica el recuento dentro del rango hasta la fila actual. Si el recuento es 1, marcamos el duplicado con "x", ya que es el primero que vemos. Si no es 1, sabemos que debe ser un duplicado posterior y marcamos con "xx"
Fórmula básica
Para marcar el primer duplicado en una lista solo con un 0 o 1, puede usar esta fórmula simplificada, que usa un rango en expansión y la función CONTAR.SI.
=(COUNTIFS($B$5:B5,B5)=2)+0
Esta fórmula devolverá 1 solo cuando se haya encontrado un valor dos veces; la primera aparición devolverá cero:
Para marcar la segunda y todas las apariciones posteriores, la fórmula en F5 anterior es:
=(COUNTIFS($E$5:E5,E5)>=2)+0
Nota: En ambos ejemplos, sumar cero es solo una forma sencilla de convertir los valores VERDADERO y FALSO en 1 y 0.
Además, el uso de CONTAR.SI en lugar de CONTAR.SI hace posible evaluar valores en otras columnas como parte de la prueba de duplicados. Cada columna adicional también debe ingresarse como un rango en expansión.
