CÓMO ENCONTRAR DATOS REPETIDOS EN DOS COLUMNAS DE EXCEL

0
143
Slider

Cuando trabajas con una hoja de Excel que contiene muchos datos, 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. La función del formato condicional de Excel te muestra justamente dónde se ubican los duplicados, mientras que la función de eliminar duplicados los elimina por ti. Comparar columnas en Excel y resaltar repetidos te garantiza que tus datos y tu presentación sean lo más preciso posible.

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 > Reglas para resaltar celdas > Valores duplicados.

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 > Edición > 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 2021 por lo que el resultado será como el siguiente:

previous arrow
next arrow
Slider

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, en esta 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 2021 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 2021 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.

Artículo que te recomendamos leer:
MACRO PARA IMPEDIR LA IMPRESIÓN EN EXCEL

¿Qué te pareció nuestra blog? Coméntanos.



CURSO DE ESPECIALIZACIÓN: EXCEL EMPRESARIAL – NIVEL AVANZADO

📆 Fechas: Martes y jueves: 20/07, 22/07, 27/07 de julio, 03/08, 05/08, 10/08, 12/08, 17/08 de agosto.
⏰ Horario: 7:00 pm – 10:00 pm
Duración: 8 sesiones,1 mes.

Mayor información: https://wa.link/1zp01x

Comentarios
Slider