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
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
PERFECTO
ResponderEliminarME HAS AHORRADO HORAS DE TRABAJO
Me alegra saberlo.
ResponderEliminarSaludos
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.
ResponderEliminarPor 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.
Hola, Kiko
ResponderEliminarMuchas gracias por esta entrada del blog, ¡revisar 15000 entradas una por una se me antojaba misión imposible...!
Muchas gracias Kiko, también me ahorraste trabajo, desde ayer estaba buscando algún filtro o fórmula para detectar repeticiones. Muchas gracias.
ResponderEliminarGracias Kikolin
ResponderEliminarhola 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.....
ResponderEliminarSantiago
Y si quisiera saber por ejemplo, cuantas veces se repite ese codigo del cliente en las 3 zonas, no por cada zona?
ResponderEliminarLo más sencillo es sumar tres CONTAR.SI
ResponderEliminar=CONTAR.SI(B$3:B$18;B3)+CONTAR.SI(D$3:D$18;B3)+CONTAR.SI(F$3:F$18;B3)
eres fenomenal profe!
ResponderEliminarEn 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.
ResponderEliminarGrande KIKO, muchas gracias la verdad es que me ha servido mucho los tips que pones.
ResponderEliminarEres consulta obligada !
Gracias
Muchas gracias. Me alegra que os haya resultado útil. Un saludiño
ResponderEliminarhola estuve mirando la formula y la verdad no se si es la que necesito...
ResponderEliminaryo 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
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
ResponderEliminarHola,
ResponderEliminary si las celdas contienen texto ???
Gracias.
La fórmula expuesta funciona con texto igualmente.
Eliminarhaaa, genial con su explicacion, gracias por su tiempo y por la ayuda.
ResponderEliminarhola, y si quisiera comprara datos repetidos en distintas columnas por ejemplo comparara lo de la columna C con la columna E
ResponderEliminarSerí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.
EliminarMuy bien, y rizando el rizo: si quiero contar valores únicos, pero que además cumplan otras condiciones?
ResponderEliminarMuchas gracias por tu aportación. Mejoras la vida laboral de muchos. Dios te bendiga.
ResponderEliminarAtte:
Pablo.
Hola.
ResponderEliminar¿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
Hola Alberto. Para intentar ayudarte necesito un ejemplo concreto de lo que quieres solucionar. Gracias
ResponderEliminarSaludos 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.
ResponderEliminarEn 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
Creo que lo que buscas lo tienes en este post: http://lareboticadeexcel.blogspot.com.es/2009/04/resumen-de-ventas-con-sumarsi.html
EliminarUn saludo
:D
EliminarKiko buenos dias... Puede crear un tutorial similar con office 2010? Gracias!!!
ResponderEliminarBuenos días. La pregunta es ¿cómo se resuelve este mismo problema pero en excel 2010?
EliminarHOLA ANTES QUE NADA SALUDOS...
ResponderEliminarSOY 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.....
Hola. Tienes un post con la respuesta a tu pregunta en este mismo blog:
Eliminarhttp://lareboticadeexcel.blogspot.com.es/2012/03/resaltar-duplicados-en-distintas-hojas.html
Hola que tal.
ResponderEliminarMe 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.
Hola Alejandro. Te contesto en un nuevo post que publicaré este fin de semana. Muchas gracias y un saludo
EliminarQUE 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..
ResponderEliminarHola Pedro. Por favor indícame a qué dirección de mail te mando la solución. Gracias
EliminarHola esta muy bueno tu ejercicio ya que me ayudo mucho, pero se puede encontrar valores duplicados y sumar los mismos.
ResponderEliminarPor 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.
Suponiendo que el primer dato (100) lo tienes en A2, puedes utilizar la siguiente fórmula para conseguir lo que necesitas:
Eliminar=SI(CONTAR.SI($A$2:$A$6;B2)=0;0;(CONTAR.SI($A$2:$A$6;B2)+1)*B2)
Buenas como va? me llamo Fede, tengo un pequeño problema.
ResponderEliminarYo 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!
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?
EliminarA 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.
EliminarSaludos 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 (en mi ejemplo sería A1:A10) y en Formato Condicional establecer la siguiente fórmula:
Eliminar=CONTAR.SI($A$1:$A$10;A1)>1 y seleccionar un formato de relleno.
Estimado Kiko:
ResponderEliminarYo 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
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:
Eliminar=CONTAR.SI($A$1:$A$10;A1)>1 y seleccionar un formato de relleno. Un saludo
Hola Kiko:
ResponderEliminarqueria 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
Te he mandado la solución a tu correo. Ya me dirás si es lo que necesitabas. Un saludo
EliminarHola,
ResponderEliminarTengo 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
Buenos días. Puedes ver la solución en: http://lareboticadeexcel.blogspot.com.es/2012/07/contar-registros-unicos.html
ResponderEliminarUn saludo
Que tal Kiko, muy buena la explicación. Yo necesito algo parecido, trato de explicarlo:
ResponderEliminarEn 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
Te contesto en el post de hoy. Un saludo
EliminarHola 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:
ResponderEliminarzona 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,
Mándame un mail y te envío la solución
EliminarHOLA 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.
ResponderEliminarAGRADEZCO MUCHO TU APOYO Y SALUDOS CORDIALES.
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
EliminarUn saludo
Hola Kiko,
ResponderEliminartengo 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
Hola Alvaro. Por favor mándame un ejemplo a kiko.rial@gmail.com con el detalle de lo que quieres conseguir. Gracias
ResponderEliminarkiko, 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
ResponderEliminarde antemano, mil gracias.
Hola. Por favor mándame una hoja con un ejemplo y lo que quieres conseguir exactamente a kiko.rial@gmail.com
ResponderEliminarHOLA KIKO TENGO UNA HOJA DE CODIGOS Y SE REPITEN COMO EXCEL ME LOGRA BUSCAR LOS CODIGOS REPETIDOS CUANDO LOS VALLA DIGITANDO GRACIAS.
ResponderEliminarSi no entiendo mal lo que buscas lo encontrarás en este artículo: Evitar datos repetidos con Validación Datos Personalizado http://lareboticadeexcel.blogspot.com.es/2009/04/evitar-datos-repetidos-con-validacion.html
ResponderEliminarQuiero hacer una consulta... al consolidad 2 hojas de excel en una ahora tengo valores duplicados en una columna A que corresponden a códigos de producto, y en la columna B tengo el stock de cada código. Lo que quiero es que excel detecte los valores duplicados en la columna (A) y de esas selecciones sume las cantidades de la columna B de modo a tener 1 solo código de producto en la A y 1 sola cantidad (sumada) en la columna B. Se entiende la consulta? Cómo puedo hacer esto?
ResponderEliminarSi no entiendo mal la pregunta, puedes solucionarlo utilizando tablas dinámicas o con fórmulas utilizando la función SUMAR.SI
ResponderEliminarPuedes consultar el post:
http://lareboticadeexcel.blogspot.com.es/2009/04/resumen-de-ventas-con-sumarsi.html
Me ayudo un monton FORMATO CONDICIONAL, tengo que hacer algo que no se se bien como explicarlo, pero la ayuda fue tantastica!!!!
ResponderEliminarbuenas noches podría ayudarme , no se como podría borrar hojas de excel que estan repetidas,le exlico , pasa que tengo hojas repetidas 4 veces como por ejemplo cocinas 5 veces repetida la hoja de salón principal , entre otras ,mi pregunta es ?
ResponderEliminarpor medio de excel hay alguna formula en la que yo pueda eliminar las hojas repetidas ya que el contenido de las mismas son iguales y si no hay como o en el caso de no haber como con una macros que me facilite el trabajo.
hola muy buena información, quisiera saber de que manera podría aplicar formato condicional digamos solo por única vez a valores que se repiten, es decir :
ResponderEliminar1. De A1:A13: tenemos los valores 1,2,1, 4,1,,2,3,4,5,6,6,6 y de B1:B13 los valores 2,3,4,5,6,7,6,8,1,1,1,1,9, lo que se desea es que por cada valor de A1:A13 solo se marque una sola vez en B1:B13 y no se marque dos veces o tres veces, que al resaltar o buscar ya no se vuelva a considerar(que se considere una sola vez) al igual que viceversa , es decir que si en A1:A3 tenemos 1,1,1 y en B1:B3 tenemos 1,1,2 se use el formato y solo se quede pintado los dos primeros que se repiten y en el tercer uno no , ya aplique con contar.si y coincidir , y nada , quiero aplicarlo en dos BD para hacer cruces de información muy extensa son casi 6000 registros y hacerlo de forma manual llevaría mucho tiempo. agradecería su pronta respuesta. o que solución hay????
Hola Gary. Necesito aclararme.
EliminarColumna A valores: 1;2;2;1;3;6;7;8;1;1;1
Columna B valores: 9;1;1;2;5;5;5;
Qué valores de la columna A quieres que se aplique formato? Sólo el primer 1 porque está repetido en la columna B? Lo dos primeros 1 porque en la columna B hay dos 1 pero no los otros tres 1 que aparecen al final de la columna A?
Si puedes mandarme un ejemplo de los datos que tienes y lo que quieres obtener a kiko.rial@gmail.com te ayudo. Un saludo
Hola, tengo un caso del cual les planteo mi requerimiento:
ResponderEliminarCliente Nombre cliente Folio Factura Monto Factura Serie
10504 AGRO INSU 1603 $113.00 APS
10504 AGRO INSU 157 $182.00 CDE
12199 SEMILLAS 182 $106.00 NIT
12306 MAPF 45 $16.00 NIT
12306 MAPF 82 $284.00 PAT
125 AGRO GON 158 $120.00 CDE
125 AGRO GON 153 $181.00 CDE
125 AGRO GON 1609 $60.00 CDG
1331 PULIDO CONT 16121 $234.00 CDG
En una tabla de muchos datos pero tomando como ejemplo lo anterior, necesito poner algún valor que me sirva de distintivo cuando un número de cliente exista en dos o más series distintas y si sólo pertenece a una serie, ponga otro valor. por ejemplo, que al cliente 10504 me ponga un 1 al pertenecer a más de 1 serie pero al cliente 12199 me ponga 0 al pertenecer solo a una serie.
Suponiendo que el rótulo Cliente esté en la celda A1 y el rótulo Serie esté en H1, me sitúo, por ejemplo en la celda K2 y escribo: =A2&H2 y copio hacia abajo. Con esto hemos concatenado las dos columnas (Cliente y Serie). Después me pongo, por ejemplo, en J2 y escribo la siguiente fórmula:
ResponderEliminar=SI(CONTAR.SI($A$2:$A$10;A2)=CONTAR.SI($K$2:$K$10;K2);0;1)
Aquellos clientes que tengan asociada más de una serie aparecerá un 1 y los que tengan una única serie asociada aparecerá un 0.
Hola Kiko. Como puedo hacer para encontrar valores duplicados pero que esten en diferente orden? por ejemplo: 123, 321.
ResponderEliminarTe contesto en mi post de hoy. Un saludo
EliminarHola Kiko
ResponderEliminarGracias por crear este blog es fantastico que nos apoyes a solucionar nuestros problemas de excel
Te consulto porque no se como resolver un problema y que es el siguiente:
Tengo una lista de empleados con sus datos (numero de empleado, puesto, dias de descanso, etc) y lo que quiero es que de una liste de asistencia que tengo poder filtrar en otra hoja los empleados que generaron tiempo extra en un dia en especifico o en un rango de fechas, me podrias ayudar con esto.
De antemano gracias amigo KIKO por tu atencion
Por cómo lo planteas parece el típico caso a solucionar con tablas dinámicas. Si me mandas un ejemplo a kiko.rial@gmail.com intento ayudarte
EliminarHola Kiko, deseo saber como desde una macro, teniendo elementos duplicados, sumar los elementos duplicados y posteriormente eliminar los elementos duplicados, teniendo solo un registro con la suma total, por ejemplo:
ResponderEliminarA B
Limon 5
Limon 4
Sandia 3
Fresa 5
Limon 5
Limon 7
No es necesaria una macro. Puedes resolverlo como comento en el siguiente post: http://lareboticadeexcel.blogspot.com.es/2009/04/resumen-de-ventas-con-sumarsi.html
EliminarTambién puedes generando una tabla dinámica.
Hola Kiko tengo una lista de aproximadamente 300 frases, algunas son iguales pero se diferencian en una o dos palabras.
ResponderEliminarQuiero que excel las identifique hay algún modo?
Ojala me puedas ayudar, muchas gracias.
Hola Kiko, muy buen blog!!!
ResponderEliminarConsulta, tengo una hoja de excel con datos numéricos : por ejemplo la fila 9 tiene los numeros : 3, 8 , 8 , 8 , 7 , 65 , 23 , 8 , 23 , 11 , 11 , 8 , 7, lo que necesito es que excel me resalte solo los datos repetidos consecutivos, como en este caso serian los 8 de las columnas 2 , 3 y 4, y los numero 11, que estan ubicadas en las columnas 10 y 11, el resto aunque se repiten algunos de ellos no son repeticiones consecutivas.
Gracias
Hola Wilson. Suponiendo que tienes los datos en el rango B5:N5, tienes que seleccionar dicho rango y dentro de la herramienta de Formato Condicional debes escribir la fórmula:
Eliminar=O(C5=B5;C5=D5)
Gracias Kiko por la respuesta , pero tengo ahora un nuevo desafío,
ResponderEliminarSuponiendo que los datos estén en la fila de rango B5 a Q5; y esta contiene lo siguiente por celda:
23 0 0 23 12 12 12 0 12 12 12 0 12 8 23 0.....el desafío es pintar las celdas que se repitan y luego contarlas. La condición para considerarlas repeticiones es que se ignore el CERO , es decir 1l primer 23 se repite con el cuarto y deberían pintarse por que hay ceros entremedio, en estricto rigor en este ejemplo las únicas celdas no pintadas deberían ser las que contienen cero y el 8 y 23 del final; por lo que el contador debería ser 9, puedes ayudarme?