FÓRMULAS DE EXCEL

Quitar duplicados en Excel con fórmula

Quitar duplicados en Excel con fórmula

Funciones utilizadas: INDICE, K.ESIMO.MENOR, SI, COINCIDIR, FILA, INDIRECTO,  CONTARA, TRANSPONER

 

Si bien Excel nos ofrece la posibilidad de quitar los duplicados de un rango o listado mediante la opción dentro de la barra de herramientas, muchas veces requerimos de una fórmula para poder lograr nuestro objetivo. Esto se debe a que la opción de la barra de herramientas puede llegar a desconfigurar nuestra hoja de cálculos.

En el video «Quitar duplicados en Excel con fórmula», podemos ver ejemplos de como quitar duplicados mediante opción de la barra de herramientas y con fórmulas, tanto para rangos de una sola columna, varias columnas, y también de filas.

Para listar un rango de valores de una sola columna y omitiendo los valores repetidos, podemos utilizar la siguiente fórmula matricial (no copiar los símbolos {}, sino aceptar la fórmula con CTRL+SHIFT+ENTER):

{=INDICE(lista;K.ESIMO.MENOR(SI(COINCIDIR(lista;lista;0)=FILA(INDIRECTO("1:"&CONTARA(lista)));COINCIDIR(lista;lista;0);"");FILA()-1))}

Esta fórmula se aplica a un rango que denominamos «lista», y que comienza en la fila 2. Si nuestro listado comienza en otra fila debemos modificar el argumento «FILA()-1» por «FILA()-x» donde «x» corresponde a la fila donde comienza el listado menos uno. Es decir si nuestra lista comienza en la fila 1 deberemos colocar «FILA()» y si comienza en la fila 5 colocaremos «FILA()-4».

Arrastramos la fórmula hasta encontrar el primer resultado «#¡NUM!» y lo eliminamos.

Si nuestro listado se encuentra en dos o más columnas, realizaremos los siguientes pasos:

  1. Creamos una primer columna auxiliar donde concatenaremos todos los valores de la misma fila.
  2. Creamos una segunda columna auxiliar y le aplicaremos la fórmula mencionada a las celdas previamente concatenadas.
  3. Creamos una tercera columna auxiliar, copiamos los valores de la segunda columna auxiliar y los pegamos como «solo valor».
  4. Por último, aplicamos la opción de Excel de la barra de herramientas de «Texto en columnas» utilizando la tercer columna auxiliar y las que necesitemos según cantidad de celdas concatenadas por fila del paso 1.

Por último, si nuestra lista se encuentra en una fila en vez de columna, podemos adaptar nuestra fórmula matricial incluyendo la función de transponer dentro de la sintaxis:

{=INDICE(lista3;K.ESIMO.MENOR(SI(TRANSPONER(COINCIDIR(lista3;lista3;0))=FILA(INDIRECTO("1:"&CONTARA(lista3)));TRANSPONER(COINCIDIR(lista3;lista3;0));"");FILA()-2))}

Esta fórmula se aplica a un rango que denominamos «lista3», y que comienza en la segunda columna (B). Al igual que en la fórmula anterior, debemos modificar el argumento «FILA()-2» según las celdas donde queramos presentar el resultado siempre en función de la fila inicial. En este caso, la fórmula se encuentra configurada para comenzar el listado de resultado en la fila 3.

 

Te recomiendo ver el siguiente link con más videos de Fórmulas ingeniosas de Excel AQUÍ.

Salvo aclaración, todas las fórmulas y macros de este sitio están configuradas para aplicarse sobre la celda A1. Algunas fórmulas se encuentran encerradas entre llaves {} debido a que son fórmulas matriciales. Estas llaves no deben introducirse tecleándolas, sino que se generan automáticamente al aceptar la fórmula pulsando Control+Shift+Enter al mismo tiempo. Las fórmulas de este sitio son compatibles con versiones de Microsoft Excel® 2010 o superiores.