Obtener valores únicos en Excel

Vamos a explicar hoy, como podemos generar una lista con valores únicos en Excel, a través del uso de macros. Esta operación la podemos realizar de una forma muy sencilla con Excel 2007 o superior, tal y como nos explica Microsoft en este artículo, utilizando filtros avanzados. Como a nosotros nos gusta complicarnos la vida un poco, queremos que esta operación se realice sin intervención humana ;-) y de forma inmediata, en función de cuando vamos introduciendo valores en una hoja, con independencia de que a medida que los introduzcamos, estén repetidos una o mil veces, así que programaremos un sencillo macro que nos servirá para enseñárselo a nuestros compañeros de trabajo, o a nuestro jefe, y quedar como unos usuarios avanzados en Excel.

En definitiva, lo que vamos a hacer es obtener en una hoja del mismo libro, un listado de valores únicos (no repetidos), a partir de una serie de datos con valores que pueden estar o no repetidos (lo normal es que estén repetidos, para ver en funcionamiento esta utilidad) . Eso es lo mismo que decir, que vamos a eliminar todos aquellos valores duplicados que haya, para dejar un solo valor de cada dato introducido.

Para ilustrarlo de un modo gráfico, lo que pretendemos obtener es esto:


Como veis en la columna de la izquierda (tabla A), tenemos repetidos una serie de valores, en la columna de la derecha (tabla B), solo tenemos los valores únicos. Es decir, lo que haremos será eliminar los duplicados, o filtrar la tabla para que solo aparezcan los valores una sola vez.

Todo esto lo vamos a hacer introduciendo valores en la Hoja1, y en la Hoja2 es donde mostraremos los valores únicos o sin duplicados, de forma instantánea, a medida que vayamos introduciendo valores en la primera de las hojas.

Pensaréis que esto es algo complicado de obtener mediante macros, pero no, no lo es. De hecho es bastante sencillo. A pesar de que el código siempre lo comento -y esta vez no va a ser menos-, explicaré el proceso por el cual, a partir de una tabla donde vayamos introduciendo valores duplicados, obtendremos los valores únicos.

La técnica es muy sencilla. En el mismo instante en el que introduzcamos un valor en la Hoja1, nos desplazaremos a la Hoja2, a ver si ese valor está en la columna donde nos interesa que salgan los valores únicos, y si es así, volveremos a la Hoja1 sin hacer nada. Si por el contrario, el valor no está en esa columna de la Hoja2, lo que haremos será añadirlo, y volveremos a la Hoja1 para continuar introduciendo valores. Todo esto lo haremos sin darnos cuenta, pues visualmente no observaremos ningún refresco de pantalla por el cambio de la Hoja1 a la Hoja2, y de nuevo a Hoja1.

Para ello utilizaremos los siguientes códigos. Este primero, lo colocaremos en la Hoja1:

Private Sub Worksheet_Change(ByVal Target As Range) 'Al introducir un valor en la columna B, 'llamaremos al macro "valores_unicos_instantaneos" If Not Application.Intersect(Target, Range("B:B")) Is Nothing Then valores_unicos_instantaneos End Sub

Al modificar un dato de la columna B, se ejecutará el macro llamado “valores_unicos_instantaneos”. Ahora, en un módulo escribiremos este código:

Sub valores_unicos_instantaneos() 'Ocultamos el procedimiento Application.ScreenUpdating = False 'si hay errores, que continúe On Error Resume Next 'pasamos el dato que acabamos de introducir, a una variable dato = ActiveCell 'buscamos ese dato en la Hoja2, 'y más concretamente en la columna B With Hoja2.Range("B:B") 'buscamos el dato en esta Hoja2 Set existe = .Find(dato, LookAt:=xlWhole) 'si no existe, lo añadimos, pero antes nos 'situaremos en la primera celda donde queremos 'empezar a escribir los valores únicos If existe Is Nothing Then 'nos situamos en la primera celda donde queremos 'empezar a escribir los valores únicos Hoja2.Select Range("B5").Select 'bajamos hasta encontrar una fila vacía Do While Not IsEmpty(ActiveCell) ActiveCell.Offset(1, 0).Select Loop 'y escribimos el dato ActiveCell = dato End If End With 'subimos al principio de la tabla Range("B5").Select 'volvemos a la Hoja1 Hoja1.Select 'mostramos el procedimiento Application.ScreenUpdating = True End Sub

Si hemos hecho todo correctamente, al introducir valores en la Hoja1, de una forma similar a esta, y en la que repetimos ciertos valores (hemos supuesto que introducimos el nombre los vendedores de la empresa):


Obtendremos de forma automática, y sin darnos cuenta, estos otros datos en la Hoja2:


Ahora pensaréis: “vale, muy chulo, pero ¿y si ya tengo la hoja con los datos duplicados?, ¿voy a tener que reescribirlos para que me tome sólo los valores únicos?”. La respuesta es no. No necesitarás hacer nada de eso porque hemos preparado un pequeño macro en Excel que hará el trabajo por ti. Con este sencillo ejemplo, obtendremos valores únicos, a partir de un listado donde tengamos datos duplicados. Es decir, no será necesario que introduzcamos datos para ver como se nos añaden en la Hoja2 los valores únicos, pues tan solo tendrás que ejecutar este macro:

Sub valores_unicos() 'Ocultamos el procedimiento Application.ScreenUpdating = False 'si hay errores, que continúe On Error Resume Next 'fichamos la celda donde estamos, para volver a ella al final celda = ActiveCell.Address 'si en la hoja2, la celda B5 contiene datos, 'eliminamos todas las filas con datos contiguas If Hoja2.Range("B5") <> "" Then 'seleccionamos la hoja2 Hoja2.Select Range("B5").Select 'seleccionamos el rango contínuo hasta abajo Range(Selection, Selection.End(xlDown)).Select 'eliminamos las filas Selection.EntireRow.Delete 'volvemos a B5 Range("B5").Select End If 'seleccionamos la hoja1 Hoja1.Select 'y nos situamos en B5 Range("B5").Select 'hasta que no encuentre una fila vacía... Do While Not IsEmpty(ActiveCell) 'pasamos el dato que acabamos de introducir, a una variable dato = ActiveCell 'buscamos ese dato en la Hoja2, 'y más concretamente en la columna B With Hoja2.Range("B:B") 'buscamos el dato en esta Hoja2 Set existe = .Find(dato, LookAt:=xlWhole) 'si no existe, lo añadimos, pero antes nos 'situaremos en la primera celda donde queremos 'empezar a escribir los valores únicos If existe Is Nothing Then 'nos situamos en la primera celda donde queremos 'empezar a escribir los valores únicos Hoja2.Select Range("B5").Select 'bajamos hasta encontrar una fila vacía Do While Not IsEmpty(ActiveCell) ActiveCell.Offset(1, 0).Select Loop 'y escribimos el dato ActiveCell = dato End If 'seleccionamos la Hoja2 Hoja2.Select 'subimos al principio de la tabla Range("B5").Select End With 'volvemos a la Hoja1 Hoja1.Select 'bajamos una fila ActiveCell.Offset(1, 0).Select 'continuamos con el bucle Loop 'volvemos a la celda donde estábamos Range(celda).Select 'mostramos el procedimiento Application.ScreenUpdating = True 'mostramos un mensaje si no hay errores If Err = 0 Then mensaje = MsgBox("¡Listo!. Acabamos de hacer el " + _ Chr(10) + "trabajo que nos encargaste :-) ", vbInformation, "Trabajo hecho") End Sub

Ese macro de Excel también nos servirá para depurar la tabla de valores únicos de la Hoja2, en el caso de que eliminemos algún dato que tenga valores únicos de la Hoja1, pues no desaparecerá de forma automática ese dato eliminado en la Hoja2. Otro caso en el que nos servirá el macro, es cuando editamos un dato ya escrito. En ese caso, la primera edición permanecerá en la Hoja2, por lo que convendrá pasarle el macro, para arreglar las modificaciones realizadas en la Hoja1.

Si este nuevo macro de Excel que acabamos de hacer, lo asignamos a un botón como se muestra en la siguiente imagen, con tan solo leer el texto del propio botón, ya sabremos que es lo que va a ocurrir al presionarlo:


Con esto podríamos dar por finalizado este trabajo, porque hemos hecho lo que pretendíamos hacer al principio, que no es otra cosa que obtener valores únicos a partir de una lista con valores duplicados, pero como nos gusta complicarnos la vida, vamos a liarla un poco más.

Vamos a añadir una columna adicional en la Hoja1, con valores numéricos, de tal forma que imaginaremos que los nombres introducidos son los de nuestros vendedores, y los importes serán la cifra de ventas de cada uno de ellos:


Y claro, ahora nos interesará obtener en la Hoja2, no solo los valores únicos, sino además, la suma de la cifra de ventas de cada uno de los vendedores, y para acabar de redondearlo, el número de acciones de venta de cada uno de ellos, es decir, el número de ventas o transacciones, que no será otra cosa que el número de veces que sale en esta Hoja1, cada uno de los vendedores. Esto podríamos conseguirlo por ejemplo, mediante el uso de una tabla dinámica, pero vamos a prescindir de ella, para hacerlo "a pelo" con macros de Excel, como siempre solemos hacer.

Comenzaremos colocando este código en la Hoja1:

Private Sub Worksheet_Change(ByVal Target As Range) 'Al introducir un valor en la columna B o C, 'siempre que tengamos el dato de laotra columna 'llamaremos al macro "valores_unicos_instantaneos" If (Not Application.Intersect(Target, Range("B:B")) Is Nothing And ActiveCell <> "" And _ ActiveCell.Offset(0, 1) <> "") Or (Not Application.Intersect(Target, Range("C:C")) Is Nothing _ And ActiveCell <> "" And ActiveCell.Offset(0, -1) <> "") Then 'siempre que no estemos en la columna 1 If ActiveCell.Column > 1 Then 'llamamos al macro valores_unicos_instantaneos End If End If End Sub

Ese código anterior, lo que hace es evaluar si cada vez que escribimos un dato en la columna B, tenemos asociado a su derecha en la columna C, otro dato. Si así fuera, ejecutaríamos el macro llamado “valores_unicos_instantaneos”. También lanzaremos este macro si escribimos un dato en la columna C, y tenemos en su columna inmediatamente anterior otro dato (en la columna B).

Ahora colocaremos este código en un módulo:

Sub valores_unicos_instantaneos() 'Ocultamos el procedimiento Application.ScreenUpdating = False 'si hay errores, que continúe On Error Resume Next 'pasamos los datos a variable2 dato1 = Range("B" & ActiveCell.Row) dato2 = Range("C" & ActiveCell.Row) 'buscamos ese dato1 en la Hoja2, 'y más concretamente en la columna B With Hoja2.Range("B:B") 'buscamos el dato1 en esta Hoja2 Set existe = .Find(dato1, LookAt:=xlWhole) 'si no existe, lo añadimos, pero antes nos 'situaremos en la primera celda donde queremos 'empezar a escribir los valores únicos If existe Is Nothing Then 'nos situamos en la primera celda donde queremos 'empezar a escribir los valores únicos Hoja2.Select Range("B5").Select 'bajamos hasta encontrar una fila vacía Do While Not IsEmpty(ActiveCell) ActiveCell.Offset(1, 0).Select Loop 'y escribimos los datos, comenzando por el vendedor ActiveCell = dato1 'ahora el importe ActiveCell.Offset(0, 1) = dato2 'ahora añadimos una venta ActiveCell.Offset(0, 2) = ActiveCell.Offset(0, 2) + 1 'si ya existe, sumamos la cantidad, y añadimos una venta Else 'seleccionamos la Hoja2 Hoja2.Select 'seleccionamos el valor encontrado existe.Select 'añadimos la cantidad ActiveCell.Offset(0, 1) = ActiveCell.Offset(0, 1) + dato2 'añadimos una venta ActiveCell.Offset(0, 2) = ActiveCell.Offset(0, 2) + 1 End If End With 'subimos al principio de la tabla Range("B5").Select 'volvemos a la Hoja1 Hoja1.Select 'mostramos el procedimiento Application.ScreenUpdating = True End Sub

Este macro que acabamos de implementar está muy bien, siempre que estemos introduciendo datos, pues obtendremos los valores únicos en la Hoja2. Pero como en el caso anterior, tiene un problema, y es que cuando nos equivocamos una vez introducidos el nombre del empleado y la cifra de ventas, si editamos el nombre poniendo un nuevo vendedor, nos genera una nueva línea en la Hoja2, pero sin eliminar el dato anterior. Si eliminamos una fila con un valor único en la Hoja1, tampoco nos lo eliminará en la Hoja2.

Todo esto lo podríamos haber resuelto creando un macro que evalúe toda la columna cada vez que introducimos o editamos un dato en la Hoja1, pero eso es cargar innecesariamente el programa con tareas innecesarias. La solución pasa como en el ejemplo anterior, por ejecutar el macro que os pongo a continuación -una vez finalizada la tabla de la Hoja1-, y que podemos asignar a un botón:

Sub valores_unicos() 'Ocultamos el procedimiento Application.ScreenUpdating = False 'si hay errores, que continúe On Error Resume Next 'fichamos la celda donde estamos, para volver a ella al final celda = ActiveCell.Address 'si en la hoja2, la celda B5 contiene datos, 'eliminamos todas las filas con datos contiguas If Hoja2.Range("B5") <> "" Then 'seleccionamos la hoja2 Hoja2.Select Range("B5").Select 'seleccionamos el rango contínuo hasta abajo Range(Selection, Selection.End(xlDown)).Select 'eliminamos las filas Selection.EntireRow.Delete 'volvemos a B5 Range("B5").Select End If 'seleccionamos la hoja1 Hoja1.Select 'y nos situamos en B5 Range("B5").Select 'hasta que no encuentre una fila vacía... Do While Not IsEmpty(ActiveCell) 'pasamos los datos a variables dato1 = ActiveCell dato2 = ActiveCell.Offset(0, 1) 'buscamos ese dato1 en la Hoja2, 'y más concretamente en la columna B With Hoja2.Range("B:B") 'buscamos el dato1 en esta Hoja2 Set existe = .Find(dato1, LookAt:=xlWhole) 'si no existe, lo añadimos, pero antes nos 'situaremos en la primera celda donde queremos 'empezar a escribir los valores únicos If existe Is Nothing Then 'nos situamos en la primera celda donde queremos 'empezar a escribir los valores únicos Hoja2.Select Range("B5").Select 'bajamos hasta encontrar una fila vacía Do While Not IsEmpty(ActiveCell) ActiveCell.Offset(1, 0).Select Loop 'y escribimos los datos, comenzando por el vendedor ActiveCell = dato1 'ahora el importe ActiveCell.Offset(0, 1) = dato2 'ahora añadimos una venta ActiveCell.Offset(0, 2) = ActiveCell.Offset(0, 2) + 1 'si ya existe, sumamos la cantidad, y añadimos una venta Else 'seleccionamos la Hoja2 Hoja2.Select 'seleccionamos el valor encontrado existe.Select 'añadimos la cantidad ActiveCell.Offset(0, 1) = ActiveCell.Offset(0, 1) + dato2 'añadimos una venta ActiveCell.Offset(0, 2) = ActiveCell.Offset(0, 2) + 1 End If 'seleccionamos la Hoja2 Hoja2.Select 'subimos al principio de la tabla Range("B5").Select End With 'volvemos a la Hoja1 Hoja1.Select 'bajamos una fila ActiveCell.Offset(1, 0).Select 'continuamos con el bucle Loop 'volvemos a la celda donde estábamos Range(celda).Select 'mostramos el procedimiento Application.ScreenUpdating = True 'mostramos un mensaje si no hay errores If Err = 0 Then mensaje = MsgBox("¡Listo!. Acabamos de hacer el " + _ Chr(10) + "trabajo que nos encargaste :-) ", vbInformation, "Trabajo hecho") End Sub

De tal forma que tras ejecutar ese macro en la Hoja1, sobre esta tabla con datos:


Obtendríamos esto en la Hoja2:


Como veis, con los macros podemos idear una alternativa fiable, para competir tanto a los filtros avanzados, como a las tablas dinámicas, a la hora de presentar valores únicos, y para construir tablas con un resumen de datos.

Desde aquí podéis descargar un fichero zip comprimido, con los ejemplos de Excel que hemos visto en este artículo. En el fichero comprimido se incluye tanto el libro de Excel “Valores únicos (ejemplo 1).xls”, como el libro “Valores únicos (ejemplo 2).xls”, que son los que hemos utilizado para ilustrar este artículo.