Comparar listas, valores de una lista que no están en otra lista

En este Blog aprenderemos a cómo marcar artículos de una tabla que no están en otra tabla.

Es muy normal que en una hoja de Excel tengamos una lista de artículos y, en una nueva lista, bien sea porque la hayamos importado, o copiado de otro libro, o dichos artículos los introdujese un compañero, quisiera saber qué artículos de esta lista son nuevos, es decir, no están en la otra lista.

Para ello deberemos comparar las dos listas, buscando los elementos de la Lista 2 que no están en la Lista 1, aplicando, finalmente, el formato condicional.

Sabemos que Excel tiene una opción automática de eliminar filas repetidas, pero no una opción automática de buscar los que no están en otra lista.

Herramienta de Excel Quitar Duplicados

Acuérdate que, para eliminar las filas repetidas, debemos acceder a la opción Datos > Quitar duplicados.

Como no hay una herramienta automática, recurriremos a las diferentes funciones y herramientas que nos suministra Excel, para lograr el objetivo.

Para ello utilizaremos las funciones EsError(), Coincidir() y la herramienta formato condicional.

En el anterior blog ya vimos la utilización de la función Coincidir(). Por esto, me centraré en comentar la función EsError() y la herramienta Formato condicional.

Función de Excel EsError()

Nos ayuda a comprobar si un valor introducido en la celda es un error, y nos devuelve el valor VERDADERO o FALSO.  El valor evaluado puede ser una celda o una fórmula cuyo resultado será evaluado.

Sintaxis de la función EsError()

Ejemplo función EsError()

En una hoja de cálculo hemos introducido dos fórmulas que nos generan dos tipos de errores:

• Dividir por cero, nos aparecerá #¡DIV/0!

En la celda D2, la fórmula =EsError(B2), nos dá Verdadero, porque es cierto que la fórmula contiene un error. Estamos intentando dividir 4/0 en la celda B2.

En la celda D3, la fórmula =EsError(B3), da como resultado Falso, ya que la expresión introducida en la celda B3 =4/2, da 2.  Por lo tanto, es Falso que tenga error.

Error de nombre de fórmula # ¿NOMBRE?

Otro tipo de error es que introduzcamos mal el nombre de la fórmula. En la celda D4, la fórmula =Es Error(B4), devolverá Verdadero, ya que es cierto que la función está mal escrita (=Sumar(4;7)).

En la celda B5, hemos corregido la fórmula:

En consecuencia, la función =Es Error(B5), devuelve Falso, porque la celda no contiene error.

Podemos deducir que, si la celda contiene cualquier tipo de error, la función EsError() devuelve Verdadero. Falso en caso contrario.

Si estás interesado en ampliar tus conocimientos sobre todo lo relacionado con Excel visita nuestro Curso de Excel

A comparar Listas

Lo que pretendemos es pintar, con un color de fondo, las celdas de la Lista 2, cuyo valor no esté presente en la Lista 1. Con lo visto anteriormente te será muy fácil.

Partiremos de la Lista1, que contiene una serie de libros, con su código correspondiente:

Y en una nueva lista (Lista2), tenemos dichos códigos de libros y algunos de nuevos que hemos añadido.

Queremos saber cuáles de estos libros son nuevos, es decir, los libros que no están en la otra lista, y que nos los marque:

Herramienta de Excel Formato condicional

Para ello recurriremos a la herramienta formato condicional.

Seleccionamos la Lista 2, y accedemos a Inicio > Formato condicional > Nueva regla

Nos aparecerá la siguiente ventana:

A través de esta ventana podemos asignar un color a un conjunto de celdas que cumplen una condición.

Para ello en la ventana Seleccionar un tipo de regla, accedemos a Utilice una fórmula que determine las celdas para aplicar formato. Nos permitirá dar formato a los valores donde esta fórmula sea verdadera.

Acuérdate que la función EsError() nos daba Verdadero cuando el argumento de la función daba error.

Introducimos la fórmula:

=ESERROR(COINCIDIR(F4;$B$4:$B$16;0))

Clicando en el botón Formato, seleccionamos el color que queremos aplicar a las celdas que cumplen la condición.

Si estás interesado en ampliar tus conocimientos sobre todo lo relacionado con Excel visita nuestro Curso de Excel

Remarco los pasos importantes para entender el ejercicio:

+ La primera función a estudiar es Coincidir()

= COINCIDIR(F4;$B$4:$B$16;0)

• F4, la primera celda de la Lista 2, donde hay el primer código de libro a buscar.
• $B$4:$B$16 es el rango donde vamos a buscar, para saber si encuentra dicho código. En nuestro caso los códigos de libros de la Lista 1.
• 0 es la coincidencia, en nuestro caso la lista está ordenada, pero, por si en algún momento cambiamos el código de algún libro y, ya no sigue el orden, el algoritmo funcione igualmente.

+ Si el código de libro no está en la Lista 2, nos va a dar error. En este caso quiero que me pinte (marque) dicha celda. Por esto, añadimos en la formula la función EsError. Si nos da error la función, es verdadero y le digo píntame la celda.

+ Nos dará error cuando no encuentre el código de libro en la Lista 2

+ Podemos observar que ha marcado correctamente los códigos de los libros que no están en la Lista 1. En nuestro caso los códigos de libros correspondientes son el 5, 8, 10, 36 y 48.

Resultado final de la comparación de los diferentes códigos de artículos de las tablas:

Visualizar sólo los que no están en la otra lista usando los filtros por color de Excel

Para ver los que no están en otra lista, podemos filtrarla por color y visualizar sólo los que tienen un color determinado. Interesante si la lista de artículos es muy extensa. Los pasos para conseguirlo son:

Datos > filtro

Seleccionamos Filtrar por Color > Filtrar por color de celda. Escogemos el color anaranjado de color de relleno, como criterio de filtro.

Apareciendo, sólo los registros que están pintados con este color. Podemos ver que está filtrado por la columna Código de libro, por el icono del embudo.

De esta manera hemos visto como marcar artículos de una lista que no están en la otra lista, y hemos aprovechado el artículo para repasar funciones que se utilizan mucho en Excel, como Coincidir, EsError y formato condicional.

Seguro que es una técnica que vas a utilizar en infinidad de ocasiones en tu trabajo y te será de gran utilidad.

Descargar archivo de ejemplo: Marcar_los_que_no_están.xlsx

Si estás interesado en ampliar tus conocimientos sobre todo lo relacionado con Excel visita nuestro Curso de Excel