miércoles, 24 de octubre de 2012

Detectar valores duplicados en una columna de Excel



De forma distinta al artículo anterior, en el que se trataba de "Detectar diferencias entre 2 columnas en Excel", vamos en esta ocasión a aprender un "truco" para detectar valores repetidos más de una vez, dentro de una misma columna.

Insistimos en que vamos a "detectar", no a eliminar duplicados. Para esto último, Excel tiene una herramienta que podemos encontrar en la ficha Datos / grupo Herramientas de Datos / Quitar duplicados


Además, y de la misma forma que lo hacíamos en el artículo anterior, lo vamos a hacer con una utilización avanzada de "Formato condicional". Es decir, sin necesidad que crear una columna auxiliar con fórmulas, sino que incluiremos una función "dentro" del formato condicional.


Detectar un valor repetido más de una vez en una columna (forma sencilla)

Si sólo queremos saber los valores que están repetidos (independientemente del nº de veces), se puede conseguir directamente utilizando una de las opciones de Formato Condicional (después de seleccionar el rango donde suponemos que habrá valores repetidos):

Ficha Inicio / Formato condicional / Resaltar reglas de celdas / duplicar valores

Detectar un valor repetido x veces en una columna (forma avanzada)

Vamos a aprender a conseguir lo mismo que antes, pero de una forma algo más avanzada, lo que nos permitirá tener más control en algunos casos. Este mayor control consiste en que, no sólo podremos saber  los valores que están repetidos más de una vez, sino que podemos discriminar aquellos que están repetidos más de x veces sólamente.... o bien aquellos que están repetidos exactamente x veces.

En primer lugar, debemos seleccionar el rango de celdas al cual queremos aplicar la detección de diferencias. Elegimos, por ejemplo, la columna 2 (el rango C3:C9) porque queremos saber si en esa columna ha hay algún valor repetido más de una vez. ¡¡Atención, se debe seleccionar con el ratón de arriba hacia abajo, para que quede activa la celda de arriba y después funcione el formato condicional!!


Vamos a la ficha Inicio / Formato condicional.../Nueva regla... / Utilice una fórmula que determine... y escribimos en el campo "Dar formato a los valores donde esta fórmula sea verdadera" la siguiente fórmula: 


El siguiente paso es dar el formato deseado desde el botón que encontraremos más abajo y presionamos "Aceptar". El resultado será similar al siguiente:


El motivo de utilizar la función =CONTAR.SI ($C$3:$C$9;C3)>1 es que conseguimos que el formato condicional reconozca en cada celda si el valor contenido en ella está repetido más de una vez (por ello se escribe > 1 al final) en todo el rango C3:C9.

Vemos que el rango C3:C9 se escribe con forma de referencia absoluta: $C$3:$C$9;C3 , sin embargo, la referencia C3 NO se debe escribir entre "signos dólar". El motivo de que la celda C3 deba ser una referencia relativa es posibilitar al formato condicional que se aplique a todo el rango (el formato condicional, aunque no lo hayamos puesto, va leyendo celda a celda aumentando un nº de fila: C3, C4, C5, C6...etc.).

Es muy fácil intuir que si queremos detectar sólo los valores que están repetidos más de dos veces, deberíamos cambiar >1  por  >2 ..... y las demás posibilidades son fáciles de imaginar. Por ejemplo, podríamos marcar aquellos valores que estén repetidos exactamente 3 veces, tan sólo cambiando > por =3.


Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:









34 comentarios:

  1. A esta fórmula le falta un paso, o al menos yo lo tuve que hacer, al establecer la regla como se menciona perfectamente en el artículo, tuve que ir a administrar reglas y en "se aplica a" volver a definir el rango, solo así surtió efecto, creí que había seleccionado mal al principio, como enfatiza en el artículo, pero lo volví hacer en otra pc y fue lo mismo, saludos y muchas gracias!

    ResponderEliminar
    Respuestas
    1. Después de volver a hacer tal y como pone el artículo el formato condicional, se aplica perfectamente sin necesidad de volver a redefinir el rango.

      Quizá te falte o sebre algún signo $ , no se. Revísalo.

      Gracias por leernos y un saludo.

      Eliminar
  2. Como podria hacerce para que detecte el repetido al momento de ingresar el dato en la celda (o sea, saber si el dato ingresado ya esta dentro de la columna)? Gracias! seria de gran utilidad

    ResponderEliminar
    Respuestas
    1. 1. Suponiendo que el rango donde vayamos a introducir los datos sea A1:A10, vamos a la ficha Datos y después hacemos clic en Validación de datos.

      2. Seleccionamos Permitir:Personalizada y en "Fórmula" escribimos:
      =CONTAR.SI($A$1:$A$10;A1)<2

      3. Aceptamos y salimos del cuadro.


      Un saludo

      Eliminar
    2. Muchas gracias!!, me sirvió de mucho.
      No se si es demasiado pedirte, pero debería hacerlo también en calc, y en "validez" no tiene la opción de ingresar una formula personalizada...bueno, es un comentario por las dudas también conozcas como hacerlo con calc.(porque en la pc de mi trabajo se usa libreoffice)
      Muchas gracias nuevamente!!
      Saludos

      Eliminar
  3. Hola, he logrado aplicar este procedimiento para detectar repetidos en cada fila. Pero ¿Cómo aplicar esto a todas las filas a través de un sólo procedimiento?

    Gracias y saludos.

    ResponderEliminar
  4. ami me interesa dar el resultado de verdadero o falso en una fila si tengo numeros repetidos osea si entre A1 Y JI tengo numeros repetidos en la celda k1 me de el resultado de verdaero o falso y cambiar el verdadero por el numero 1 y el falso por 0 para despues hacer una suma de antemano mil gracias

    ResponderEliminar
    Respuestas
    1. En cada fila, puedes poner en una columna al lado del rango principal (la col. B), la expresión =CONTAR.SI($A$1:$A$8;A1) para detectar la cantidad de repetidos de cada item en dicho rango principal (en el ejemplo A1:A8).
      Después, debajo de todas las expresiones que has introducido en la columna B, puedes poner la expresión: =SI(CONTAR.SI(B1:B8;">1")>0;1;0) y te dirá, devolviendo un nùmero 1, si hay algun item repetido en el rango principal de la columna A.
      Después, ya puedes sumar los numeros 1 normalmente.

      Eliminar
  5. hola como puedo quitar valores repetidos ejemplo tengo columnas enunciadas como fecha, lote y tabla; si tengo dos o tres veces el mismo lote y tabla pero con diferente fecha como le hago para que me deje la fecha mas reciente

    ResponderEliminar
  6. Todas las columnas deberán tener el mismo tamaño?
    Gracias

    ResponderEliminar
  7. Hola, me podrias ayudar? necesito una formula para localizar números duplicados en la columna A y si los encuentra los coloree solo si en la columna F los nombres coinciden, esto lo necesito para evitar el caso de poner una factura del mismo proveedor 2 veces o mas,
    saludos y muchas gracias

    ResponderEliminar
    Respuestas
    1. 1) Genera una columna nueva que concatene el dato de cada celda de la columna A, con el dato de cada celda de la columna F.
      2) Seleccionas toda la nueva columna generada
      3) Vas a Inicio / Formato condicional / Resaltar reglas de celdas / Duplicar valores.


      De esta forma detecta los registros que tanto en la columna A (número) como en la columna F (Proveedor) son iguales... que todo ello es el indicativo inequívoco de una factura registrada de forma duplicada.

      Si no quieres que los datos unidos de la columna nueva se vean, aplícale un color de fuente blanco, das un ancho de columna pequeño (para que cada celda sea un cuadradito) y así solo veras los cuadraditos coloreados de los duplicados.

      Esta es una de las soluciones más fáciles de explicar por aquí... aunque hay otras más complicadas de explicar que pueden ser más óptimas.

      Eliminar
  8. Hola, quiero que me ayudes con lo siguiente: tengo una base de datos y solo quiero que me resalte la celda cuando repito los tres datos a la vez en otra fila.
    Por ejemplo:

    carlos 20/05/2014 9:00 am (resaltar)
    carlos 20/05/2014 11:00 am
    jesus 20/05/2014 10:00 am
    carlos 20/05/2014 9:00 am (resaltar)

    gracias

    ResponderEliminar
  9. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  10. NOOO FUNCIONA NADA DE ESO YA LO INTENTE KM ESTA EN LA FORMULA Y SE QUE HAY DATOS DUPLICADOS A PROPOSITO Y NO SALE NADA

    ResponderEliminar
  11. Hola, yo necesito, una vez encontrados los valores repetidos (numéricos) de una columna, que me devuelva el valor, es decir, me lo muestre en la celda donde inserto la fórmula. Le estoy dando mil vueltas sin conseguirlo.
    Gracias

    ResponderEliminar
  12. Hola. Tengo una columna con las fechas de cumpleaños de un numero de personal, quisiera resaltar con colores, las personas que cumplen el mismo mes. Le he dado la vuelta y no hay forma. Agradecido de antemano

    ResponderEliminar
  13. Una consulta, cual sería la formula para que no se puedan ingresar a las celdas signos prohibidos como ¿,?, ", +.*

    ResponderEliminar
  14. Solo respondemos dudas relacionadas directamente con el artículo, siempre y cuando la pregunta está formulada de forma inequívoca.
    Ofrecemos formación y consultoría para otros servicios que necesiten, previo presupuesto.
    Saludos y gracias por leernos.

    ResponderEliminar
  15. Buenas, me funciona a medias, lo que yo quiero hacer es que me reconozca numeros de serie duplicados, el problema que tengo es que estos numeros de serie constan de 15 digitos aproximadamente y los 10 primeros son siempre iguales, esto hace que por alguna razon me reconozca todos como duplicados, que podria hacer?

    ResponderEliminar
    Respuestas
    1. He vuelto a comprobar los resultados con la circunstancia que comentas (15 dígitos de los cuales los 10 primeros son siempre iguales) y solo reconoce los que realmente están duplicados.
      Quizá algo se te escapa y no lo haces bien. Repasa todo de nuevo administrando la regla de formato condicional.
      Saludos.

      Eliminar
  16. Como puedo buscar un Valor en una Tabla de excel y al mismo tiempo que lo encuentre los Marque, lo palome, le cambie el formato a la celda o al texto (lo que sea) pero saber que articulo ya se encontró en una tabla muy grande en excel.
    Nota. Es básicamente es la opción de buscar y remplazar pero la necesito con formulas y todo estarlo manejando desde una sola celda y que se estén remplazando los datos ya encontrados incluso en otra hoja

    ResponderEliminar
    Respuestas
    1. El texto de tu mensaje no se entiende bien. Por ejemplo, no se lo que significa "lo palome" y creo que saberlo puede ser importante para la respuesta.
      En cualquier caso, creo que lo que pides necesita la realización de un procedimiento en Visual Basic for Applications.

      Eliminar
  17. Buen dia. Muy bueno el articulo.
    Lo intente y me resalto todos los que estaban repetidos, pero lo que necesito es que me identifique cada uno de los repetidos de diferentes colores. Me puedes ayudar por favor

    ResponderEliminar
  18. Muy buen articulo

    Lo hice, pero lo que necesito es que cada tipo de coincidencia se colore de diferentes colores. Me podrias ayudar con eso por favor.

    ResponderEliminar
  19. Se podría hacer mediante funciones como CONTAR.SI y ALEATORIO.ENTRE utilizadas en otras columnas auxiliares y después aplicando un formato condicional de escala de colores. Pero no puedo responder en un comentario de un post a una pregunta de este tipo.

    ResponderEliminar
  20. necesito ayuda en lo siguiente,quisiera saber como encontrar valores repetidos individuales,me explico con el siguiente ejemplo de numeros de 4 cifras:
    1894
    1656
    2352
    6980
    y mi pregunta es como pudiera detectar en una larga lista cuantas veces esta repetido el 0,el 1 y asi sucesivamente hasta llegar a 9

    ResponderEliminar
    Respuestas
    1. Buenas tardes
      Partiendo de la base que tienes en la columna C una relación de datos de 4 cifras (o de las cifras que sea), si pones en la celda D1 esta fórmula:
      =LARGO(C1)-LARGO(SUSTITUIR(C1;"0";""))
      ... y la arrastras hasta el final, tendrás en cada celda el número de veces que se repite el número 0 dentro de cada número. Luego, solo tienes que sumar toda la columna C.
      Si quieres hacer eso con el resto de números hasta el 9, repites lo mismo en otras columnas.
      Saludos.

      Eliminar
  21. Necesito ayuda para identificar valores repetidos en una columna pero que los ordene de acuerdo a valores de una segunda columna es decir:
    Columna A
    A1= ABCD
    A2= ABCD
    A3= ABCD
    A4= ABCD
    COLUMNA B
    B1= 1
    B2= 1
    B3= 0
    B4= 1
    COLUMNA C es donde estaria la formula para encontrar todos las veces que estan duplicados los valores de la columna A, por ejemplo uitilizando la funcion contar.si aplicado a la columna me regresaria los valores
    c1= 1
    c2= 2
    c3= 3
    c4= 4
    Pero lo que ocaparia es que comparara tambien contra la columna B es decir , si hay repetidos en la columna A que le asigne el primer valor "1" al numero menor de la columna B y asi sucecivamente quedando el resultado como:
    c1= 2
    c2= 3
    c3= 1
    c4= 4
    No se si se tenga que concatenar alguna otra funcion para lograr eso.

    ResponderEliminar
    Respuestas
    1. Las explicaciones son insuficientes y creo que algunas incorrectas. Por ejemplo, la función CONTAR.SI no devolvería esos valores, puesto que ABCD está repetido 4 veces... luego en la celda C1 no puede haber un resultado = 1 (por ejemplo)

      Eliminar