Resaltar duplicados en Google Sheets

Identificar Duplicados en Google Sheets
— by

Una de las principales situaciones con las que te vas a encontrar en cuanto al análisis de datos en Google Sheets, es el manejo de registros y celdas duplicadas. Bien sea porque las debes eliminar, o simplemente porque necesitas identificar los duplicados.

Hay muchas formas y técnicas de hacer esto, dependiendo de los datos y el objetivo.

Hoy vamos a ver algunas de estas técnicas para resaltar o marcar los duplicados en Google Sheets (Muchas de estas también te funcionarán en Excel).

Puedes acceder a una plantilla con decenas de ejemplos de funciones que identifican duplicados aquí.

Duplicados en Google Sheets

Primero debemos ponernos de acuerdo en lo que consiste un duplicado para después solucionarlo, o trabajarlo.

En el caso de una lista, es decir de una tabla de una sola columna, es muy fácil. Un duplicado es un valor que se repite, como en el siguiente ejemplo:

Ya cuando tenemos una tabla con varias columnas, normalmente hablamos de duplicados cuando toda la fila se repite.

Pero también puede darse un tercer caso, donde se repita algún identificador (como el nombre, la referencia, u otro). En este caso usaríamos otras técnicas.

¿Por qué identificar duplicados?

A veces tenemos que identificar duplicados cuando realizamos auditoria de alguna hoja de cálculo, por varias razones:

  1. Para ver posibles errores de la base (Si fuera una llave única no debería tener duplicados)
  2. Encontrar registros puestos dos veces por error
  3. Identificar campos que si deberían estar duplicados
  4. Después de haber juntado, copiado y pegado o eliminado registros.
  5. O simplemente queremos ver, por ejemplo las diferentes ventas de un mismo cliente, o de un mismo vendedor

Ya después podemos decidir que hacer con ellos.

Muchas veces vamos a querer eliminarlos (Aquí un tutorial entero de cómo eliminar filas duplicadas), pero a veces solo queremos «marcarlos» o asegurarnos que sean duplicados. Esto es lo que vamos a ver hoy

Suscríbete para acceder al curso «Descubriendo el poder de Google Sheets» y empezar tu camino a la maestría de Sheets, Forms y Apps Script

Cómo marcar duplicados en Sheets

Hay varias maneras de identificar duplicados, y todo depende de lo que queramos hacer exactamente.

Una manera de identificar los duplicados es usando una nueva columna que identifique un registro como repetido o no.

Otra forma, que puede ser más conveniente, es usando formatos condicionales, para poner un color especial o negrita a las celdas o filas que estén duplicadas

Otra cosa que podemos hacer es identificar todas las instancias del repetido, o que me deje el «original» din marcar

Por otro lado, también depende de que tanto control tengamos sobre la base, y que tanto podamos modificarla, reordenarla o agregar nuevas columnas. Estas son algunas de las formas en que podemos resaltar duplicados:

  1. Ordenando y añadiendo una columna auxiliar formulada.
  2. Ordenando y añadir un formato condicional
  3. Añadir una columna auxiliar (sin ordenar)
  4. Añadir un formato condicional (sin ordenar)

A continuación verás en detalle algunas de estas.

Ordenar los datos

La manera más fácil de ver rápidamente si hay valores repetidos es ordenando por la llave o alguna columna de referencia (Aquí todo acerca de ordenar datos en Google Sheets)

Una vez ordenas, tienes dos opciones: La primera es añadir una columna adicional, con una fórmula que me avisa si el valor es repetido o no. Para esto, seguimos los siguientes pasos:

  1. Agregar un filtro
  2. Ordenar los datos por la columna de referencia.
  3. En la primera celda de la columna siguiente introducimos la fórmula =SI(A2=A1;»REPETIDO»;»OK»)
  4. Arrastramos la fórmula, o la copiamos y pegamos.

Esta metodología es ideal para cuando queremos hacer algo adicional a los duplicados: eliminarlos, copiarlos y pegarlos, o alguna otra acción.

Homologación Excel: Este método funciona exactamente igual en Excel

Nota: Con este método dejamos el original sin marcar

Suscríbete para acceder al curso «Descubriendo el poder de Google Sheets» y empezar tu camino a la maestría de Sheets, Forms y Apps Script

Ordenar y añadir una columna condicional y un formato condicional

Al método anterior le podemos agregar una cosa más y es un formato condicional para que nos ayude a identificar de manera más visual y de verdad destaque todos los resaltados.

Adicional a los pasos anteriores, añadimos lo siguiente:

  1. Seleccionamos la columna B.
  2. Clic derecho y escogemos «Formato Condicional»
  3. Escogemos la condición «La celda es igual a»
  4. Escribimos «REPETIDO»

Homologación Excel: En Excel se haría todo igual, excepto que el formato condicional se aplica de manera un poco diferente.

Ordenar y añadir un formato condicional

Si solo queremos identificarlos visualmente, una buena opción es incluir la fórmula anterior en un formato condicional directamente, quitando así la necesidad de incluir una columna auxiliar.

En otras palabras, fusionamos los dos métodos anteriores en uno solo:

Lo haríamos así:

  1. Escoger la columna B (Desde la fila 2)
  2. Una de dos:
    1. Ir a Formato > Formato Condicional
    2. Clic derecho y escoger «Formato Condicional»
  3. En «Reglas de Formato», escoger, «La fórmula personalizada es:»
  4. En el campo escribir «=A2=A1», asumiendo que la primera celda del rango es A1.
  5. Poner cualquier formato que resalte los repetidos.

Resaltar toda la fila

Podemos hacer una pequeña modificación para que resalte toda la fila y así hacer que sea más visible la columna repetida.

Para esto simplemente cambiamos el rango del formato hacemos un pequeño cambio en la formula del formato condicional. Para esto:

  1. Seleccionamos todo nuestro rango
  2. Vamos a «Formato Condicional»
  3. Asegurar que el rango incluya las dos columnas (En nuestro caso A2:B14)
  4. En «Reglas de Formato», escoger «La fórmula personalizada es:»
  5. Cambiar la fórmula para que quede fija la columna identificadora (en nuestro caso la A, y quedaría así =$A2=$A3)

Añadir una columna auxiliar formulada (sin tener que ordenar)

Esta es una de las metodologías que más me gusta.

Con la ayuda de la función CONTAR.SI, podemos ver si un registro está más de dos veces en la columna.

Hay dos formas de hacerlo. Una es que resalte todas las instancias del valor duplicado (la forma fácil), otra es que deje la primera sin resaltar (Algo así como el original, o el innovador). Igual que en los primeros ejemplos. De nuevo, todo depende de lo que quieras hacer con los duplicados.

Identificar todos los duplicados

  1. Nos situamos en la columna a la derecha del primer dato e ingresamos la fórmula =SI(CONTAR.SI(A:A;A2)>1;»REPETIDO»;»ORIGINAL»)
  2. Arrastramos la fórmula
  3. También podríamos añadir un formato condicional para hacerlo más visual, tal como hicimos en los ejemplos anteriores.

Homologación Excel: Esta fórmula se puede aplicar exactamente igual en Excel

Identificar los duplicados pero no el «original»

El método anterior es muy fácil, pero a veces vamos a querer que nos deje sin marcar el original, es decir la primera ocurrencia de algún valor repetido, tal como en los primeros ejemplos.

Para lograr lo anterior, repetimos los pasos del último ejemplo pero tenemos que modificar nuestra fórmula, a:

=SI(CONTAR.SI($A$2:A2;A2)>1;»REPETIDO»;»OK»)

Homologación Excel: Esta fórmula se puede aplicar exactamente igual en Excel

Formato condicional para resaltar los duplicados

Esta es, en principio, la forma más fácil, pero no me gusta que necesariamente resalta todas las instancias del duplicado.

No deja «el original», como me gusta, pero puede ser útil en muchas ocasiones.

  1. Escoger la columna donde estan los datos
  2. Una de dos:
    1. Ir a Formato > Formato Condicional
    2. Click derecho y escoger «Formato Condicional»
  3. En «Reglas de Formato», escoger, «La fórmula personalizada es:»
  4. En el campo escribir «=CONTAR.SI(A1:A;A1)>1», asumiendo que la primera celda del rango es A1.
  5. Poner cualquier formato que resalte los repetidos.

En muchos artículos en la web encontrarán solo esta última. Me parece que no es lo suficientemente flexible, y si conocen todas las anteriormente expuestas tienen un kit de herramientas muchísimo más amplio para enfrentar este y otros retos en Google Sheets.

Conclusión

Pudiste ver varios métodos distintos para resaltar duplicados en Google Sheets. Es importante conocerlos, porque depende de que tanto puedan modificar la base original y de lo que quieran hacer con sus duplicados

¿Qué tal te parece? ¿Tienes algún otro método? ¿Tienes algún otro caso que no haya mencionado? Cuéntame en los comentarios.

Suscríbete para acceder al curso «Descubriendo el poder de Google Sheets» y empezar tu camino a la maestría de Sheets, Forms y Apps Script

Boletín

Nuestras últimas noticias en tu correo electrónico

Respuestas

  1. Avatar de DAVID FRANCISCO PUERTAS
    DAVID FRANCISCO PUERTAS

    Hola.
    He procurado sin éxito colorear de rojo las columnas B, C, y L utilizando la siguiente fórmula en el formato condicional: =Y(CONTAR.SI(B:B;B4)>1;CONTAR.SI(C:C;C4)>1);CONTAR.SI(J:J;J4)>1)
    Sería posible que me orientes al respecto?

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola!

      Te toca hacer 3 formatos condicionales diferentes, uno por columna

      Saludos!

  2. Avatar de Niko

    Hola, soy solo un aficionado. Me detengo a publicar un agradecimiento a JuanSguzman por su gran ayuda a todos. Inclusive a mi, sin entender mucho del asunto. Muchas gracias Guzman por ser tan amable. Dios te bendiga.

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola!

      Que amables palabras! Mil gracias!

      Saludos!

  3. Avatar de YULISSA
    YULISSA

    HOLA TENGO 4 COLUMNAS QUE QUIERO QUE ME RESALTE LOS DUPLICADOS

    EJEMPLO:
    COLUMNAS A B C D
    10 15 20 25
    11 25 21 15
    25 10 20 11

    QUE FUNCION PUEDO USAR SOLO QUIERO QUE ME RESALTEN LOS DUPLICADOS COLOR ROJO ESPERO QUE ME AYUDEN LO MAS PRONTO GRACIAS

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola!

      Aquí te dejo un video

      https://youtu.be/lWIIGIhei3w

      Saludos!

  4. Avatar de Paloma
    Paloma

    Hola! he intentado todas las opciones que nombraste y de ninguna manera puedo hacer que las casillas de la columna f puedan resaltarse los duplicados. Vi que se resaltan algunos repetidos pero otros repetidos no lo he verificado buscandolo con ctrl+f buscandolo. Podrias ayudarme pues frustrada estoy.

  5. Avatar de Sofia
    Sofia

    Hola vi todos tus videos pero no encuentro la solución para mi duda.

    Lo que necesito es que tengo 2 columnas en una misma hoja. Quiero comparar y que resalte en la columna A si es que se repite el nombre en la columna B. No quiero que se resalte si es que se repite en la misma columna A, solo debe resaltar si está en la columna B. Y si se repite 2 veces que se marque de otro color.

    Lo que me pasa es que se resalta cuando se repite en cualquier columna, y yo quiero que se resalte solo al momento de comparar con la columna B.

    Espero se entienda mi duda,

    Gracias

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola Sofia

      Te remito a estos dos videos donde hay muchos casos avanzados

      https://youtu.be/wWEKbOBDxgg
      https://youtu.be/GY-MusvzE8M

      ME cuentas si lo encuentras

  6. Avatar de Cecilia
    Cecilia

    Hola! Necesito saber si existe una formula que resalte el último dato que ingreso. Aplique la formula Contar.Si pero me marca en rojo el dato original. Manejo planillas con mas de 3000 filas y se me hace muy laborioso. Me gustaria, al ingresar un nuevo dato en la columna, que se marque en rojo si el dato ya esta presente. Desde ya muchas gracias!

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola Cecilia, aplica contarsi()>=2 para que solo se te resalte la nueva

      Saludos!

      1. Avatar de vanina
        vanina

        Hola!, me pasa lo mismo que a Cecilia, aplique la formula que le has dicho pero sigue resaltando el dato original.

        1. Avatar de jsguzmanb
          jsguzmanb

          Hola!

          Lograste resolver?

          Saludos!

  7. Avatar de Lupita
    Lupita

    ¡Hola! tengo una consulta, se me ha estado dificultando para ver cómo le puedo hacer en el siguiente caso: tengo una base de datos en la cual se ingresan facturas, sin embargo, sucede que a veces la factura llega dos veces y se duplica su ingreso, pensaba poner un formato condicional para que en el número de la factura se marcara si ese número ya había sido ingresado, sin embargo, me comentan que distintos proveedores pueden tener el mismo número de factura sin necesariamente ser la misma, por lo tanto no sabría cómo hacer ahí, no sé si habrá alguna manera de concatenar la columna de orden de compra, nombre de proveedor y número de factura para que solamente marque cuando se repitan en otra misma linea, no sé si me di a entender.

    Saludos.

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola! Interesante

      Ya viste estos dos videos?

      Identificar celdas y filas duplicadas en Google Sheets – Versión 2020 – https://youtu.be/lWIIGIhei3w
      Duplicados Avanzados en Google Sheets – Duplicados en filas completas, en otras hojas y libros – https://youtu.be/fegqRRDbEjA

      Me cuentas!

  8. Avatar de Felipe
    Felipe

    Hola, estoy aplicando un formato condicional de forma simple usando la fórmula =COUNTIF(A1:A;A1)>1

    Por alguna razón solo me resalta el primer elemento repetido y no el segundo (ver captura aquí: https://i.imgur.com/TnVuq89.png )

    ¿Sabes por qué me pasa esto?
    Saludos!

    1. Avatar de Felipe
      Felipe

      ..en los comentarios encontré la solución. Reemplazar
      =COUNTIF(A1:A;A1)>1

      por =COUNTIF(A:A;A1)>1

      Saludos!

      1. Avatar de Xochitl
        Xochitl

        Gracias Felipe

        Era justo lo que estaba buscando.

        Saludos.

  9. Avatar de Jose Valencia Bautista
    Jose Valencia Bautista

    Hola, tengo una consulta.

    como resaltar valores duplicados pero dos columnas, decir, resalte las celdas con valor duplicado de columna A y B.

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola Jose

      Te dejo dos videos donde hay algunos casos avanzados que creo que te va a servir

      https://youtu.be/lWIIGIhei3w

      https://youtu.be/fegqRRDbEjA

      Saludos!

  10. Avatar de peters
    peters

    Hola, tengo una consulta.
    necesito resaltar los repetidos de una fila, los datos que van en cada celda se obtienen de una lista desplegable.

    no logro hacer funcionar la condición REPETIDO, ni el contar.si.

    gracias por la ayuda

    1. Avatar de peters
      peters

      la lista despegable es de nombres de personas

      1. Avatar de jsguzmanb
        jsguzmanb

        Hola!

        No te debería dar ningun problema

        Si quieres, dejame ver la copia de tu archivo y lo miro rápidamente

        Saludos!

  11. Avatar de Angela
    Angela

    Hola, quiero resaltar todos los duplicados encontrados en una tabla, por ejemplo C1:E1500, pero solo resalta los que encuentra en la columna C. Como lo consigo? que fórmula debo usar?

  12. Avatar de Angela
    Angela

    Hola, tengo una base de datos de contactos con más de un número de teléfono y quiero encontrar (resaltar de un solo color) todos los duplicados en un rango, por ejemplo: C1:E1500. Que fórmula puedo usar?. Ya he usado CONTAR.SI(C:E,C1)>1, pero no resalta los duplicados de la columna D y E, solo resalta los duplicados de la columna C.

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola

      Intenta con esta:

      CONTAR.SI(C:E,$C1)>1

      Me cuentas si te funciona

      Saludos!

  13. Avatar de Rodrigo Navarro B olado
    Rodrigo Navarro B olado

    Hola! La tercera fórmula es posible aplicarla no con números, si no con texto y varias columnas?

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola Rodrigo

      A que te refieres con texto

      Como esta, te encuentra duplicados sean numeros o textos

      En cuanto a las filas enteras, hay que hacer un par de modificaciones de las que hablaré en un proximo artículo.

      Saludos!

  14. Avatar de Angel Garcia
    Angel Garcia

    Dar difrente color a cada uno de los valores repetidos

    1. Avatar de jsguzmanb
      jsguzmanb

      Wow. Buena sugerencia

      Se me ocurre hacer formatos condicionales para los diferentes valores de contar.si

      Si contar.si da 2, un color, si da 3, otro y asi.

      Muy interesante

  15. Avatar de gabriel
    gabriel

    Hola, como podria aplicar la ultima forma, la de CONTAR.SI(A:A;A1)>1 pero que tome los datos de dos columnas? es decir, busque y resalte los duplicados en las columnas A y B. Tengo dos columnas donde cada una indica dos numeros telefonicos y quiero saber si coincide algun numero con otro. Agradecido por la guia

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola Gabriel

      Si estas buscando duplicado sin importar la fila, simplemente amplia la formula a las dos columnas

      seria CONTAR.SI(A:B;A1)>1

      Me cuentas si te sirve.

      Saludos!

      1. Avatar de gabriel
        gabriel

        Hola, si me resulto, pero solamente me resalta los duplicados de la columna A. Aunque si toma en cuenta los resultados de la columna B solo resalta los de la columna A, agradecido de antemano por la ayuda ya brindada

  16. Avatar de Elizabeth Flores
    Elizabeth Flores

    Excelente, me sirvio bastante. Muchas gracias

  17. Avatar de Sofia
    Sofia

    Hola! Como le pongo el formato condicional a =SI(CONTAR.SI(A:A;A2)>1;»REPETIDO»;»ORIGINAL»)

    Me trae perfecto el resultado, pero me serviria que tambien me resalte las que aparecen como «REPETIDO»

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola Sofia

      Gracias por la pregunta

      Escoge la columna.

      Dale click derecho, formato condicional

      En Formato Condicional, incluye la condición «El texto es exactamente»

      y en el campo pones REPETIDO

      Cuéntame si te funciona.

      Saludos!

  18. Avatar de gonzalo
    gonzalo

    Muchas gracias
    encontre lo que buscaba.
    un abrazo

  19. Avatar de inma
    inma

    Hola buenas,
    tengo una fila con datos todos diferentes y cuando aplico el formato condicional me considera algunos como repetidos, y no entiendo porque. Podrias ayudarme?

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola. Enviame un pantallazo de tu base, dejándome ver la fórmula de alguna celda. Un gran saludo!

      1. Avatar de inma
        inma

        905901680915082013722020-905901680915082091007061
        905901840915082062770071-905901840915082091007062
        905901030915082062770072-905901030915082091007063
        905901190915082062770073-905901190915082091007064
        905901350915082062770074-905901350915082091007065

        Empiezan igual pero terminan diferentes y los considera como valores repetidos
        la formula que utilizo en el formato condicional es esta =CONTAR.SI($C$6:$C$20;C6)>1

  20. Avatar de FRANCO
    FRANCO

    HOLA QUE TAL SABES QUE ESTA FORMULA NO ME LA ESTA TOMANDO =SI(CONTAR.SI(A:A;A2)>1;»REPETIDO»;»ORIGINAL») SABRAS EL POR QUE?

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola!

      Te sale algún error?

      O simplemente no resalta los duplicados?

      ME envias un pantallazo?

      Gracias!

  21. Avatar de Pablo Araujo
    Pablo Araujo

    Muchas gracias! Me sirvio perfecto, va joya con esta CONTAR.SI(A:A;A1)>1

    1. Avatar de jsguzmanb
      jsguzmanb

      Excelente!

      Me alegro. Saludos!

  22. Avatar de Johann Barrero
    Johann Barrero

    Hola, a parte de que resalte los repetidos hay una manera de que resalte todos los duplicados, con la última formula =CONTAR.SI(A1:A;A1)>1 me resalta uno de los 2 datos repetidos pero no me resalta los 2 datos duplicados.

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola Johann

      Tienes razón.

      Cambialo por =CONTAR.SI(A:A;A1)>1

      Buena corrección.

      Un gran saludo!

  23. Avatar de jsguzmanb
    jsguzmanb

    Hola!

    Gracias por escribir

    Si claro, si tu columna esta en la columna A, y te ubicas por ejemplo en la fila 7, puedes usar la formula

    =COINCIDIR(A7;$A$1:A6;0)

    Despues la arrastras

    Esto te va a devolver el numero de la fila de la primera coincidencia del repetido. Si no esta repetido, te trae un error. Si hay mas de uno repetido solo te va a traer la primera que encuentre.

    Me cuentas si te sirve.

    Saludos!

  24. Avatar de IC
    IC

    excelente, la ultima funciono correctamente, gracias!

    1. Avatar de jsguzmanb
      jsguzmanb

      Excelente! Me alegro. Estoy trabajando en una macro para que sea aún más sencillo con un solo botón.

      La compartiré más adelante

      Un gran saludo!

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.

Pin It on Pinterest

Share This