lunes, 6 de abril de 2009

Detectar y contar duplicados

Un problema muy frecuente es el de tener que localizar dentro de una o varias columnas datos duplicados. En este ejemplo veremos cómo localizarlos visualmente mediante el Formato condicional y como contar cuántas veces está repetido cada dato dentro de una columna (función CONTAR.SI). Los datos de partida son los siguientes:


Queremos que en cada Zona sombree en gris los datos (los códigos de cliente) que estén repetidos dentro de cada zona y que además, a la derecha, nos indique el número de veces que está repetido dicho código. Para ello seguimos los siguientes pasos:


1. Seleccionamos el rango B3:B18 y vamos al menú Formato/Formato Condicional.

2. Abrimos la primera lista desplegable, donde dice valor de la celda, y seleccionamos la opción Fórmula, y en el recuadro de la derecha escribimos: =CONTAR.SI(B$3:B$18;B3)>1

Con esta fórmula excel evaluará cada celda del rango B3:B18 contra todo el rango y verificará si se repite más de una vez.
3. Una vez escrita la fórmula pulsamos el botón Formato, con lo que se abrirá la ventana de formato de celda, donde debemos seleccionar el formato que queremos que tengan las celdas duplicadas. En nuestro ejemplo hemos utilizado una trama gris. Aceptamos el formato elegido y volvemos a pulsar aceptar en la ventana de formato condicional. De esta manera ya habremos conseguido que los códigos de cliente que estén duplicados en la primera columna queden sombreados de gris.

4. Seleccionamos el rango B3:B18 y pulsamos el icono de Copiar formato.
5. Hacemos clic encima de la celda D3 y repetimos la operación en la celda F3. De esta manera habremos copiado el formato condicional para las zonas 2 y 3 y ahora las tres columnas nos muestran los datos duplicados en las respectivas zonas.

Ya sólo nos queda que nos indique cuántas veces está repetido cada código dentro de la columna. Para ello nos situamos en la celda C3 y escribimos la siguiente fórmula:

=CONTAR.SI(B$3:B$18;B3)

Hecha la fórmula la copiamos hasta C18 (o hacemos doble clic en la parte inferior derecha de la celda C3 una vez estemos situados en la misma). Finalmente podemos copiar el rango C3:C18 en E3:E18 y en G3:G18

57 comentarios:

  1. PERFECTO
    ME HAS AHORRADO HORAS DE TRABAJO

    ResponderEliminar
  2. Hola Kiko, gracias a ti he descubierto que en Excel 2007 hay una opción en "Formato condicional" ==> "Resaltar reglas de celdas" ==> "Duplicar valores..." que nos resuelve la detección de duplicados de forma automática. También sirve para detectar y resaltar los valores únicos de un rango de celdas. El recuento sí que se ha de hacer a mano.

    Por otro lado comentarte que en la última captura de pantalla de tu ejercicio hay un error en la celda D16, es el mismo código que en la celda D3. Creo que se te ha colado un espacio en blanco al final.

    Un saludo master.

    ResponderEliminar
  3. Hola, Kiko

    Muchas gracias por esta entrada del blog, ¡revisar 15000 entradas una por una se me antojaba misión imposible...!

    ResponderEliminar
  4. Muchas gracias Kiko, también me ahorraste trabajo, desde ayer estaba buscando algún filtro o fórmula para detectar repeticiones. Muchas gracias.

    ResponderEliminar
  5. hola man... vi tu post... y busq opciones d ehacerlo en office 2007 y la opcion de "raika" me funciono a la perfeccion... muchas grafias a los dos... me sacaron de un problema enorme.... y me libraron de horas de trabajo.. un abrazo desde colombia.....

    Santiago

    ResponderEliminar
  6. Y si quisiera saber por ejemplo, cuantas veces se repite ese codigo del cliente en las 3 zonas, no por cada zona?

    ResponderEliminar
  7. Lo más sencillo es sumar tres CONTAR.SI
    =CONTAR.SI(B$3:B$18;B3)+CONTAR.SI(D$3:D$18;B3)+CONTAR.SI(F$3:F$18;B3)

    ResponderEliminar
  8. En primer lugar, sólo decir, que esta web es ejemplar, ya que a la vez de explicar, da ejemplos, cosa tanto rara. En 2º lugar, tengo una pregunta sobre este ejemplo: ¿como puedo sacar una lista "limpia" de los códigos de clientes y el número de repeticiones, es decir, aquí nos marca aquellos que se repiten, y luego nos pone el numero de repeticiones en cada uno de ellos, lo que busco es que nos de ese codigo con el numero de repeticiones asociadas sin más. Gracias.

    ResponderEliminar
  9. Grande KIKO, muchas gracias la verdad es que me ha servido mucho los tips que pones.

    Eres consulta obligada !

    Gracias

    ResponderEliminar
  10. Muchas gracias. Me alegra que os haya resultado útil. Un saludiño

    ResponderEliminar
  11. hola estuve mirando la formula y la verdad no se si es la que necesito...
    yo quiero dentro de una matriz de datos
    contar la cantidad de veces que se repiten, cada numero,.... de cada unidad cuantas veces esta repetida cada una.-... algo asi
    si pueden ayudarme se los agradezco de antemano

    ResponderEliminar
  12. Necesito que por favor me especifiques más la pregunta ya que entiendo que lo que comentas es lo que se resuelve en el artículo. Gracias

    ResponderEliminar
  13. Hola,

    y si las celdas contienen texto ???

    Gracias.

    ResponderEliminar
    Respuestas
    1. La fórmula expuesta funciona con texto igualmente.

      Eliminar
  14. haaa, genial con su explicacion, gracias por su tiempo y por la ayuda.

    ResponderEliminar
  15. hola, y si quisiera comprara datos repetidos en distintas columnas por ejemplo comparara lo de la columna C con la columna E

    ResponderEliminar
    Respuestas
    1. Sería el mismo caso que planteo en el post "resaltar duplicados en distintas hojas" pero sin necesidad de que los datos estén en distintas hojas.

      Eliminar
  16. Muy bien, y rizando el rizo: si quiero contar valores únicos, pero que además cumplan otras condiciones?

    ResponderEliminar
  17. Muchas gracias por tu aportación. Mejoras la vida laboral de muchos. Dios te bendiga.

    Atte:
    Pablo.

    ResponderEliminar
  18. Hola.
    ¿como puedo comparar una celda con otra y que me indique cual es la diferencia del texto que hay en ambas?
    Gracias de antemano por la ayuda

    Alberto Q

    ResponderEliminar
  19. Hola Alberto. Para intentar ayudarte necesito un ejemplo concreto de lo que quieres solucionar. Gracias

    ResponderEliminar
  20. Saludos desde Valencia Venezuela u mil gracias por su esfuerzo. Mi pregunta es la siguiente: tengo imagino tabla de 2 columnas con: código producto y cantidad vendida, donde se registran ventas de artículos que se repiten con diferentes cantidades por registro.
    En otra hoja tengo necesito hacer un resumen de cuántas unidades se vendieron de de cada articulo en una tabla simple de dos.columnas (código articulo y cantidad total vendida)...me ayudas a hacerlo? Gracias

    ResponderEliminar
    Respuestas
    1. Creo que lo que buscas lo tienes en este post: http://lareboticadeexcel.blogspot.com.es/2009/04/resumen-de-ventas-con-sumarsi.html
      Un saludo

      Eliminar
  21. Kiko buenos dias... Puede crear un tutorial similar con office 2010? Gracias!!!

    ResponderEliminar
    Respuestas
    1. Buenos días. La pregunta es ¿cómo se resuelve este mismo problema pero en excel 2010?

      Eliminar
  22. HOLA ANTES QUE NADA SALUDOS...
    SOY NUEVO CON ESTO DEL MANEJO DEL EXCEL Y HE BUSCADO EN VARIOS SITIOS PERO NO HE ENCONTRADO UNA RESPUESTA QUE YO LOGRE COMPRENDER PARA PODER SOLUCIONAR EL HECHO DE QUE EN UN MISMO LIBRO DE EXCEL CON 7 HOJAS ME EXPONGA LOS DATOS DUPLICADOS ENTRE ELLAS... QUIZAS NO LO HE PODIDO COMPRENDER DEBIDO A QUE EL EJEMPLO SE MUESTRA EN UNA MISMA HOJA SOLO EN DIFERENTES COLUMNAS.....

    ResponderEliminar
    Respuestas
    1. Hola. Tienes un post con la respuesta a tu pregunta en este mismo blog:
      http://lareboticadeexcel.blogspot.com.es/2012/03/resaltar-duplicados-en-distintas-hojas.html

      Eliminar
  23. Hola que tal.

    Me gustaria hacer una consulta.

    Necesito comparar 2 columnas y que en la tercera aparezcan los datos unicos. Es decir, si la columna "A" esta numerada del 1 al 12 y la columna "B" esta numerada del 1 al 10, necesito que en la columna "C" me aparezcan el 11 y el 12.

    Usando el formato condicional puedo identificar esos 2 registros, sin embargo las listas de mis columnas van de los 5 mil a los 20 mil registros es por esta razon que busco una opcion que en forma automatica me muestre esos registros unicos en una nueva columna.

    De antemano agradezco el apoyo y lo felicito por su pagina.

    Saludos.

    Alejandro Nava
    Chihuahua. Mexico.

    ResponderEliminar
    Respuestas
    1. Hola Alejandro. Te contesto en un nuevo post que publicaré este fin de semana. Muchas gracias y un saludo

      Eliminar
  24. QUE TAL, Y COMO PUEDO CONTAR FECHAS DUPLICADAS, SI TENGO 5 LINEAS DE CADA FECHA DEL MES O MAS, SOLO QUIERO CONTAR LOS DIAS O MEJOR DICHO CUANTOS DIAS EXISTEN DEL MES EN ESA COLUMNA..

    ResponderEliminar
    Respuestas
    1. Hola Pedro. Por favor indícame a qué dirección de mail te mando la solución. Gracias

      Eliminar
  25. Hola esta muy bueno tu ejercicio ya que me ayudo mucho, pero se puede encontrar valores duplicados y sumar los mismos.

    Por ejemplo:

    Dato1 Dato2 C
    100 400 800
    250 450 0
    400 300 0
    350 200 400
    200 250 500

    Esto como ejemplo de lo que necesito realizar.
    Me puede ayudar?
    Gracias.

    ResponderEliminar
    Respuestas
    1. Suponiendo que el primer dato (100) lo tienes en A2, puedes utilizar la siguiente fórmula para conseguir lo que necesitas:
      =SI(CONTAR.SI($A$2:$A$6;B2)=0;0;(CONTAR.SI($A$2:$A$6;B2)+1)*B2)

      Eliminar
  26. Buenas como va? me llamo Fede, tengo un pequeño problema.

    Yo lo que necesito es ponerle formato a la combinacion de 2 colmnas

    Cliente CODIGO
    1515 4007
    1515 4004
    1515 4007


    Mi idea principal es que resalte el 1ro y el tercero.

    PD: Ya utilice varios post de este foro :D

    Saludos Fede!

    ResponderEliminar
    Respuestas
    1. Hola Fede. Me puedes especificar más la pregunta? Te refieres a que quieres que dentro de una celda al combinar ambas columnas resalte el primer y tercer dígito?

      Eliminar
    2. A ver si me explico, que me aparezcan en "rojo" si A&B&F se repite en mi tabla, osea no puede haber 2 iguales, yo lo hice pero en otra hoja, quería que en la misma tabla me marque el formato condicional que establezco Si queres te mando links con imágenes, para que se entienda mejor.

      Saludos Fede!

      Eliminar
    3. Si tienes una columna en la que has concatenado (&) códigos de distintas columnas en un solo código y quieres ver si se repiten, debes seleccionar dicha columna (en mi ejemplo sería A1:A10) y en Formato Condicional establecer la siguiente fórmula:
      =CONTAR.SI($A$1:$A$10;A1)>1 y seleccionar un formato de relleno.

      Eliminar
  27. Estimado Kiko:
    Yo tengo un problema parecido a nuestro compañero Fede. Tengo datos en varias columnas A&C&E que no se deben repetir en conjunto a lo largo de TODA la columna. Ej: A2&C2&E2<>An&Cn&En

    ResponderEliminar
    Respuestas
    1. Hola Javier, como contesté a Fede, Si tienes una columna en la que has concatenado (&) códigos de distintas columnas en un solo código y quieres ver si se repiten, debes seleccionar dicha columna (por ejemplo A1:A10) y en Formato Condicional establecer la siguiente fórmula:
      =CONTAR.SI($A$1:$A$10;A1)>1 y seleccionar un formato de relleno. Un saludo

      Eliminar
  28. Hola Kiko:
    queria ver si me puede ayudar en lo siguiente:
    tengo una columna llena de códigos por ejemplo:
    1006543123
    1006548920
    1006549877
    1006548799
    7991040781
    7991087953

    lo que quisiera es que me carque todos los que empezan con 79910 y el resto no interesa, de igual forma los 10065.
    Como podria identificar estos datos.
    Muchas gracias

    ResponderEliminar
    Respuestas
    1. Te he mandado la solución a tu correo. Ya me dirás si es lo que necesitabas. Un saludo

      Eliminar
  29. Hola,
    Tengo un listado de casi 8mil personas que pertenecen a núcleos familiares diferentes identificados con codigos.
    Ejemplo:
    2234 Laura Diaz
    2215 Ana Torres
    2208 Carlos Lopez
    2234 Cesar Sol

    Yo aplico la función ordenar y los acomodo por códigos, pero necesito saber cuantos nucleos familiares hay (cuantas familias de acuerdo al codigo dentro de las 8000 personas, ejemplo saber si hay 300 familias o 500 familias) y no cuántas veces se repite el código (el número de personas que conforman la familia).
    Te agradezco de antemano tu colaboración.
    UN abrazo desde Colombia

    ResponderEliminar
  30. Buenos días. Puedes ver la solución en: http://lareboticadeexcel.blogspot.com.es/2012/07/contar-registros-unicos.html
    Un saludo

    ResponderEliminar
  31. Que tal Kiko, muy buena la explicación. Yo necesito algo parecido, trato de explicarlo:

    En una lista de datos, los cuales se repiten varias veces, necesito resaltar aquellos datos que se repitan mas de dos veces, pero que las dos primeras veces que aparezcan no se marquen. Me explique bien? Es posible hacer esto?

    Muchas gracias,
    Saludos

    ResponderEliminar
    Respuestas
    1. Te contesto en el post de hoy. Un saludo

      Eliminar
  32. Hola Kiko, yo tengo el siguiente problema tengo en la columna A un código conformado por 3 dígitos entre 200 y 800 a esto les llamo ZONAS, y en la columna B, del 1 al 50 le llamo EQUIPOS, las zonas se pueden repetir pero no los equipos, sin embargo, estoy detectando que tengo varios equipos de la misma zona, por ejemplo:

    zona equipo
    305 11
    305 12
    305 13
    305 11
    305 16
    305 17
    305 18
    305 19
    305 20
    305 21
    305 20
    305 52
    305 53
    331 1
    331 2
    331 3
    331 4
    331 5
    331 6
    331 6
    331 8
    331 9
    331 10

    Si te das cuenta se están repitiendo 3, pero esta base es de 3443 registros, no puedo eliminar ningún registro (sino lo haría con QUITAR DUPLICADOS), ya que estos están relacionados a nros. telefónicos únicos,

    yo trate de hacerlo con: =CONTAR.SI($B$2:$B$34&$C$2:$C$34,B2&C2)>1 para probar pero no me sale... me podrías por favor ayudar con esta formula.

    gracias,

    ResponderEliminar
    Respuestas
    1. Mándame un mail y te envío la solución

      Eliminar
  33. HOLA KIKO RIAL, SOY MAX. YO TENGO EL MISMO PROBLEMA CON UN ARCHIVO DE CODIGOS Y YA HICE LA FORMULA QUE HAS MENCIONADO Y SI ME MARCA LOS REPETIDOS, PERO HABRA ALGUNA FORMULA PARA QUE ESOS REPETIDOS SE PUEDAN AGRUPAR HACIA LAS CELDAS DE ABAJO Y QUEDEN LOS NUMEROS NO REPETIDOS EN LA PARTE DE ARRIBA.
    AGRADEZCO MUCHO TU APOYO Y SALUDOS CORDIALES.

    ResponderEliminar
    Respuestas
    1. Creo que lo que buscas es muy similar a lo que publiqué en este post: http://lareboticadeexcel.blogspot.com.es/2013/02/lista-de-valores-unicos-con-formulas.html
      Un saludo

      Eliminar
  34. Hola Kiko,
    tengo una duda, tengo varias fechas, en la columna b esta la fecha inicial, y en la c la fecha final, cada una de ellas son los diferentes trabajos que una persona ha tenido, en total ha tenido 8 trabajos (por lo tanto 8 fechas iniciales y 8 fechas finales), como puedo formular para que las celdas me detecten cuando alguna fecha se empalme con respecto de las demas?? gracias

    ResponderEliminar
  35. Hola Alvaro. Por favor mándame un ejemplo a kiko.rial@gmail.com con el detalle de lo que quieres conseguir. Gracias

    ResponderEliminar
  36. kiko, saludos necesito ayuda! expreso mi problema asi, tengo que contar cuantos medicamentos que tengo en existencia tienen disponibilidad para un periodo limitado; por ejemplo un listado de 250 medicamentos, Cuantos tengo disponibles para (entre 1 y 2 meses), casi lo logre utilizando contar.si.conjunto pero no cuenta los repetidos y es necesario agregarlos

    de antemano, mil gracias.

    ResponderEliminar
  37. Hola. Por favor mándame una hoja con un ejemplo y lo que quieres conseguir exactamente a kiko.rial@gmail.com

    ResponderEliminar