Al trabajar con Excel es común tener la necesidad de buscar datos repetidos en dos columnas por lo que en esta ocasión te mostraré algunas técnicas que podemos utilizar para realizar dicha búsqueda e identificar aquellos datos que aparecen en ambas listas de nuestra hoja.
Como ejemplo utilizaremos las listas de clientes de una empresa en dos meses diferentes. Nuestro objetivo es conocer los clientes que han adquirido algún producto en ambos meses de manera que se les pueda aplicar un descuento en su próxima compra.
Es probable que a simple vista puedas identificar algunos de los clientes que aparecen en las dos columnas pero seremos mucho más certeros si podemos aplicar algún estilo de celda especial a aquellos datos repetidos así que nuestra primera opción será utilizar el formato condicional para resaltar dichas celdas.
Resaltar datos repetidos con formato condicional
Para encontrar los datos que están repetidos en ambas columnas seleccionaré el rango de celdas A2:B21 y en la ficha Inicio seleccionaré el comando Formato condicional > Resaltar reglas de celdas > Duplicar valores.
Se mostrará el cuadro de diálogo Duplicar valores y deberás asegurarte de elegir la opción Duplicar de la primera lista desplegable y podrás elegir cualquier opción de formato de la segunda lista. Una vez que hagas clic en el botón Aceptar, se creará la regla de formato condicional correspondiente y se resaltarán todos los datos repetidos en las dos columnas.
Para obtener fácilmente la lista de valores repetidos puedes aplicar un filtro desde la ficha Inicio > Modificar > Ordenar y filtrar > Filtro y posteriormente hacer clic en el filtro de cualquiera de las dos columnas y elegir la opción Filtrar por color y en la sección Filtrar por color de celda deberás elegir el color de relleno de las celdas duplicadas.
Esto ocultará las celdas que no tienen relleno y solo dejará visibles las celdas que contienen datos repetidos en las dos columnas. En este ejemplo apliqué el filtro a la columna Febrero 2015 por lo que el resultado será como el siguiente:
Las celdas de la primera columna no son relevantes en este momento y solo deberás centrar tu atención en la columna recién filtrada la cual mostrará los datos que aparecen tanto en la primera como en la segunda columna.
Debes saber que para este ejemplo he supuesto que cada una de las listas tiene valores únicos, es decir, no existen clientes repetidos bajo una misma columna sino que solamente se repiten entre ambas columnas. Si por el contrario, tuvieras datos repetidos bajo una misma columna, entonces deberás dar algunos pasos adicionales que explicaré en la siguiente sección.
Encontrar datos repetidos en columnas
A continuación haremos un ejemplo que es una variación del anterior ya que la lista de clientes para un solo mes contiene datos duplicados. Comenzaremos de la misma manera que con el ejemplo anterior, es decir, por seleccionar el rango de celdas para crear el mismo tipo de regla de formato condicional hasta obtener un resultado como el siguiente:
Aquí puedes ver que bajo la columna Enero 2015 tenemos repetido tres veces al cliente Apple. También está repetido dos veces el cliente Valero Energy y algo similar sucede bajo la columna Febrero 2015 con otros clientes. La única diferencia que habrá con el ejemplo anterior es que, al momento de aplicar el filtro sobre estos datos, obtendremos una lista con valores duplicados.
Para eliminar los duplicados puedes seleccionar las celdas que han sido resaltadas y copiarlas a otro rango diferente. Si pegas los datos a la misma altura que el rango original, debes recordar borrar el filtro para que se vuelvan a mostrar todas las filas de la hoja.
Asegúrate de tener seleccionados los datos recién copiados y haz clic en el comando Datos > Herramientas de Datos > Quitar duplicados y tendremos la lista de los valores que se repiten en ambas columnas tal como la obtuvimos en el primer ejemplo.
Fórmula para buscar datos repetidos en dos columnas
Aunque el método utilizado en los ejemplos anteriores es suficiente para buscar los datos repetidos en dos columnas de Excel, a continuación te mostraré una fórmula que nos ayudará a obtener los mismos resultados. Pero debo advertirte que no es una fórmula sencilla, de hecho es una fórmula matricial, así que antes de insertarla en nuestra hoja debemos seleccionar el rango de celdas donde queremos mostrar los resultados.
Con el rango de celdas seleccionado, insertamos la siguiente fórmula en la barra de fórmulas y pulsamos la combinación de teclas Ctrl + Mayús + Entrar:
=SI.ERROR(INDICE($A$1:$A$21, K.ESIMO.MAYOR(SI.ERROR(COINCIDIR(B1:B21, $A$1:$A$21,0), ""), FILA())), "")
Recuerda que algunos conocen la tecla Mayús como la tecla Shift (que es su nombre en inglés). Una vez ingresada la fórmula obtendremos el resultado esperado en la columna D:
Esta fórmula la podemos analizar desde la función más anidada hacia afuera es decir, a partir de la función COINCIDIR. Dicha función se encarga de comparar los valores de la columna B con los valores de la columna A. Si existe una coincidencia, obtendremos el número de fila donde se encuentra la celda con el mismo valor o de lo contrario obtendremos un error #N/A.
En seguida utilizamos la función SI.ERROR que nos sirve para remover esos errores #N/A y los reemplazamos por una cadena de texto vacía de manera que solo tengamos números de fila y cadenas vacías. Este paso es indispensable para que la función K.ESIMO.MAYOR ordene primero los números y deje las cadenas vacías al final de los resultados.
Finalmente la función INDICE nos ayuda a obtener el texto de cada celda en base a los números de fila devueltos por la función COINCIDIR y que ya han sido ordenados por la función K.ESIMO.MAYOR. La última función SI.ERROR se encargará de reemplazar con cadenas vacías los errores devueltos por la función INDICE.
Aunque mi preferencia está con el método basado en las reglas de formato condicional, la realidad es que la fórmula puede ser muy útil para esos casos en los que deseas automatizar este proceso. Tal vez tienes una macro que se encargará posteriormente de tomar los resultados de la fórmula y aplicar automáticamente el descuento a esos clientes y todo eso podría suceder sin la necesidad de intervenir en el proceso.
Finalmente te dejo un vínculo para descargar el libro de Excel utilizado en la creación de estos ejemplos. Recuerda que este artículo se ha basado exclusivamente en la búsqueda de datos repetidos en dos columnas pero, si estás interesado en eliminar los datos repetidos entonces te recomiendo leer el artículo Extraer valores únicos en Excel que seguramente será de gran ayuda en dicha tarea.