Ya he tratado el tema en diversas oportunidades, por ejemplo como comparar dos listas usando formato condicional o como evitar duplicados usando validación de datos. También he mostrado como usar consolidación de datos para comparar listas
En esta nota haremos una revisión ordenada del tema. Empecemos por plantearnos un ejemplo. En una misma hoja tenemos dos listas de productos
Para facilitar nuestro trabajo hemos definido dos nombres
lista1 =Hoja1!$A$2:$A$11
lista2 =Hoja1!$D$2:$D$11
1 - Encontrar duplicados usando la función COINCIDIR
En el rango B2:B11 ponemos la fórmula =COINCIDIR(A2,lista2,0); en el rango E2:E11 ponemos la fórmula =COINCIDIR(D2,lista1,0). El resultado es
Los resultados N/A identifican los valores de la lista 1 que no se encuentran en la lista 2, y viceversa. Podemos lograr un resultado más "elegante" combinando COINCIDIR con SI y ESERROR en esta fórmula
=SI(ESERROR(COINCIDIR(A2,lista2,0)),"","duplicado")
El resultado es
Podemos mejorar aún más la presentación usando formato condicional para dar un fondo de color a los duplicados. Por ejemplo, para la lista2 definimos esta condición
La dirección de la celda de referencia debe ser relativa (sin los signos $)
2 – Señalar duplicados usando formato condicional.
Como explicamos en la nota sobre el tema, usamos la función CONTAR.SI en el menú de formato condicional para comprobar si valores de la lista 1 también figuran en la lista 2. Usamos una fórmula distinta para cada lista.
En la lista 1 usamos la fórmula =CONTAR.SI(lista2,A2)>0
En la lista 2 usamos la fórmula =CONTAR.SI(lista1,A2)>0
El resultado es idéntico al método anterior
3 – Generar una lista de valores comunes a ambas listas
Digamos que ahora queremos generar una lista de valores que aparecen en ambas listas.
En nuestro ejemplo, escribimos esta fórmula en la columna F:
=SI(CONTAR.SI(lista2,A2)>0,A2,"")
Alternativamente podemos usar la fórmula =SI(CONTAR.SI(lista1,D2)>0,D2,""), que dará el mismo resultado aunque en distinto orden.
Como podemos ver en la lista de valores comunes aparecen celdas en blanco. Una alternativa para eliminar las celdas en blanco es copiar los valores de la lista a otro rango y usar la función Ir A--Especial--Celdas en Blanco, como explicamos en esta nota.
También podemos hacerlo usando fórmulas. Para esto necesitaremos crear dos rangos de fórmulas auxiliares.
Siguiendo con nuestro ejemplo, escribimos esta fórmula en la celda H2 y la copiamos a lo largo del rango
=SI(CELDA("contenido",F2)="",0,FILA())
Esta fórmula nos permite dar un número único si en la columna F aparece un producto común a ambas listas o 0 (cero) si el resultado en F es blanco. Usamos la función CELDA y no ESBLANCO, ya ninguna de las celdas en la columna F está en blanco (todas contienen fórmulas).
El próximo paso es usar la función JERARQUIA en la columna I
=JERARQUIA(H2,$H$2:$H$11)
En esta fórmula hemos omitido el argumento opcional "orden", por lo tanto el resultado aparece en forma descendiente.
Podemos ver que para las celda con resultado 0 en la columna H, obtenemos el mismo número de orden.
El resultado en la columna I nos sirve de variable en la fórmula que escribimos en la columna J,
=INDICE($F$2:$F$11,COINCIDIR(FILA()-1,$I$2:$I$11,0))
que nos da esta lista
El problema con esta fórmula es que aparecen resultados N/A. Para evitar esto agregamos una condición a nuestra fórmula, resultando
=SI(ESERROR(INDICE($F$2:$F$11,COINCIDIR(FILA()-1,$I$2:$I$11,0))),"",INDICE($F$2:$F$11,COINCIDIR(FILA()-1,$I$2:$I$11,0)))
es decir, primero evaluamos la fórmula y si el resultado da error, la función SI da como resultado "blanco", si no se aplica la función INDICE.
4 – Generar una lista de valores únicos
Para generar una lista de valores únicos, usamos el mismo método con la diferencia que la fórmula en la columna F pasa a ser
=SI(CONTAR.SI(lista2,A2)=0,A2,SI(CONTAR.SI(lista1,D2)=0,D2,""))
donde combinamos dos condiciones para obtener los productos que sólo aparecen en una de las listas.
Por supuesto, existen otros métodos para comparar listas, incluyendo el uso de macros. Esto será tema de una futura nota.
Categorías: Funciones&Formulas_, Manejo de Datos_
Technorati Tags: Excel, Duplicados en Excel
les invito a bajar el archivo con el ejemplo aquí
