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):
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!!
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:
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!
ResponderEliminarDespué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.
EliminarQuizá te falte o sebre algún signo $ , no se. Revísalo.
Gracias por leernos y un saludo.
GRACIAS!
ResponderEliminarComo 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
ResponderEliminar1. 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.
Eliminar2. Seleccionamos Permitir:Personalizada y en "Fórmula" escribimos:
=CONTAR.SI($A$1:$A$10;A1)<2
3. Aceptamos y salimos del cuadro.
Un saludo
Muchas gracias!!, me sirvió de mucho.
EliminarNo 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
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?
ResponderEliminarGracias y saludos.
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
ResponderEliminarEn 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).
EliminarDespué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.
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
ResponderEliminarTodas las columnas deberán tener el mismo tamaño?
ResponderEliminarGracias
No, no tiene nada que ver el tamaño de la columna.
EliminarSaludos
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,
ResponderEliminarsaludos y muchas gracias
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.
Eliminar2) 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.
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.
ResponderEliminarPor 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
Cambia el >1 del ejercicio por =3
EliminarEste comentario ha sido eliminado por el autor.
ResponderEliminarNOOO FUNCIONA NADA DE ESO YA LO INTENTE KM ESTA EN LA FORMULA Y SE QUE HAY DATOS DUPLICADOS A PROPOSITO Y NO SALE NADA
ResponderEliminarHola, 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.
ResponderEliminarGracias
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
ResponderEliminarUna consulta, cual sería la formula para que no se puedan ingresar a las celdas signos prohibidos como ¿,?, ", +.*
ResponderEliminarSolo respondemos dudas relacionadas directamente con el artículo, siempre y cuando la pregunta está formulada de forma inequívoca.
ResponderEliminarOfrecemos formación y consultoría para otros servicios que necesiten, previo presupuesto.
Saludos y gracias por leernos.
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?
ResponderEliminarHe 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.
EliminarQuizá algo se te escapa y no lo haces bien. Repasa todo de nuevo administrando la regla de formato condicional.
Saludos.
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.
ResponderEliminarEn cualquier caso, creo que lo que pides necesita la realización de un procedimiento en Visual Basic for Applications.
Buen dia. Muy bueno el articulo.
ResponderEliminarLo 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
Muy buen articulo
ResponderEliminarLo hice, pero lo que necesito es que cada tipo de coincidencia se colore de diferentes colores. Me podrias ayudar con eso por favor.
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.
ResponderEliminarMuy bueno! Gracias!
ResponderEliminarnecesito ayuda en lo siguiente,quisiera saber como encontrar valores repetidos individuales,me explico con el siguiente ejemplo de numeros de 4 cifras:
ResponderEliminar1894
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
Buenas tardes
EliminarPartiendo 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.
Necesito ayuda para identificar valores repetidos en una columna pero que los ordene de acuerdo a valores de una segunda columna es decir:
ResponderEliminarColumna 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.
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