Cómo comparar dos listas en Excel

lunes, enero 23, 2006

Los ejemplos en esta nota fueron desarrollados usando la versión inglesa de Excel. Por favor, lean esta nota.

Otra tarea bastante común en Excel es comparar entre dos listas. El objetivo es encontrar qué diferencias hay entre dos listas (que personas de una lista no aparecen en la otra, por ejemplo). Este tipo de comparación se puede hacer de varias maneras. En esta nota mostraré cómo hacerlo usando Conditional Formatting (Data ---> Conditional Formatting).
Supongamos esta hoja



Para señalar los faltantes en cada lista, daremos un fondo de color azul a los nombre de la lista 1 que no aparecen en la lista 2 y un fondo de color rosa a los nombre de la lista 2 que no aparecen en la lista 1.

Procedemos de la siguiente manera:

  1. seleccionamos el rango de los nombres en la lista 1 (A2:A10)
  2. en la barra del menú pulsamos Format ---> Conditional Formating
  3. seleccionamos Formula Is
  4. en la ventanilla escribimos la formula =COUNTIF($B$2:$B$10,A2)=0 (prestar atención al signo $ en la fórmula)
  5. apretar el botón Format, seleccionar Pattern y elegir el color azul.


Ahora hacemos lo mismo con la segunda lista (el rango será B2:B10) y utilizamos la fórmula

=COUNTIF($A$2:$A$10,B2)=0

Apretamos OK


Ahora podemos ver claramente quien falta en cada lista.

Otra técnica es utilizar la fórmula =MATCH, lo que mostraré en alguna futura nota.


Si te gustó esta nota anotala en del.icio.us


Tags: , ,



Categorías: Manejo de Datos_

30 comments:

Anónimo,  07 febrero, 2006 13:21  

La formula no funciona. Da "error en la formula". He reproducido el ejemplo y tampoco funciona.
Un saludo.

Jorge L. Dunkelman 07 febrero, 2006 18:13  

Hola, la fórmula funciona bien. Por favor, fijate que si usas Excel version castellano, tenés que usar los fórmulas en ese idioma (en lugar de COUNTIF debés usar CONTAR.SI). También controlá que los signos $ estén correctamente ubicados en los rangos.
La nota la escribí antes de tener el Office en castellano, así que usaba fórmulas en inglés para los ejemplos.
Si seguís teniendo problemas podés mandarme el archivo con la fórmula para ver dónde falla.

Anónimo,  18 junio, 2006 21:53  

hola

uso la version inglesa del excel pero la formula no me funciona!


gracias

Jorge L. Dunkelman 18 junio, 2006 22:23  

Hola, el problema puede estar en el uso de ";" (punto y coma) en lugar de "," para separar los argumentos de la función.
También podés mandarme el archivo para que pueda revisarlo.

Madrynense 14 octubre, 2006 04:20  

Mil gracias! un exelente trabajo, gracias por compartilo

La formula en excel en castellano quedaria de la siguiente forma

=CONTAR.SI($B$2:$B$13;A2)=0

Notar que se usa el ";" en lugar de ","

Jorge L. Dunkelman 14 octubre, 2006 08:15  

Gracias a vos. El uso de "," o ";" como separador de argumentos en las funciones depende de las definiciones regionales en el panel de control. Como mayormente trabajo en inglés mis definiciones usan ",".

Cesar 02 mayo, 2007 17:54  

Hola Jorge

Te felicito por tu excelente trabajo, gracias a ello das a conocer más allá de la frontera del conocimiento que la mayoría de la gente tiene para con Excel, las diversas aplicaciones del mismo.

Te pido de favor si me haces llegar el ejemplo en excel de controloes activex especificamete el del calendario aplicado a ventas.
El enlace existe pero me marca error.

Gracias


Atentamente.
Ing. Cesar Sánchez Martínez
cesar.sanchez.mtz@gmail.com

Jorge L. Dunkelman 02 mayo, 2007 21:11  

Hola César
gracias por los conceptos. He corregido el enlace. Puedes entrar en la nota y descargar el archivo.

Anónimo,  29 octubre, 2007 19:00  

Hola!

Excelentes articulos !

Quisiera saber si proximamente podria explicarnos como generear una lista con valores unicos pero utilizando macros!!

Muchas Gracias!

Atte. Gabriela

Jorge L. Dunkelman 29 octubre, 2007 20:25  

Hola Gabriela

lo anoto en la lista de temas a tratar.

Gracias por los elogios

Jaime 15 enero, 2008 21:18  

Muchas gracias por la ayuda; es MUY interesante.

Anónimo,  24 enero, 2008 06:21  

Lo he intentado hacer con listas numericas pero me surje el problema de las duplicidades, necesito que me encuentre el mismo número de coincidencias en la lista 1 y en la dos, no sé si me explico.

Jorge L. Dunkelman 29 enero, 2008 23:13  

No, no logro entender la consulta. Puede ser un poco más explícito?

Anónimo,  02 febrero, 2008 16:23  

hola jorge, ,e gustaría por favor que me aclararas una duda...
trabajo con excel 2007, y en reiteradas ocasiones cuando utilizo "formato condicional" al dar formato a la fuente, solo me permite modificar las características de ésta, y no la fuente en sí...

ahora bien, existe alguna forma de posibilitar la modificación de la fuente?, o la fuente esta predeterminada por excel?, y si es así, existe alguna manera de modificar la fuente predeterminada?

en mi caso la fuente predeterminada para el formato condicional es "cambria"...
sino logro modificar dicha situación tendré que dejar de usar la herramienta porque la presentación de mis informes no me parece del todo pulcra...

gracias de antemano,

Jorge L. Dunkelman 02 febrero, 2008 18:47  

Hola
por ahora no me ocupo de temas relacionados con XL2007. Te sugiero que averigües en los distintos foros que hay en la WEB.
De todas maneras, no me parece que el problema esté relacionado con formato condicional.

Oscar 01 junio, 2008 09:05  

Hola,
Debo comparar la información de dos ficheros que tienen un origen distinto. Contienen, fecha, código de producto y unidades.
La comprobación que hay que hacer consiste primero en, ordenar los dos listados. Luego, comparar que los códigos de productos que están en la lista A estén en la lista B, de ser así, debo restar el número de productos de A menos B.
Esta operación hay que hacerla a la inversa, es decir, comparar los que los códigos de productos de la lista B estén también en la A y hacer la operación.
Visualmente ayuda mucho alinear en cada fila un producto es decir, ir insertando o eliminando celdas para que el contenido de una lista quede alineado en las filas que está el otro listado. Al final, tendré que los procuctos Z de las dos listas están en la fila 102, por ejemplo.
Me gustaría que me ayudaras a automatizar, mediante alguna fórmula esta manualidad. Es bastante engorroso ir insertando celdas para que los dos listados queden alineados sobre todo si el catálogo supera los 2000 códigos!

Muchas gracias

Jorge L. Dunkelman 02 junio, 2008 21:43  

Oscar
para este tipo de ayudas tienes que contactarte a través del mail. Lo mejor sería que me envíes un archivo con el ejemplo de los que quieres hacer.

Anónimo,  14 junio, 2008 20:13  

Muchas gracias por la nota, es muy útil.

¿Y hay alguna forma de filtrar los resultados según su color (formato)?
En el ejemplo saltan a la vista, pero si fuera una lista con miles de registros, ¿habría alguna forma de filtrar sólo azules/rosas de cada lista?
Gracias!

Jorge L. Dunkelman 14 junio, 2008 22:01  

Si, habría que crear una columna auxiliar donde "traducir" los colores a su número de serie, para luego filtrarlos con Autofiltro.
Cómo obtener el número de color está explicado en mi nota sobre operaciones con colores en Excel

Anónimo,  09 julio, 2008 04:16  

Muy buena esta pagina, es muy util,
Podrias decirme como comparar dos tablas, pero cuando los datos estan en varias columnas.
ejemplo: orden de compra; linea; articula.
estaria comparando filas y no solo una celda. ya q en otra fila se pueden repetir datos pero no todos juntos
muchas gracias;

Jorge L. Dunkelman 09 julio, 2008 08:05  

Concatenando los datos de las celda de la fila en una columna auxiliar y comparando luego las columnas auxiliares.
Supongamos que los valores están en las columnas A, B y C. En la columna D creas la columna auxiliar con la fórmula:

=CONCATENAR(A1,B1,C1)

o con esta

=A1&B1&C1

hlarque@e3display.com,  22 septiembre, 2010 19:53  

Hola Jorge que tal,

Esta excelente tu blog, aunque no encuentro algo que pueda ayudarme en mi problema:

Tengo una hoja con varias columnas y filas que tienen diferentes datos, esta lista es la lista de proyectos de ventas y se actualiza de una manera variable; de esa hoja extraigo los datos de los proyectos que ya son seguros de realizar.
Necesito saber como hacer para automatizar o agilizar la hoja que yo hago con los proyectos más probables y la de ventas que es la que varia.

Espero que me haya dado a entender. Un saludo.

Hugo Larqué

Jorge L. Dunkelman 23 septiembre, 2010 08:11  

Hugo,
tu consulta es muy general. Existen muchas formas de hacerlo. Te sugiero que te pongas en contacto conmigo por mail (fijate ne la condiciones que aparecen en Ayuda)

Anónimo,  24 febrero, 2012 20:11  

No entiendo que pasa, no me funciona, coloco =COUNTIF($G$2:$G$281,C2)=0, y no funciona!. sale que hay error en la formula. ayudaaaaa

Jorge L. Dunkelman 24 febrero, 2012 23:18  

Fijate si alguna de las celdas del rango contiene un resultado de error.

Anónimo,  09 noviembre, 2012 00:40  

Muy bueno y bien explicado, Gracias

Anónimo,  06 diciembre, 2012 16:14  

Que pasa si la dos lista son diferente, me explico: Columna A (10,5,3,1) Columna B (7,3,1). como hago para compara la los Columna y en la Columna C tenero los repetidos o lo no Repetido, o resaltar los repetido con un color...etc.

Jorge L. Dunkelman 07 diciembre, 2012 07:12  

De la misma manera; el tamaño relativo de las listas no tiene importancia.

Blog Personal 20 febrero, 2013 16:53  

Jorge
junto con saludarete y felicitarte por el blog.... quisiera tu ayuda.

como puedo hacer para que un xls al abrir revise si existe una nueva version del mismo en una ruta (\\versiones\) y de ser asi se actualice automaticamente por el ultimo.

gracias por tu tiempo......

Jorge L. Dunkelman 20 febrero, 2013 22:44  

Bien, sencillo no es. Hay que programar un evento (Workbook_Open) que dispare una rutina que haga la tarea.

Publicar un comentario

Seguidores

Google+ Followers

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP