Fórmula genérica
=SUMPRODUCT(COUNTIF(data,data)-1)>0
Resumen
¿Un rango contiene valores duplicados? Si desea probar un rango (o lista) en busca de duplicados, puede hacerlo con una fórmula que use COUNTIF junto con SUMPRODUCT.
En el ejemplo, hay una lista de nombres en el rango B3: B11. Si desea probar esta lista para ver si hay nombres duplicados, puede usar:
=SUMPRODUCT(COUNTIF(B3:B11,B3:B11)-1)>0
Explicación
Trabajando de adentro hacia afuera, COUNTIF primero obtiene un conteo de cada valor en B3: B11 en el rango B3: B11. Como proporcionamos un rango (matriz) de celdas para los criterios, COUNTIF devuelve una matriz de recuentos como resultado. En el ejemplo que se muestra, esta matriz se ve así:
(1; 2; 1; 1; 1; 1; 1; 2; 1)
El siguiente 1 se resta, lo que produce una matriz como esta:
(0; 1; 0; 0; 0; 0; 0; 1; 0)
Tenga en cuenta que cada 1 en la matriz (es decir, los elementos que aparecen solo una vez) se ha convertido en un cero.
A continuación, SUMPRODUCT suma los elementos de esta matriz y devuelve el resultado, que en este caso es el número 2, que luego se prueba para un valor> 0.
Cada vez que una lista contiene duplicados, habrá al menos dos unos en la matriz sumada por SUMPRODUCT, por lo que un resultado final de TRUE significa que la lista contiene duplicados.
Manejo de celdas en blanco
Las celdas vacías en el rango harán que la fórmula anterior genere resultados incorrectos. Para filtrar celdas en blanco o vacías, puede utilizar la siguiente alternativa:
=SUMPRODUCT((COUNTIF(list,list)-1)*(list""))>0
Aquí usamos la lista de expresiones lógicas "" para forzar a cero todos los valores asociados con celdas en blanco.
