viernes, 9 de marzo de 2012

Resaltar Duplicados en Distintas Hojas

"Necesito resaltar texto duplicado entre varias hojas. Por ejemplo, tengo una lista de 80 nombres en la hoja 1 y en la hoja 2 otra lista de 80 nombres y quiero resaltar los nombres duplicados entre las dos hojas".

No problemo. Para solucionar este problema utilizaremos el Formato Condicional y la función CONTAR.SI. Partimos del siguiente ejemplo en donde nos encontramos una lista de 13 nombres en la hoja 1 y otra lista con 10 nombres en la hoja 2:



Los pasos que debemos seguir a continuación son los siguientes:

1. Seleccionamos en la hoja 1 el rango B3:B15 y dentro de la ficha Inicio vamos al módulo de Estilos y pulsamos el icono de Formato Condicional. De la lista que se abre seleccionamos nueva regla.


2. En la ventana que se abre seleccionamos Utilice una fórmula que determine las celdas para aplicar formato (la última opción de la lista):


3. Nos situamos en Dar formato a los valores donde esta fórmula sea verdadera y escribimos la siguiente fórmula:

=CONTAR.SI(Hoja2!$B$3:$B$12;B3)>0

4. Acabamos pulsando el Botón Formato. Ahora seleccionaremos el formato con el que queremos que resalte excel los nombres repetidos. En nuestro ejemplo le pediremos relleno violeta y color de fuente blanco y negrita. Aceptamos y problema resuelto:


49 comentarios:

  1. Hola Kiko !!

    Soy el que te hizo la consulta. Muchas gracias por aclararlo y por la velocidad de respuesta.

    Un saludo !!

    ResponderEliminar
  2. ¡Vaya por dios! estoy buscando como loco la opción para escribir un correo al autor de este blog, pero no la encuentro... ¿será que tengo el cerebro en "modo domingo"...? Puesto que, como digo, no lo localizo espero que no te moleste que te escriba una duda aquí mismo: necesito proteger un documento de excel que hice a mi jefe, que le sirve de contabilidad. Lo estructuré con subtotales, de tal modo que puede desplegar o contraer los detalles de las diferentes ramas de la empresa. Ahora quiere compartir el documento con otro miembro de la empresa y para ello debo proteger las fórmulas, o de lo contrario me las pisarán... pero en cuanto protejo el documento, también me queda protegida la opción de contraer o desplegar las ramas de los subtotales. ¿Hay algún modo para seguir permitiendo esta característica aún cuando la hoja está protegida? Muchas gracias de antemano. Carles

    ResponderEliminar
    Respuestas
    1. No me molesta en absoluto. Por un lado decirte que no se puede agrupar y proteger la hoja y que dicha agrupacion se pueda "manipular" posteriormente. Hay una solución muy sencilla (pero muy limitada)que puedes ver en mi post: http://lareboticadeexcel.blogspot.com.es/2010/03/proteger-celdas-con-validacion-de-datos.html
      Otra forma sería utilizando macros. He encontrado esta discusión y solución en un foro que espero te resulte útil:
      http://grupos.emagister.com/debate/como_proteger_el_contenido_pero_no_el_formato/1013-21591

      Eliminar
    2. KIKO, SOS UN GENIO, ME HAN SERVIDO MUCHO TUS APORTES, UN ABRAZO FORTÍSIMO

      Eliminar
  3. Muy buen comentario y excelente ayuda, ahora será posible este mismo ejercicio pero para hojas que esten en diferentes libros, mismo formato de celdas??? habrá que intentarlo no creen, bueno a trabajar vere si puedo lograralo..

    ResponderEliminar
  4. Saludos,

    Muchas gracias por tu post. Me es muy util para mi trabajo diario. Tengo una duda adicional, es posible resaltar con diferentes colores los diferentes valores repetidos? Me explico, en tu ejemplo quisiera que las celdas repetidas con "enrique" tengan un color diferente a las celdas repetidas con "Miguel". Visualmente se hace mucho mas facil agrupar los diferentes valores repetidos.

    Gracias de antemano, Luis

    ResponderEliminar
    Respuestas
    1. Hola Luis. He dejado un post http://lareboticadeexcel.blogspot.com.es/2012/08/resaltar-duplicados-por-colores.html solucionando lo que pides. Un saludo.

      Eliminar
    2. Muchas gracias por tu respuesta. Me ha sido muy util. Voy a trabajarl esta opcion mas a fondo para aprender a manejarla con mejor detalle.

      De nuevo Gracias.

      Luis

      Eliminar
  5. Hola, he visto tu post y se ve sencillo de hacerlo, yo lo intenté usar pero simplemente el excel muestra un mensaje en pantalla que no es posible utilizar formato condicional en otra hoja o libro diferente... en mi caso son mas de una columna pero lo intenté solo con una y no me funcionó.

    Saludos

    ResponderEliminar
    Respuestas
    1. Necesitaría ver el archivo porque, como puedes ver en este post, sí es posible establecer formatos condicionales en otras hojas.

      Eliminar
    2. Hola Kiko. Me sucede lo mismo que esta persona. Excel me dice que no es posible utilizar formato condicional en otra hoja o libro diferente.
      Solo me funciona en la misma hoja, en diferentes hojas no.
      Gracias y quedo atento.

      Eliminar
  6. A mi me muestra lo mismo que excel no puede hacer referencia a otros libros u hojas, tengo office 12 puede que la version 14 si lo realice

    ResponderEliminar
  7. Sabras como resaltar una celda con hipervinculo destino? Es decir de una celda cuando le doy click y me manda al destino, esta celda destino quiero que resalte de todas las demas, se puede hacer esto en excel? Muchas gracias y saludos!

    ResponderEliminar
  8. Me podrian ayudar con el siguiente caso: Tengo varios libros de excell a los cuales llamaré AB,HT Y RT , en el libro AB yo introdusco datos los cuales sin querer pueden estar repetidos en otros libros (HT Y RT). Lo que desearia es una formula que desde AB me señale si dicho numero esta repetido en otro libro al momento de yo escribirlo.

    ResponderEliminar
    Respuestas
    1. Te respondo en el próximo post. Un saludo

      Eliminar
  9. Una consulta, tengo una hoja en la que cada 4 columnas, tengo los números de recibos, estos número no deben repetirse, he intentado usando el Contar.Si, como ya lo han puesto de ejemplo aquí, pero no me resulta, a ver cómo me puedes ayudar.

    Te explico, el rango sería, A1:A2000, E1:E2000, I1:I2000, y necesito que nunca se repita un dato en esas columnas.

    Muchas gracias.

    ResponderEliminar
    Respuestas
    1. Te contesto en el próximo post. Un saludo

      Eliminar
  10. Kiko, Que tal? te quería preguntar si hay alguna manera de chequear en distintas hojas, pero con excel 2000, porque lo que vos sugerís es para el 7, supongo. Gracias!

    ResponderEliminar
    Respuestas
    1. La solución que propongo es igualmente aplicable a excel 2000. Sólo cambia el menú disponible en formato condicional pero en la versión 2000 permite establecer una fórmula también.

      Eliminar
  11. Hola Kiko

    Podrías decir cual era la solución al error de "No se puden usar referencias a otros libros u hojas de cálculo para criterios de Formato Condicional"? es que lo acabo de intentar y me dá el mismo error. Utilizo Excel 2007.

    Un saludo

    ResponderEliminar
  12. Mi estimado Kiko ante todo muy buenos tu blog estoy aprendiendo bastante.
    No puedo realizar el ejemplo de duplicados;me indica una error que se detalla.
    "NO SE PUEDEN USAR REFERENCIAS NA OTROS LIBROS U HOJAS DE CÀLCULO PARA CRITERIOS DE FORMATO CONDICIONAL".

    ResponderEliminar
    Respuestas
    1. Qué versión de excel estás usando?

      Eliminar
    2. Buen día Kiko, yo tengo el mismo inconveniente, uso Excel 2007 y me da el mensaje en mención.

      Eliminar
    3. A mi me pasó lo mismo. Opte por copiar los datos en la misma hoja y me funcionó. Luego los borré. Eran 13750 y tenía que resaltar 580. Gracias. Me solucionó mucho.

      Eliminar
  13. Buenas Kiko,

    Tengo dos tablas idénticas en dos hojas diferentes de un documento excel. En dicha tabla existe una columna que identifica el peso de cada elemento. Lo que necesito es que si el dato del peso de la hoja 2 cambia respecto a la de la hoja 1, automáticamente se cambie de color del fondo del dato de la hoja 2. Es decir, si el dato es mayor que el anterior el color del fondo será rojo, si es menor, verde; y si es igual se queda tal como está. ¿Lo podría hacer con formato condicional? ¿Cómo? Tengo la versión 2007 de excel.
    ¡Muchisimas gracias!

    ResponderEliminar
    Respuestas
    1. Efectivamente lo puedes hacer sin problema. Si, por ejemplo, tenemos el dato del peso en la hoja 2 en la celda B3, entonces abrimos la herramienta Formato condicional, seleccionamos la opción "Utilice una fórmula que determine las celdas para aplicar formato" (la última opción de la lista), al igual que en el ejemplo de este artículo, y escribimos la fórmula =B3>Hoja1!B3 y pulsamos el botón Formato y le ponemos relleno de color rojo. Tras Aceptar pulsamos Nueva regla y escribimos la fórmula =B3<Hoja1!B3 y en Formato le ponemos color de relleno verde y ya está. Para que afecte a toda la columna debes seleccionar previamente todos los datos de dicha columna o hacerlo para B3 y al terminar copiar formato y pegarlo en el resto de datos de dicha columna. Un saludo

      Eliminar
  14. Hola! aplique la formula pero con numeros, no texto y se me presenta un problema, en vez de dar formato condicional a los numeros que se me repiten entre ambas hojas, me aplica el formato condicional en el numero de la celda, es decir, si ingreso el numero 5 en la hoja2, en vez de aplicarle el formato al numero 5 de la hoja1 se lo aplica a la celda 5 del rango que seleccione, siendo que el nuero 5 pueda estar en la celda 20 ... no se si me explico ... me puedes ayudar por favor.
    Pablo.

    ResponderEliminar
    Respuestas
    1. Hola. Te sugiero la lectura del post:
      http://lareboticadeexcel.blogspot.com.es/2009/04/detectar-y-contar-duplicados.html
      Si no soluciona tu problema, por favor mándame tu archivo a kiko.rial@gmail.com
      Un saludo.

      Eliminar
  15. Hola Kiko, es posible que Excel me detecte celdas en 2 columnas distintas con texto que no es exactamente exacto como duplicados, es decir que pej. me tome aprox como aproximadamente. O dicho de otro modo me permita establecer palabras claves dentro de una celda.

    Te hago esta pregunta porque se me da el caso de que tengo que comparar 2 archivos de Excel en el los textos no son literalmente iguales pero sí se refieren a los mismos expedientes y haciéndolo "a mano" el trabajo se hace bastante tedioso debido al gran número de datos que debo comparar.

    Saludos y gracias

    ResponderEliminar
  16. Hola Lucía. Te recomiendo el siguiente post donde encontrarás la solución a tu pregunta. Un saludo:
    http://jldexcelsp.blogspot.com.es/2011/08/uso-de-comodines-wildcards-en-funciones.html

    ResponderEliminar
  17. Buen día.
    Este post me fue de gran ayuda, de antemano gracias. Sin embargo, trabajo la mayoría del tiempo con un Excel 2007 y al parecer este método no se puede aplicar en esa versión. Le agradecería alguna indicación al respecto.

    ResponderEliminar
    Respuestas
    1. Hola David. No deberías tener problema con la versión 2007. Prueba a darle un nombre al rango Hoja2!$B$3:$B$12. Por ejemplo llámale tabla2 y utiliza la siguiente fórmula:
      =CONTAR.SI(tabla2;B3)>0 en vez de la que propongo en este post: =CONTAR.SI(Hoja2!$B$3:$B$12;B3)>0
      Si no te funciona, por favor mándame tu archivo a kiko.rial@gmail.com

      Eliminar
  18. Hola Kiko, muchas gracias por tu blog.
    ¿Se podrían buscar duplicados en tres (o más) hojas distintas?

    Muchas gracias

    Mari Mar

    ResponderEliminar
    Respuestas
    1. Sin problema. Si quieres buscar si hay duplicados de una lista en la hoja 1, por ejemplo, en otras dos hojas más, simplemente debes ampliar la fórmula de este post. La fórmula que tendrás que introducir dentro del formato condicional será del tipo (siguiendo con nuestro ejemplo):
      =O(CONTAR.SI(Hoja2!$B$3:$B$12;B3)>0;CONTAR.SI(Hoja3!$B$3:$B$12;B3)>0)
      donde suponemos que en la hoja 3 en el rango B3:B12 está la tercera lista en cuestión.

      Eliminar
  19. Kiko, buenos dias, cordial saludo
    Tengo el siguiente caso, estoy haciendo un registro de varias personas de las cuales tienen que hacer un pago mensual, el pago lo realizan en un banco y este emite un recibo con un numero único y algunas personas cancelan varios meses en un solo pago, quiero dejar el condicional si se repite algún numero de recibo por si alguna otra persona esta haciendo fraude pero también quiero una excepción si la persona paga varios meses de una vez no se me muestre repetido, en pocas palabras que me avise si ya existe el numero de recibo en otra persona pero que no me ponga problema al ingresar el mismo recibo en varios meses en una sola fila. Gracias

    ResponderEliminar
    Respuestas
    1. En principio es sencillo dependiendo de cómo tengas estructurada la información. Por favor mándame un ejemplo a kiko.rial@gmail.com e intento ayudarte.

      Eliminar
  20. Hola... Bueno quisiera saber como puede comparar o encontrar el nombre de un proyecto que se encuentra en diferentes hojas...

    ResponderEliminar
  21. Necesito que me especifiques un poco más la pregunta con un ejemplo. Gracias.

    ResponderEliminar
  22. ¿Se puede hacer esto para un rango 3D? de preferencia sin formato condicional, solamente indicando si hay o no algún dato repetido, ejemplo para revisar folios, no lo necesito para eso pero es para tratar de explicar lo que busco

    ResponderEliminar
  23. Kiko buenas, como va?
    te queria preguntar
    tengo en la hoja1 nombres, y en la hoja2 tambien nombres pero son para dar de baja, lo que quiero hacer es que los nombres de la hoja2 que estan en la hoja1 desaparescan?
    es posible esto? y si son mas hojas?

    Saludos,

    ResponderEliminar
    Respuestas
    1. Suponiendo que el rango de nombres de la Hoja1 lo tengas en A1:A8 y que el rango en la hoja 2 sea A1:A6, por ejemplo, Entonces nos situamos en la celda B1 de la Hoja2 y escribimos la siguiente fórmula (que debemos copiar hasta B6):
      =SI(CONTAR.SI(Hoja1!$A$1:$A$8;A1)>0;"";A1)

      Eliminar
  24. Hola, me pareció muy interesante tu información y muy útil, pero que puedo hacer para saber cuantas veces se repiten los nombres de la hoja 1 por ejemplo en relación con 6 o 7 hojas mas.

    ResponderEliminar
  25. Por que me manda error en formula =CONTAR.SI(9000!$H5$;$H1144$;H5)>0, donde 900 es = a hoja 2, y la formula la pongo en 650 que es = a la hoja 1y tiene el rango H5;H144.

    ResponderEliminar
  26. No entiendo bien el planteamiento. El primer argumento es el rango H5:H144, que entiendo que está en la hoja 2, que le has llamado 9000. Y el segundo argumento es el criterio que lo tienes en H5 de la hoja1
    ¿Es correcto esto? Si es correcto dime que error te sale porque a mi me funciona bien.

    ResponderEliminar
  27. Por si alguien tiene Excel 2013 o superior:

    A partir de Excel 2013, ya existe una herramienta para comparar ficheros:

    http://www.sysadmit.com/2015/10/excel-comparar-ficheros.html

    ResponderEliminar
  28. Me párese muy útil tu información . tengo dos listados como de 8000 nombres cada uno quiero comparar para ver cuantos clientes son nuevos. seguí los pasos del ejemplo que pusiste y me aperase error de formula: de la hoja1 es del B3 - B8700 y de la hoja2 es del B3 - B9811, te agradezco tu ayuda

    ResponderEliminar
  29. Puede ser que en vez de ; utilices , como separador de argumentos en las funciones?

    ResponderEliminar
  30. buen<s tardes amigo soy nuevo y quisiera ver si me podrias apoyar.
    mira tengo una libro de excel conce contienen numeros de plazas de trabajo quiero verificar en la joja 3 y en la 1 si tengo algun repetido y si pongo otra plaza en la base de datos que se den de alta en mi tranajo queria ver si estaba duplicado el numero de plaza de nuevo ingreso

    ResponderEliminar