Cargando...
Detectar y borrar datos duplicados en Excel
Una de las tareas más comunes en el día a día en Microsoft Excel es detectar los datos duplicados en una columna o una fila. Existen multitud de técnicas y cada vez descubro nuevas fórmulas para realizar esta tarea. Personalmente utilizo 2 formulas que considero igualmente buenas dependiendo si los datos están ordenados o no.
Por último, os mostrare como eliminar los elementos duplicados de forma sencilla o de forma no tan sencilla en el caso de buscar elementos en más de una columna.
Detectar duplicados con datos NO ordenados.
Cuando los datos no están ordenados podemos detectar si hay duplicados o no utilizando la fórmula CONTAR.SI. En el siguiente ejemplo se muestra un listado de nombres (Columna A) en el que aparecen valores repetidos. Con la formula CONTAR.SI contamos el número de elementos que son iguales en la columna A, si el número es superior a 1, entonces el valor está repetido.
Detectar duplicados con datos ordenados.
Si los datos están ordenados podemos también utilizar la fórmula que hemos visto en el ejemplo anterior, no obstante, aprovechando que los datos están ordenados podemos utilizar una solución más sencilla y que no utiliza formulas matriciales que son muy pesadas, sobretodo si trabajamos con muchos datos. De hecho si realmente tenéis muchos datos y podéis cambiar el orden de los mismos os recomiendo este método.
Utilizado dos sencillas comparaciones, podemos saber si se trata de un dato duplicado o no. En un listado ordenado, si una celda es igual a su antecesor o es igual a su predecesor entonces es un duplicado. Podéis verlo en el siguiente ejemplo.
Borrar elementos duplicados.
Borrar los elementos duplicados supone que debemos dejar los elementos que no estén duplicados y uno y sólo uno de los elementos que están duplicados. Si sólo queremos eliminar los elementos duplicados en una columna podemos aplicar la siguiente metodología.
- Ordenar los datos.
- Usar la formula para la celda B3 “=A2=A3” que nos detecta si una celda es igual a su antecesora.
- Filtramos y borramos las filas que sean “VERDADERO”.
Borrar duplicados en múltiples columnas.
Para borrar duplicados en múltiples columnas de Excel se puede repetir el proceso anterior, pero creando una columna índice que concatene el contenido de las columnas en las que queremos detectar los duplicados. Una vez creada esta columna índice sólo tenemos que repetir el proceso anterior para detectar y borrar los elementos duplicados.
Evidentemente este es un proceso demasiado tedioso y no exento de posibles errores al manipular demasiada información manualmente. Para estos casos realmente utilizo una Macro de Excel que de forma sencilla y rápida hace todo el proceso por mi 
, es una Macro muy sencilla, voy a “limpiarla” y “documentarla” minimamente y en los próximos días actualizaré este post o escribiré uno nuevo.
Imprime este post
-
Blog bajo licencia Creative Commons Attribution 3.0 License
42 comentarios sobre Detectar y borrar datos duplicados en Excel
Muchas gracias por tu ayuda, muy fácil de utilizar y muy bien explicado!!
la verdad que esta muy buena la explicacion y me sirvio muchisimo, gracias
Muchas Gracias, de verdad que ha sido util!!!
Gracias por la ayuda pero deseo tener conocimiento de la FORMULA, de como detectar numeros de facturas en una hoja de calculo, pero con los datos en mas de tres columnas GRACIAS
Hola Nestor,
Creo que la forma más sencilla es:
1) Crear una columna auxiliar donde concatenes los datos de todas las columnas donde deseas buscar los duplicados. Concatenar columnas es muy sencillo, puedes usar la fórmula
=CONCATENAR(A1;B1;…)o bien el operador “&”=A1&B1&...2) Una vez generada la columna auxiliar puedes realizar el mismo proceso que se describe en el post para deduplicar las filas.
NOTA: Ciertamente, esto sólo funciona si los datos son exactamente iguales. Si quieres que seleccione datos aproximados o aplique técnicas más complejas no podrás hacerlo sólo con fórmulas de Excel. Para estos casos hay herramientas que ayudan a deduplicar datos de forma semi-inteligente, pero deduplicar es un proceso muy complejo de automatizar y no hay una fórmula mágica 100% efectiva a día de hoy.
Un saludo,
lcflores
estan buenas las explicaciones, pero por favor queisiera saber si es que es posible buscar dulicados en mas de dos columnas y poder darles un formato condicional a los duplicados.
conozco la manera de hacerlo en una columna, pero quisiera saber si se puede en dos o mas columnas
gracias
Hola Many,
Se puede realizar, pero en este caso tendremos que usar una macro.
Yo utilizo una pequeña macro, llevo tiempo queriendo darle un poco de formato y añadir algunas explicaciones para publicar un post, espero no tardar mucho más.
Un saludo,
lcflores
¿Cómo puedo crear una fórmula para que me avise si tengo números de teléfono duplicados?, gracias a todos.
Siguiendo las explicaciones del post:
1) Crea una nueva columna a la derecha de la columna con los números de teléfono.
2) OJO: Es importante que los teléfonos tengan un formato homogéneo sino el resultado no será el deseado. Por ejemplo todos los números seguidos y sin el código de país.
3) En la primera celda de la nueva columna, utiliza la fórmula CONTAR.SI: el primer argumento será la columna de los teléfonos; el segundo argumento será la celda de la izquierda.
4) El resultado de la fórmula motrará “1″ en los teléfonos no duplicados y otro número para aquellos duplicados (igual al número de veces que está duplicado).
Espero haberte ayudado,
lcflores
Permitame felicitarle, excelente
Muchísimas gracias a mí también me sirvió y sale bien detallado y bien explicado
Estimado Lcflores:
Un saludo primero que nada y las gracias por tu aporte a la comunidad. Me gustó la forma en que lo explicaste, todo muy claro, sin embargo no me ha sido de utilidad. Quiero comentar que estoy manejando una base de 13170 registros de los cuales sabemos que hay al rededor de 1000 registros repetidos y solo tenemos un pequeño problema al respecto; no podemos identificarlos. Cabe mencionar que los valores que buscamos tienen formato de correo electrónico. Agradeciendo cualquiero tiepo de ayuda, reciban un cordial saludo.
Estimado Vicente,
Gracias a ti por tu comentario. La primera pregunta es si los correos electrónicos duplicados son exactamente iguales o no. Para hacerlo más sencillo, diferenciaría los siguientes tipos de duplicados:
Caso A) “pepe@prueba.com” y “pepe@prueba.com”
Caso B) “pepe@prueba.com” y “PEPE@prueba.com ”
Caso C) “pepe@prueba.com” y “jose@prueba.com”
Caso D) “jgarcia@prueba.com” y “josegarcia@prueba.com”
Caso A
Con el ejemplo de este post se detectarían los duplicados sin problemas (ojo, puede que tarde un tiempo por el número de registros.
Caso B
Es igual que el Caso A, pero hay que realizar un preproceso que ponga los datos en un formato homogéneo. Ten en cuenta que los 2 carácteres blancos que aparecen al final de “PEPE@prueba.com ” para una máquina si son importantes y hará que falle la búsqueda de los elementos duplicados.
En este sencillo ejemplo, la solución pasa por crear una nueva columna y en base a sencillas formulas de Excel poner los datos de una forma homogénea.
Supongamos que los datos están en la columna A, creamos la columna B y añadimos la fórmula:
=MINUSC((SUSTITUIR(A1;" ";"")))de esta forma quitaremos los espacios en blanco y pondremos todo en minúsculas.Finalmente realizamos la búsqueda de duplicados, pero en este caso en la columna B que es donde están los datos homógeneos.
Caso C
Díficil sólución, se puede tratar de “homogeneizar” la información sustituyendo “pepe” por “jose” o similar, pero en la gran mayoría de los casos, cuando no hay un patron concreto a buscar, siento decirte que el proceso de deduplicación deberá realizarse a mano en su mayor parte. (hay programas que tienen IA y permiten hacer maravillas… pero todavía nada que haga sombra al cerebro humano).
Caso D
Para este tipo de casos pueden utilizarse algunas técnicas ingeniosas que varian en función del problema. Por ejemplo coger los 3 primeros y los 3 ultimos caracteres, o lo contrario borrar los 3 primeros y 3 ultimos caracteres. Este tipo de técnicas son realizadas normalmente mediante Macros o funciones Macro que permiten normalizar de forma más sencilla los datos. Los problemas y soluciones que podemos encontrarnos son tan amplios que podría estar escribiendo todo un mes sobre ello. Si este es tu problema, intenta utilizar alguna técnica más sencilla como las que te he comentado en el Caso B.
Espero haberte ayudado,
lcflores
Buenisimo! muchas gracias por tu ayuda… es justamente lo que estaba buscando y me funciono a la perfeccion!
Saludos cordiales
Necesito saber como borrar los puntos y guiones a los rut EJ: 15.326.958-5 que quede como 153269585 . estan en el mismo espacio y deben quedar en el mismo espacio.
Ojala me ayudes
Excelentes las explicaciones, eres un crack! Ojalá hubiera encontrado este post por mediados del mes de septiembre que fue la primera vez que necesité una formula de estas.
Bueno, con el tiempo mi problema ha crecido y mucho.
Tengo una base de datos con más de 5000 numeros y estan todos en una coluna (ninguno de ellos es repetido). Ahora viene el lío: A la medida que vamos llamando a esta gente, lo vamos poniendo en una otra coluna, haciendo que con eso los numeros se queden repetidos, pero en colunas distintas. He logrado que los numeros en la primera coluna se queden coloridos a la medida que vamos rellenando la segunda coluna(formato condicional en toda la coluna 1 con la formula =COINCIDIR($A10365;B:B;0) ), pero lo que quiero realmente es borrar estos numeros automaticamente a la medida que los metemos en la coluna 2.
Como lo hago?
Estimado morpheus,
La opción para realizar exactamente lo que quieres no es sencilla y principalmente sería vía Macro, lo cual tampoco tiene mucho sentido para este problema.
Lo que creo que si puedes hacer es crear como en el ejemplo una columna auxiliar donde reflejar con “Verdadero”/”Falso” si es un duplicado (en este caso con la función
ESERROR(BUSCARV(xxx))). Al mismo tiempo, crear un autofiltro para poder filtrar de forma rápida y ver que numeros no están todavía en la otra lista.Un saludo,
Lcflores
Hola Lcflores,
En las ultimas dos horas estuve probando algo con macro, pero aun no he logrado lo que necesito. He probado crear una coluna auxiliar, pero con la función contar.si y la verdad es que en ese caso no me fue muy útil porque tengo millares de numeros de telefono (muchisimo más que los 5000 que he dicho antes) y no están ordenados. Aparte de que cuando son utilizados los cogen de 600 en 600 y buscar manualmente todos los “verdaderos” era una verdadera batalla. Aún no he probado con la funcion ESERROR, pero si se me ocurrió la idea de usar filtros pero la verdad es que esta parte se me va de las manos pues no sé utilizarlas bien jejejeje…
Bueno, voy a probar tus sugerencias y tambien probar con algo de macro y lo que se me ocurra. Si consigo algo posteo aqui, pues puede servir a alguien en un futuro.
Un abrazo
morpheus_ton
excelente… la formula contar.si me ahorrara horas de trabajo en los reportes. Quedo Infinitamente agradecido.
Reemplazar los puntos o guiones en Excel es relativamente fácil con la función
SUSTITUIR, con está función cambiaremos los puntos"."por espacios en blanco""Por ejemplo:
=SUSTITUIR(SUSTITUIR("15.326.958-5";".";"");"-";"")Un saludo,
lcflores
Tengo una base de datos a depurar por tener elementos repetidos (Ej.:
INTERRUPTOR SIEMENS 3VL57 31-2DK36-0AD1
INTERRUPTOR SIEMENS 3VL27 06-2DK36-0AB1
INTERRUPTOR SIEMENS 3VL37 20-1DC36-0AB1
INTERRUPTOR SIEMENS 3VL27 10-1CP36-0AB1
INTERRUPTOR SIEMENS 3VL27 16-1DC36-0AB1
INTERRUPTOR SIEMENS 3VL27 10-2DC36-0AB1
En esa lista que poseo (la cual son aprox. 3400 artículos), puede variar el texto simplemente por agregado de un punto, una coma, o texto abreviado, deja espacio entre definiciónes, etc.
Lo cual recorrer todos los elementos uno a uno se me hace tedioso y cansador. Hay alguna forma de controlar el contenido de las celdas???
Gracias.
Saludos
Hola José Luis,
Como comenté anteriormente, lo primero que tienes que hacer es estándarizar los datos, para ello remplaza los caracteres de separación como puntos, comas, espacios en blanco…
Para eliminar estos caracteres puedes usar la fórmula
SUSTITUIR, como puedes ver en el siguente ejemplo:=SUSTITUIR(SUSTITUIR("31-2DK36-0AD1";".";"");"-";"")=SUSTITUIR(SUSTITUIR("31.2DK36.0AD1";".";"");"-";"")En ambos casos el resultado será:
312DK360AD1Y con el método explicado en este post podrás borrar los que estén duplicados.
Lcflores
buena tu reseña… es facil entender cuando es bien simple la explicacion
Hay una forma mucho mas simple, sin macros ni nada complicado:
La solución está en Datos-Filtro-Filtro avanzado:
marcar “Sólo registros únicos”, informar el rango de la lista y elegir “Filtrar la lista sin moverla a otro lugar” o “Copiar a otro lugar”. Aceptar y listo.
me sirvio mucho esa explicacion para mi exposicion d examen gracias! te lo agradesco adios!
Muy útil, me sirvió demucho gracias
Me gustaria que evaluaran esta opción para eliminar elementos repetidos y ordenados =SI(B3=B2;”SI”;”NO”), luego filtramos “si” para eliminar las repetidas.
Espero comentarios.
Muchas Gracias!!! estaba patinando hace rato y lo logre!!!
Saludos!
Gracias a Icflores por lo detectar teléfonos duplicados.funcionó estupendo.
Genial. Excelente post.
Muchas gracias.
Utilicé el siguiente método a partir de la lectura de este post.
1. Detectar duplicados con datos NO ordenados.
Cuando los datos no están ordenados podemos detectar si hay duplicados o no utilizando la fórmula CONTAR.SI. En el siguiente ejemplo se muestra un listado de nombres (Columna A) en el que aparecen valores repetidos. Con la formula CONTAR.SI contamos el número de elementos que son iguales en la columna A, si el número es superior a 1, entonces el valor está repetido.
2. Una vez que aparece VERDADERO y FALSO, al tratar de ORDENAR los datos para que me queden arriba los falsos y abajo los verdaderos, para poder eliminarlos más tranquilamente, no pude hacerlo.
3. Copié la tabla excel (con la función VERDADERO – FALSO ya realizada en una columna) y la pegué en una hoja de LOTUS.
4. Posicionándome en el rango A de la columna verdadero o falso, seleccioné Rango – Valor – Indicar rango de origen (pinte toda la columna) – Indicar Rango de destino (pinte toda la columna). ENTER. De esa manera VERDADERO y FALSO se transformaron de fórmulas en palabras.
5. Luego copie toda la tabla LOTUS, la pegué en una tabla Excel. Pinté la columna de VERDADERO – FALSO y elegí ORDEN ASCENDENTE.
6. De esa manera dividí la tabla en dos partes muy bien definidas: arriba los FALSOS y abajo los VERDADEROS.
7. Luego de comprobar con dos o tres datos que realmente los falsos eran falsos y los verdaderos verdaderos, pinte las filas de VERDADEROS, las eliminé y sólo me quedaron los FALSOS, es decir, los datos que no se repiten.
8. Esto sirve para filtrar bases de hasta 65.536 rangos (el máximo de filas del Excel).
Cómo hacer para eliminar valores superiores de 65.536 datos?
Espero se les ocurra algo.
Saludos a todos.
No hace falta copiar la hoja de Excel a Lotus, ya que Excel tiene una función similar.
Entonces los pasos son:
1. Detectar duplicados con datos NO ordenados.
Cuando los datos no están ordenados podemos detectar si hay duplicados o no utilizando la fórmula CONTAR.SI. En el siguiente ejemplo se muestra un listado de nombres (Columna A) en el que aparecen valores repetidos. Con la formula CONTAR.SI contamos el número de elementos que son iguales en la columna A, si el número es superior a 1, entonces el valor está repetido.
2. Una vez que aparece VERDADERO y FALSO, simplemente pinto dicha columna, Copiar y Pegado Especial, selecciono valores y listo.
3. Pinté la columna de VERDADERO – FALSO y elegí ORDEN ASCENDENTE.
4. De esa manera dividí la tabla en dos partes muy bien definidas: arriba los FALSOS y abajo los VERDADEROS.
5. Luego de comprobar con dos o tres datos que realmente los falsos eran falsos y los verdaderos verdaderos, pinte las filas de VERDADEROS, las eliminé y sólo me quedaron los FALSOS, es decir, los datos que no se repiten.
Saludos a todos
Y cuando los datos los datos no se encuentran en una sola comumna cual seria el criterio de busqueda?
Gracias.
Concatenas las diferentes columna en una única columna y aplicas el mismo método
Excelente, me fue de gran ayuda y esta muy bien explicado!
Saludos
Perfecto me sirvio de mucho mil gracias
También hay una manera rapida sin fórmulas que encuentra los datos duplicados y los elimina.
SELECCIONAN la columna de los datos, Luego se van a la pestaña DATOS y le dan QUITAR DUPLICADOS y luego le dicen si a todo y ya está.
Q bueno, justo lo q estaba buscando, muchas gracias!
Buenas tardes, es muy basico men, le sirve a los novatos, aunque yo hago mejores que esas explicaciones, vas aprendiendo, sigue asi que pronto me superaras. va es una broma, todo chevere…
tengo una base de datos con más de 100000 registros, la tercer parte duplicados de esta forma
ALUMNO CODIGO ASIGNATURA NOTA
José 1 lenguaje 8
José 1 matematicas 7
José 1 sociales 7
Pedro 2 lenguaje 9
Pedro 2 matematicas 9
Pedro 2 sociales 8
como ven el alumno se repite 3 veces por culpa de la
columna asignatura.
yo quiero convertir eso a esto para eliminar repetidos.
ALUMNO CODIGO LENGUAJE MATEMATICAS SOCIALES
José 1 8 7 7
Pedro 2 9 9 8
Porfa!!! alguien me puede dar una idea de como hacerlo… De Antemano Gracias
Hola Rafa,
Si sólo tienes que transformarlo una vez y no tienes muchos registros, lo más sencillo en este caso es que utilices una tabla dinámica de Excel. Selecciona los datos y crea una nueva tabla dinámica. En las filas tienes que poner “ALUMNO”, en las columnas “ASIGNATURA” y finalmente en los datos “NOTA”.
Un saludo,
LCF
Hola Leo,
Pues me temo que o usas la nueva versión de Excel que ya no tiene ese límite o tendrás que utilizar otros métodos. Se me ocurre que si ordenas los datos puedes usar Excel trabajando en grupos de 65.536 registros.
LCF
Deja tu comentario sobre Detectar y borrar datos duplicados en Excel
*Nota: Sólo se tendrán en cuenta los comentarios correctamente redactados y que estén relacionados con el tema de la entrada.RSS a los comentarios de esta entrada · TrackBack URI