Buscar, resaltar, filtrar, contar y eliminar duplicados en Excel

Al buscar celdas duplicadas en una única columna o lista, podemos aplicar la función CONTAR.SI para encontrar y contar rápidamente los datos duplicados con facilidad.
1. Seleccione una celda vacía junto a la columna en la que realizará la búsqueda.

2. Escriba la fórmula =CONTAR.SI($C$3:$C$12; C3)>1 y pulse la tecla Intro.

3. Arrastre el controlador de autorrelleno de la celda que contiene la fórmula para copiarla en otras celdas.

Notas: En la fórmula =COUNTIF($C$3:$C$12, C3)>1,
(1) $C$3:$C$12 es la columna o lista en la que buscará valores duplicados. Dado que esta columna debe permanecer fija al copiar la fórmula a otras celdas, normalmente se utiliza una referencia absoluta con el símbolo “$”.
(2) C3 es la primera celda de la columna especificada. Se trata de una referencia relativa, ya que debe actualizarse automáticamente al copiar la fórmula a otras celdas.
(3) Esta fórmula devuelve VERDADERO o FALSO. VERDADERO indica que el valor correspondiente es un duplicado, mientras que FALSO significa que es único en la columna.
(4) A veces, los resultados VERDADERO o FALSO pueden no ser intuitivos. Para hacerlos más claros, podemos combinar la fórmula original con la función SI y obtener directamente Duplicados:
=IF(COUNTIF($C$3:$C$12, C3)>1,«Duplicates»,«»)

En algunos casos, necesita comparar dos columnas y encontrar los valores duplicados. Por ejemplo, dispone de dos listas de nombres y desea identificar los nombres de la segunda lista que ya aparecen en la primera para eliminarlos. Puede lograrlo fácilmente combinando las funciones BUSCARV y SI.ERROR.

1. Seleccione una celda vacía junto a la segunda lista de nombres.

2. Introduzca la fórmula =IFERROR(VLOOKUP(D3,$B$3:$B$18,1,0),«») y pulse la tecla Entrar.

3. Arrastre el controlador de autorrelleno de la celda que contiene la fórmula para copiarla a otras celdas según lo necesite.

Notas: En la fórmula anterior,
(1) D3 es la primera celda de la segunda lista de nombres. La referencia debe cambiar automáticamente al copiar la fórmula a otras celdas; por tanto, es relativa.
(2) $B$3:$B$18 es la lista de nombres. Se trata de una referencia absoluta porque el rango debe permanecer estático al copiar la fórmula a otras celdas.
(3) Cuando un nombre coincide con alguno de la primera lista, la fórmula devuelve dicho nombre; en caso contrario, devuelve una celda vacía.
(4) También puede aplicar la fórmula =IF(COUNTIF($B$3:$B$18,D3)>0,«Duplicates»,«») para identificar y eliminar nombres duplicados de la segunda lista en comparación con la primera. Esta fórmula devolverá «Duplicados» si el nombre correspondiente es un duplicado.

(5) Si necesita comparar rangos de duplicados en dos columnas situadas en hojas distintas, basta con añadir el nombre de la hoja delante de la referencia de la columna que se compara. En nuestro ejemplo, basta con cambiar $B$3:$B$18 por Hoja1!$B$3:$B$18 en la fórmula.

Las fórmulas anteriores no distinguen entre mayúsculas y minúsculas al buscar duplicados, por lo que «manzana» se considera un duplicado de «MANZANA». Puede usar una fórmula matricial para encontrar valores duplicados en una sola columna respetando la coincidencia exacta de mayúsculas y minúsculas.

1. Seleccione una celda en blanco junto a la columna en la que va a buscar duplicados.

2. Escriba la fórmula matricial =IF(SUM((--EXACT($C$3:$C$12,C3)))<=1,«»,«Duplicate») y pulse las teclas Ctrl + Mayús + Entrar.

3. Arrastre el controlador de autorrelleno de la celda que contiene la fórmula para copiar la fórmula matricial a otras celdas.

Notas: En la fórmula matricial anterior,
(1) $C$3:$C$12 es el rango de la columna en el que se deben buscar valores duplicados. La referencia es absoluta porque permanece fija al copiar la fórmula matricial a otras celdas.
(2) C4 es la primera celda del rango. La referencia es relativa porque debe actualizarse automáticamente al copiar la fórmula matricial a otras celdas.
(3) Si la celda correspondiente contiene un valor duplicado, la fórmula matricial devolverá «Duplicado»; en caso contrario, dejará la celda vacía.

Puede aplicar la función Usar formato condicional para resaltar rápidamente las celdas duplicadas en una columna o en un rango.

1. Seleccione la columna en la que quiera resaltar las celdas duplicadas.

2. Haga clic en Inicio > Usar formato condicional > Reglas para resaltar celdas > Valores duplicados. Vea la siguiente captura de pantalla:

3. En el cuadro de diálogo emergente Valores duplicados, seleccione Duplicados en la primera lista desplegable, elija un estilo de resaltado en la segunda lista desplegable y haga clic en el botón Aceptar.

Notas: Si los estilos de resaltado predefinidos no satisfacen sus necesidades, puede seleccionar Formato personalizado en la segunda lista desplegable y, a continuación, elegir el color de resaltado, la fuente y los bordes de celda que desee en el cuadro de diálogo emergente Establecer formato de celda.

A continuación, verá que todas las celdas duplicadas están resaltadas en la columna seleccionada, tal como se muestra en la siguiente captura de pantalla.

Notas:
(1) Después de resaltar las celdas duplicadas, podemos filtrar fácilmente dichos duplicados. ()Haga clic para ver cómo)
(2) Después de resaltar las celdas duplicadas, también podemos eliminar los duplicados masivamente de forma sencilla. ()Haga clic para ver cómo)

Algunos usuarios prefieren Rango de fila resaltada según las celdas duplicadas de una determinada columna. En este caso, podemos crear una regla personalizada de Usar formato condicional para lograrlo.

1. Seleccione el rango (excluyendo la fila de encabezado) sobre el que desea resaltar filas según las celdas duplicadas.

2. Haga clic en Inicio > Usar formato condicional > Nueva regla.

3. En el cuadro de diálogo Nueva regla de formato,
(1) seleccione la opción Utilizar una fórmula para determinar qué celdas hay que dar formato;
(2) introduzca la fórmula =COUNTIF($C$3:$C$12,$C3)>1 en el cuadro Dar formato a los valores donde esta fórmula sea verdadera;
Consejo: En la fórmula, $C$3:$C$12 es la columna que contiene las celdas duplicadas y $C3 es la primera celda de dicha columna.
(3) Haga clic en el botón Formato.

4. En el cuadro de diálogo Establecer formato de celda, especifique el color de relleno, la fuente y los bordes de celda que desee, y haga clic sucesivamente en los botones Aceptar para guardar la configuración.

Hasta ahora, en el Seleccionar rango, las filas se resaltan según las celdas duplicadas de la columna especificada. Vea la siguiente captura de pantalla:

Para resaltar filas duplicadas en un rango determinado, también puede utilizar la función Usar formato condicional.

1. Seleccione el rango sin incluir la fila de encabezado.

2. Haga clic en Inicio > Usar formato condicional > Nueva regla.

3. En el cuadro de diálogo Nueva regla de formato:
(1) Haga clic para seleccionar la opción Usar una fórmula que determine qué celdas se deben dar formato;
(2) En el cuadro Dar formato a los valores donde esta fórmula sea verdadera, escriba la fórmula =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1;
(3) Haga clic en el botón Formato.

Notas: En la fórmula =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1:
(1) $B$3:$B$12 es la primera columna del rango, y $B3 es la primera celda de esta columna;
(2) $C$3:$C$12 es la segunda columna del rango, y $C3 es la primera celda de esta columna;
(3) $D$3:$D$12 es la tercera columna del rango, y $D3 es la primera celda de esta columna;
(4) Si su rango incluye más columnas, puede añadir sucesivamente las referencias de columna y sus primeras celdas en la fórmula.

4. En el cuadro de diálogo Establecer formato de celda, especifique el color de resaltado, la fuente, los bordes de celda, etc., según sus necesidades y, a continuación, haga clic sucesivamente en los botones Aceptar para guardar la configuración.

Hasta ahora, los Fila duplicada se han identificado y resaltado en el Seleccionar rango. Vea la captura de pantalla:

Habrá notado que, con los métodos anteriores, todos los duplicados se identifican o resaltan. Sin embargo, a veces solo quiere ver los elementos duplicados excluyendo la primera instancia de cada uno. Esto también puede lograrse mediante la función Usar formato condicional y una fórmula diferente.

1. Seleccione la columna con encabezado.
Consejo: Si necesita resaltar un rango de filas basado en valores duplicados en una columna excluyendo las primeras instancias, seleccione el rango sin incluir la fila de encabezado.

2. Haga clic en Inicio > Usar formato condicional > Nueva regla.

3. En el cuadro de diálogo Nueva regla de formato:
(1) Haga clic para seleccionar la opción Usar una fórmula que determine qué celdas se deben dar formato;
(2) En el cuadro Dar formato a los valores donde esta fórmula sea verdadera, introduzca la fórmula =COUNTIF($C$3:$C3, C3)>1;
Consejos: Para resaltar un rango de filas basado en duplicados en una columna, utilice la fórmula =COUNTIF($C$3:$C3, $C3)>1.
(3) Haga clic en el botón Formato.

4. En el cuadro de diálogo emergente Establecer formato de celda, especifique el color de resaltado, la fuente, los bordes de celda, etc., según sus necesidades y, a continuación, haga clic en el botón Aceptar para guardar la configuración.

A continuación, verá que las celdas duplicadas, excepto las primeras instancias en la columna seleccionada (o filas basadas en duplicados en la columna especificada), están resaltadas. Vea la captura de pantalla:

Cuando resaltamos celdas o filas duplicadas con la función Usar formato condicional, todos los duplicados se marcan con el mismo color. Sin embargo, si cada grupo de valores duplicados se resalta con un color distinto, será mucho más fácil identificarlos y apreciar sus diferencias. Para lograrlo en Excel, puede utilizar un código VBA.

1. Pulse simultáneamente las teclas Alt+F11 para abrir la ventana de Microsoft Visual Basic para Aplicaciones.

2. En la ventana, haga clic en Insertar > Módulo y, a continuación, pegue el siguiente código en la nueva ventana del módulo.

VBA: Resaltar celdas duplicadas con colores distintos:

Sub HighlightDuplicatesInDifferentColors()
'Update by Extendoffice 20201013
Dim xURg, xRg, xFRg, xRgPre As Range
Dim xAddress As String
Dim xDt As Object
Dim xFNum, xCInt As Long
Dim xBol As Boolean
Dim xWs As Worksheet
Dim xSArr
Set xRg = Application.ActiveWindow.RangeSelection
If xRg.Count > 1 Then
xAddress = xRg.AddressLocal
Else
xAddress = xRg.Worksheet.UsedRange.AddressLocal
End If
On Error Resume Next
Set xURg = Application.InputBox("Select range:", "Kutools for Excel", xAddress, , , , , 8)
If xURg Is Nothing Then Exit Sub
Set xURg = Intersect(xURg.Worksheet.UsedRange, xURg)
Set xDt = CreateObject("scripting.dictionary")
Set xWs = xURg.Worksheet
xCInt = 5
xBol = Application.ScreenUpdating
Application.ScreenUpdating = False
For xFNum = 1 To xURg.Count
Set xFRg = xURg.Item(xFNum)
If Not IsError(xFRg) Then
If xFRg.Value <> "" Then
If xDt.exists(xFRg.Text) Then
xSArr = Split(xDt(xFRg.Text), ";")
If xSArr(1) = "Only" Then
xCInt = xCInt + 1
xSArr(1) = xCInt
Set xRgPre = xWs.Range(xSArr(0))
xRgPre.Interior.ColorIndex = xCInt
xDt(xFRg.Text) = xSArr(0) & ";" & xSArr(1)
End If
xFRg.Interior.ColorIndex = xSArr(1)

Else
xDt(xFRg.Text) = xFRg.Address & ";Only"
End If

End If
End If
Next
xURg.Worksheet.Active
xURg.Select
Application.ScreenUpdating = xBol
End Sub

3. Pulse la tecla F5 o haga clic en el icono Ejecutar para ejecutar este código VBA.

4. En el cuadro de diálogo emergente de Kutools para Excel, selecciona la columna en la que deseas resaltar las celdas duplicadas con colores distintos y haz clic en el botón Aceptar.

Cada serie de Valores duplicados se resaltará ahora con un color distinto.

La primera herramienta que presentaré es la función Seleccionar Celdas Duplicadas/Únicas, proporcionada por Kutools para Excel. ¡Esta función te permite identificar al instante celdas únicas o duplicadas de forma sencilla y eficaz!

1. Seleccione la columna o el rango en el que quiera encontrar y resaltar las celdas duplicadas.

2. Haga clic en Kutools > Seleccionar > Seleccionar Celdas Duplicadas/Únicas.

3. En el cuadro de diálogo Seleccionar celdas duplicadas/únicas, marca las opciones que necesites y haz clic en los botones Aceptar para finalizar la operación.

Notas: En el cuadro de diálogo Seleccionar Celdas Duplicadas/Únicas,
(1) Si necesita seleccionar o resaltar todos los duplicados excepto la primera instancia, marque la opción Valores Duplicados (Excluir la primera coincidencia). De lo contrario, marque la opción Valores Duplicados (Incluir la primera coincidencia).
(2) Si desea resaltar los duplicados, active la opción Color de Fondo de Relleno y especifique un color de resaltado según sus necesidades.
(3) Si quiere seleccionar o resaltar filas completas en función de los duplicados de la columna seleccionada, active la opción Seleccionar Fila Completa.
(4) Si necesita seleccionar o resaltar valores duplicados distinguiendo entre mayúsculas y minúsculas, active la opción Distinguir Mayúsculas y Minúsculas.

Kutools para Excel también ofrece una herramienta extraordinaria: Comparar Celdas, que te permite encontrar y resaltar fácilmente celdas duplicadas en dos columnas.

1. Haga clic en Kutools > Comparar Celdas para abrir el cuadro de diálogo Seleccionar celdas iguales/diferentes.

2. En el cuadro de diálogo Seleccionar celdas iguales/diferentes, especifica las dos columnas en los cuadros Buscar valores en y Según, marca la opción Valores iguales y activa otras opciones según tus necesidades.

Notas:
(1) Si necesita encontrar Fila duplicada, active la opción Cada fila; y para encontrar celdas duplicadas, active la opción Celda únicaen la sección Basado en;
(2) Active la opción Color de Fondo de Rellenoy especifique un color de resaltado si necesita resaltar los Fila duplicada o las celdas;
(3) Active la opción Seleccionar Fila Completasi necesita seleccionar o resaltar los Fila completa según los duplicados;
(4) Active la opción Distinguir Mayúsculas y Minúsculassi desea encontrar o resaltar duplicados que distingan mayúsculas y minúsculas.

3. Haga clic sucesivamente en los botones Aceptar para finalizar la configuración.

A continuación, verá que los duplicados en la columna especificada en el cuadro Buscar valores en están identificados y resaltados.

Este método le guiará para identificar y resaltar celdas duplicadas mediante una regla de Usar formato condicional y, a continuación, filtrar fácilmente por el color de resaltado en Excel.

1. Aplique formato condicional para encontrar y resaltar duplicados en la columna especificada. ()Haga clic para ver cómo)

2. Haga clic en la cabecera de la columna especificada y, a continuación, en Datos > Filtro.

3. A continuación, haga clic en el icono de filtro de la cabecera de la columna, seleccione Filtrar por color y, después, elija el color «Usar formato condicional» en la lista desplegable. Vea la captura de pantalla:

A continuación, verá que solo las filas con celdas duplicadas se han filtrado. Vea la captura de pantalla:

Alternativamente, también podemos Marcar duplicados con una fórmula en una columna auxiliar y, a continuación, filtrar fácilmente los duplicados mediante dicha columna auxiliar en Excel.

1. Además de los datos originales, añada una columna auxiliar y escriba Duplicado como encabezado de columna.

2. Seleccione la primera celda vacía bajo el encabezado de columna, introduzca la fórmula =IF(COUNTIF($C$3:$C$12,C3)>1,«Duplicate»,«») y arrastre el controlador de autorrelleno de esta celda para copiar la fórmula en las demás celdas.

Notas: En la fórmula anterior, $C$3:$C$12 es el rango que contiene los datos duplicados y C3 es la primera celda (excluyendo la del encabezado) de dicha columna.

3. Haga clic en el encabezado «Duplicados» y, a continuación, haga clic en Datos > Filtro.

4. A continuación, haga clic en el icono de filtro de la cabecera de la columna, active únicamente la casilla Duplicado y haga clic en el botón Aceptar. Vea la captura de pantalla:

Hasta ahora, solo se han filtrado las filas con Valores duplicados. Vea la captura de pantalla:

En general, podemos usar la función =CONTAR.SI(rango; criterio) para contar cuántas veces aparece un valor específico dentro de un rango determinado. Por ejemplo, para saber cuántas veces aparece «Apple» en la lista A2:A10, basta con aplicar la fórmula =COUNTIF(A2:A10, «Apple») y obtener así el número total de apariciones de ese valor.

Sin embargo, la fórmula =CONTAR.SI(rango; criterio) solo cuenta el valor duplicado especificado. ¿Qué ocurre si queremos contar valores duplicados con dos o más criterios? ¿Y si necesitamos contar duplicados que distingan mayúsculas y minúsculas con criterios? Los métodos siguientes le ayudarán a resolver estos problemas.

Podemos aplicar una fórmula matricial para contar Valores duplicados que distingan mayúsculas y minúsculas con criterios en Excel. Por ejemplo, para contar cuántas veces aparece el valor «Apple» en la lista B2:B21 con Coincidir Mayúsculas y Minúsculas, puede hacerlo de la siguiente manera:

1. Seleccione una celda vacía.

2. Introduzca la fórmula =SUMA(--EXACTO(B2:B20;E2)).

3. Pulse Ctrl+Mayús+Intro para obtener el resultado del recuento.

Notas: En la fórmula matricial,
(1)B2:B20es la columna en la que contará los duplicados.
(2)E2es la celda que contiene el valor especificado cuyo número de apariciones desea contar. Puede cambiar la referencia de celda por el valor entre comillas, por ejemplo,«Apple».

A veces, es posible que quieras contar duplicados según dos o más criterios. Para ello, puedes utilizar la función CONTAR.SI.CONJUNTO.
Por ejemplo, imagina que tienes una tabla de ventas de frutas como la que se muestra en la siguiente captura de pantalla. En este caso, necesitamos contar cuántas veces aparece la manzana vendida el 7/5/2020 con una cantidad superior a 300. Puedes contar esos duplicados aplicando los siguientes criterios:

1. Seleccione una celda vacía.

2. Introduzca la fórmula =COUNTIFS(B3:B20,G4,C3:C20,G3,D3:D20,«>300»).

3. Pulse la tecla Intro para obtener el resultado del recuento.

Notas: En la fórmula anterior,
(1) B3:B20 es la columna (primera) de fechas y G4 es el criterio de fecha;
(2) C3:C20 es la columna (segunda) de frutas y G3 es el criterio de fruta;
(3) D3:D20 es la columna (tercera) de cantidades y «>300» es el criterio de cantidad.
(4) Si su tabla incluye más columnas y criterios, puede añadir referencias de columna y criterios adicionales.

Para contar todos los duplicados de una columna excepto la primera aparición, proceda de la siguiente manera:

1. Seleccione una celda vacía junto a la columna.

2. Introduzca la fórmula =IF(COUNTIF($B$3:B3,B3)>1,«YES»,«») y, a continuación, arrastre el controlador de autorrelleno hacia abajo para copiarla en las demás celdas.

Notas: En la fórmula anterior,
(1)$B$3:B3 es el rango en el que se cuentan los duplicados. En $B$3:B3, B3 se actualiza automáticamente al copiar la fórmula en otras celdas.
(2)B3 es la primera celda de la columna especificada.
(3) Esta fórmula devuelve «SÍ» o deja la celda en blanco: «SÍ» indica que el valor correspondiente es un duplicado, mientras que una celda en blanco significa que es único.

De este modo, se identifican todos los duplicados de la columna especificada. Podemos contar los resultados de la fórmula para obtener el número total de duplicados.

3. Seleccione una celda vacía.

4. Introduzca la fórmula =COUNTIF(C3:C16,«YES») y pulse la tecla Intro.

Notas: En la fórmula anterior,
(1) C3:C16 es el rango al que aplicamos la fórmula para marcar duplicados en el paso anterior.
(2) SÍ es el valor devuelto por la fórmula anterior.

A continuación, obtenemos el número total de Valores duplicados dentro de la columna especificada. El número total de duplicados no incluye las primeras apariciones.

Para contar el número de todos los duplicados, incluidas las primeras apariciones en Excel, puede aplicar una fórmula matricial para lograrlo.

1. Seleccione una celda vacía.

2. Introduzca la fórmula =ROWS(B3:B16)-SUM(IF(COUNTIF(B3:B16,B3:B16)=1,1,0)).

3. Pulse Ctrl+Mayús+Intro para obtener el resultado del recuento.

Notas: En la fórmula anterior, B3:B16 es el rango de celdas especificado en el que queremos contar los duplicados, incluidas sus primeras apariciones.

Para simplificar su trabajo y evitar tener que memorizar largas y aburridas fórmulas, pruebe la función Seleccionar Celdas Duplicadas/Únicas de Kutools para Excel, que le permite contar rápidamente el número de valores duplicados en la lista o columna especificada, incluyendo o excluyendo las primeras apariciones.

1. Seleccione la columna en la que desea contar el número de valores duplicados y haga clic en Kutools > Seleccionar > Seleccionar Celdas Duplicadas/Únicas.

2. En el cuadro de diálogo Seleccionar Celdas Duplicadas/Únicas, active la opción Valores Duplicados (Excluir la primera coincidencia) o Valores Duplicados (Incluir la primera coincidencia), según sus necesidades, y haga clic en el botón Aceptar.

3. A continuación, se seleccionan todos los valores duplicados, ya sea incluyendo o excluyendo las primeras apariciones, y aparece simultáneamente un cuadro de diálogo que muestra cuántas celdas se han seleccionado. Vea la captura de pantalla anterior.

Supongamos que desea comparar dos Lista de nombres y contar el número de duplicados entre ellas. ¿Cómo puede resolver este problema rápidamente? También podemos lograrlo mediante una fórmula en Excel.

1. Seleccione una celda vacía.

2. Introduzca la fórmula =SUMPRODUCT(--(ISNUMBER(MATCH(B3:B12,D3:D18,0)))).

3. Pulse la tecla Intro.

Notas: En la fórmula anterior,
(1) B3:B12 es la primera columna de nombres en la que se contarán los duplicados.
(2) D3:D18 es la segunda columna de nombres en la que se basará el recuento de duplicados.

Alternativamente, podemos utilizar un complemento de terceros, Kutools para Excel, para contar rápida y fácilmente el número total de celdas duplicadas entre dos columnas.

1. Haga clic en Kutools > Seleccionar > Seleccionar celdas iguales/diferentes.

2. En el cuadro de diálogo Seleccionar celdas iguales y diferentes,
(1) Especifique las dos columnas en los campos Buscar valores en y Según, respectivamente.
(2) Active la opción Celda única.
(3) Active la opción Valores iguales.

4. Haga clic en el botón Aceptar.

A continuación, se seleccionan todas las celdas duplicadas de la primera columna y, simultáneamente, aparece un cuadro de diálogo que indica cuántas celdas duplicadas se han seleccionado. Vea la captura de pantalla:

Notas: Esta función cuenta el número total de valores duplicados en la columna que haya especificado en el campo Buscar valores en del cuadro de diálogo Seleccionar celdas iguales/diferentes. Si necesita contar el número total de valores duplicados en la segunda columna, vuelva a aplicar la función Seleccionar celdas iguales/diferentes, indicando la segunda columna en el campo Buscar valores en.

Puede contar rápidamente cada valor duplicado una sola vez con una fórmula como la siguiente:

1. Seleccione una celda vacía.

2. Introduzca la fórmula =SUMPRODUCT((C3:C19<>«»)/COUNTIF(C3:C19,C3:C19&«»)).

3. Pulse la tecla Intro para obtener el resultado del recuento.

Notas: En la fórmula anterior, C3:C19 es el rango de columnas especificado en el que desea contar cada valor duplicado una sola vez.

Al contar una lista, puede contar cada valor duplicado una sola vez con Coincidir Mayúsculas y Minúsculas aplicando una fórmula matricial en Excel.

1. Seleccione una celda vacía.

2. Introduzca la fórmula matricial =SUM(IFERROR(1/IF(C3:C19<>«», FREQUENCY(IF(EXACT(C3:C19, TRANSPOSE(C3:C19)), MATCH(ROW(C3:C19), ROW(C3:C19)), «»), MATCH(ROW(C3:C19), ROW(C3:C19))), 0), 0)).

3. Pulse simultáneamente las teclas Ctrl + Mayús + Intro para obtener el resultado del recuento.

Notas: En la fórmula matricial anterior, C3:C19 es la columna especificada en la que se contará cada serie de duplicados una sola vez, respetando mayúsculas y minúsculas.

Si tiene instalado Kutools para Excel, también puede utilizar su función Contar celdas con valores únicos para contar rápidamente cada serie de valores duplicados una sola vez en Excel.

1. Seleccione una celda vacía.

2. Haga clic en Kutools > Asistente de fórmulas > Estadísticas > Contar el número de valores únicos en un rango (incluyendo el primer duplicado).

3. En el cuadro de diálogo Asistente de fórmulas, especifica en el campo Rango la columna en la que quieras contar los duplicados una sola vez y haz clic en el botón Aceptar.

A continuación, el resultado del recuento se introduce inmediatamente en la celda seleccionada.

Podemos utilizar la función Subtotales para contar cada serie de valores duplicados en una columna en Excel.

1. Seleccione la columna en la que va a contar cada serie de valores duplicados y haga clic en Datos > Ascendente o Ordenar de Z a A.

2. En el cuadro de diálogo emergente Advertencia de ordenación, active la opción Ampliar la selección y haga clic en el botón Ordenar.

A continuación, verá que la selección se ha ordenado por el Valores duplicados de la columna especificada.

3. Mantenga la selección activa y haga clic en Datos > Subtotales.

4. En el cuadro de diálogo Subtotales,
(1) seleccione la columna deseada en la lista desplegable En cada cambio de;
(2) elija Contar en la lista desplegable Usar función;
(3) active únicamente la casilla correspondiente a la columna especificada en el cuadro de lista Agregar subtotal a;
(4) haga clic en el botón Aceptar.

A continuación, verá que cada serie de valores duplicados se ha contado y el resultado del recuento se ha añadido debajo de cada serie de valores duplicados; consulte la captura de pantalla anterior.

También podemos crear una Tabla Dinámica para contar rápidamente cada serie de valores duplicados de forma sencilla en Excel.

1. Seleccione el rango que contiene la columna especificada y haga clic en Insertar > Tabla dinámica.

2. En el cuadro de diálogo Crear tabla dinámica, indica dónde quieres ubicar la nueva tabla dinámica y haz clic en el botón Aceptar.

3. En el panel Campos de tabla dinámica, arrastre la columna especificada tanto a las secciones Filas como a Valores. A continuación, verá que cada serie de valores duplicados en la columna especificada se ha contado automáticamente. Consulte la captura de pantalla:

Si ya ha instalado Kutools para Excel, puede utilizar su práctica función Combinar filas avanzado para contar rápidamente cada serie de valores duplicados en la columna especificada.

Notas: La función Combinar filas avanzadas modificará el rango seleccionado y eliminará filas según los valores duplicados en la columna clave especificada. Para proteger sus datos, le recomendamos hacer una copia de seguridad o copiarlos en otro lugar antes de realizar las siguientes operaciones.

1. Añada una columna en blanco a la derecha del rango de datos original y asígnele el nombre Recuento.

2. Seleccione conjuntamente el rango de datos original y la nueva columna, y haga clic en Kutools > Combinar y dividir > Combinar filas avanzado.

3. En el cuadro de diálogo Combinar filas avanzado,
(1) haga clic para seleccionar la columna especificada en la que desea contar cada serie de valores duplicados y, a continuación, haga clic en Clave principal.
(2) Haga clic para seleccionar la nueva columna (Recuento) y, a continuación, haga clic en Calcular > Contar.
(3) Especifique los tipos de combinación o cálculo para otras columnas si es necesario.
(4) Haga clic en el botón Aceptar.

A continuación, verá que cada serie de valores duplicados en la columna especificada se ha contado masivamente. Consulte la captura de pantalla:

Supongamos que hay una lista de frutas en una columna. Dentro de la lista, algunas frutas aparecen varias veces. Ahora necesita marcar cada fruta duplicada en el orden en que aparece. ¿Cómo podría resolverlo? En esta sección se presenta una fórmula para lograrlo fácilmente en Excel.

1. Añada una columna en blanco a la derecha de los datos originales.

2. Introduzca la fórmula =IF(COUNTIF($C$3:$C$14,C3)>1,COUNTIF(C$3:C3,C3),«») en la primera celda de la columna en blanco añadida.

3. Arrastre el controlador de autorrelleno de la celda que contiene la fórmula para copiarla en las demás celdas.

Notas: En la fórmula anterior,
(1) $C$3:$C$14 es el rango de la columna en el que desea contar los valores duplicados en orden.
(2) C3 es la primera celda de dicho rango.
(3) Si el valor correspondiente es un duplicado, la fórmula devolverá un número secuencial (1, 2, 3…) según su orden de aparición; si el valor es único, la celda permanecerá en blanco.

Puede aplicar la función Eliminar duplicados para eliminar directamente todos los valores duplicados, excepto la primera aparición.

1. Seleccione la columna en la que desea eliminar todos los valores duplicados, excepto la primera aparición.

2. Haga clic en Datos > Eliminar duplicados.

3. En el cuadro de diálogo Advertencia de Eliminar duplicados, active la opción Continuar con la Selección actual y haga clic en el botón Eliminar duplicados.

Consejo: para eliminar filas según los valores duplicados en la selección, active la opción Ampliar la selección.

4. En el cuadro de diálogo Eliminar duplicados, marca únicamente la casilla de la columna especificada y haz clic en el botón Aceptar.

Consejos: Si ha activado la opción Ampliar la selección en el paso anterior, aquí aparecerán todas las columnas. Sin embargo, solo debe marcar la casilla de la columna especificada.

5. A continuación, aparecerá un cuadro de diálogo indicando cuántos valores duplicados se han eliminado. Haga clic en el botón Aceptar para cerrarlo.

También puede aplicar la función Filtro avanzado para eliminar fácilmente todos los valores duplicados de la columna especificada.

1. Haga clic en Datos > Avanzado.

2. En el cuadro de diálogo Filtro avanzado,
(1) active la opción Copiar a otra ubicación;
(2) en el cuadro Rango de la lista, seleccione la columna específica de la que desea eliminar los valores duplicados;
(3) en el cuadro Copiar a, indique el rango donde desea pegar la columna;
(4) active la opción Solo registros únicos;
(5) haga clic en el botón Aceptar.

A continuación, verá que la columna especificada se ha pegado en la Rango limitado con todos los Valores duplicados eliminados excepto las primeras apariciones. Consulte la captura de pantalla:

También puede aplicar un código VBA para eliminar rápidamente todos los Valores duplicados excepto las primeras apariciones de una columna en Excel.

1. Pulse las teclas Alt + F11 para abrir la ventana de Microsoft Visual Basic para Aplicaciones.

2. Haga clic en Insertar > Módulo y, a continuación, pegue el siguiente código VBA en la nueva ventana del módulo.

VBA: Eliminar valores duplicados excepto las primeras apariciones

Sub ExtendOffice_RemoveAllDeplicate()
Dim xRg As Range
Dim xURg, xFRg, xFFRg As Range
Dim xI, xFNum, xFFNum As Integer
Dim xDc As Object
Dim xDc_keys
Dim xBol As Boolean
Dim xStr As String
Dim xWs As Worksheet
Dim xURgAddress As String
On Error Resume Next
Set xRg = Application.InputBox("Select range:", "Kutools for Excel", "", , , , , 8)
If xRg Is Nothing Then Exit Sub
Set xURg = Intersect(xRg.Worksheet.UsedRange, xRg)
Set xWs = xURg.Worksheet
Set xDc = CreateObject("scripting.dictionary")
xURgAddress = xURg.Address
xBol = Application.ScreenUpdating
Application.ScreenUpdating = False
For xFNum = 1 To xURg.Count
Set xFRg = xURg.Item(xFNum)
If (Not IsError(xFRg)) Then
If xFRg.Value <> "" And (Not IsError(xFRg)) Then
For xFFNum = xFNum + 1 To xURg.Count
Set xFFRg = xURg.Item(xFFNum)
If Not IsError(xFFRg) Then
If xFFRg.Value = xFRg.Value Then
xDc(xFFRg.Address) = ""
End If
End If
Next
End If
End If
Next
xStr = ""
xDc_keys = xDc.Keys

For xI = 1 To UBound(xDc_keys)
If xStr = "" Then
xStr = xDc_keys(xI)
Set xURg = xWs.Range(xStr)
Else
xStr = xStr & "," & xDc_keys(xI)
Set xURg = Application.Union(xWs.Range(xDc_keys(xI)), xURg)
End If
Next
Debug.Print xStr
xWs.Activate
xURg.Select
Selection.Delete Shift:=xlUp
xWs.Range(xURgAddress).Select
Application.ScreenUpdating = xBol
End Sub

3. Pulse la tecla F5 o haga clic en el botón Ejecutar para ejecutar este código VBA.

4. En el cuadro de diálogo emergente, especifica el rango del que deseas eliminar los valores duplicados y haz clic en el botón Aceptar.

Todos los Valores duplicados, excepto las primeras apariciones, se eliminan inmediatamente del Rango limitado.

Nota: Este código VBA es Distinguir Mayúsculas y Minúsculas.

Podemos resaltar todos los Valores duplicados, incluidas las primeras apariciones, en una columna o una lista con una regla de Usar formato condicional y, a continuación, filtrar todos los Valores duplicados por el color de resaltado. Después, podemos seleccionar todas las celdas duplicadas filtradas y eliminarlas masivamente.

1. Aplique el formato condicional para resaltar los valores duplicados. ()Haga clic para ver cómo)

2. Seleccione la columna de la que desea eliminar todos los valores duplicados (incluidas las primeras apariciones) y haga clic en Datos > Filtro.

3. Haga clic en el icono de filtro de la cabecera de la columna especificada. En el menú desplegable, seleccione Filtrar por color y, a continuación, elija el color de resaltado en el submenú.

Así se filtran todos los Valores duplicados.

4. Seleccione todas las celdas filtradas, haga clic con el botón derecho y elija Eliminar fila en el menú contextual. A continuación, en el cuadro de diálogo de confirmación emergente, haga clic en el botón Aceptar para continuar.

5. Así se eliminan todos los valores duplicados masivamente. Mantenga seleccionada la lista filtrada y haga clic nuevamente en Filtro > Datos para desactivar el filtro.

Hasta ahora, verá que todas las celdas duplicadas, incluidas las primeras apariciones, se han eliminado masivamente y solo quedan los valores únicos.

Notas: Este método elimina filas según los valores duplicados de la columna especificada.

También podemos aplicar una fórmula para identificar los Valores duplicados, incluidas las primeras apariciones, en una columna auxiliar, luego filtrar los Valores duplicados según los resultados de la fórmula y, finalmente, eliminar estos Valores duplicados filtrados masivamente.

1. Añada una columna auxiliar junto a la columna especificada, introduzca la fórmula =CONTAR.SI($B$3:$B$11;B3) en la primera celda de la columna auxiliar y, a continuación, arrastre el controlador de autorrelleno hacia abajo para aplicar la fórmula al resto de celdas. Vea la captura de pantalla:

Nota: En la fórmula anterior, $B$3:$B$11 es el rango de la columna del que eliminará los valores duplicados, y B3 es la primera celda de dicho rango.

2. Seleccione la columna auxiliar y haga clic en Datos > Filtro.

3. Haga clic en el icono de filtro de la cabecera de la columna auxiliar, marque en el menú desplegable todos los valores excepto 1 y haga clic en el botón Aceptar. Vea la captura de pantalla:

4. Ahora se filtran todos los valores duplicados. Seleccione las celdas filtradas en la columna auxiliar, haga clic con el botón derecho y elija Eliminar fila en el menú contextual.

5. En el cuadro de diálogo de confirmación emergente, haga clic en el botón Aceptar para continuar.

6. Ahora se eliminan masivamente todos los valores duplicados y sus filas. A continuación, haga clic nuevamente en Datos > Filtro para desactivar el filtro.

Entonces verá que todos los Valores duplicados, incluidas las primeras apariciones, se han eliminado masivamente.

Si tiene instalado Kutools para Excel, también puede utilizar su función Seleccionar Celdas Duplicadas/Únicas para seleccionar y eliminar rápidamente los valores duplicados, incluyendo o excluyendo las primeras apariciones, de forma sencilla en Excel.

1. Seleccione la columna de la que desea eliminar duplicados.

2. Haga clic en Kutools > Seleccionar > Seleccionar Celdas Duplicadas/Únicas.

3. En el cuadro de diálogo Seleccionar celdas duplicadas/únicas, active la opción Valores duplicados (incluir la primera coincidencia) y haga clic en el botón Aceptar.

Notas:
(1) Para seleccionar y eliminar valores duplicados excluyendo las primeras apariciones, active la opción Valores Duplicados (Excluir la primera coincidencia).
(2) Para seleccionar y eliminar filas según los valores duplicados de la columna especificada, active la opción Seleccionar Fila Completa.
(3) Para seleccionar y eliminar valores duplicados con coincidencia entre mayúsculas y minúsculas, active la opción Distinguir Mayúsculas y Minúsculas.
(4) Para seleccionar, resaltar y eliminar celdas o filas duplicadas, active las opciones Color de Fondo de Relleno o Color de Fuente de Relleno y especifique el color de relleno o de fuente según sus necesidades.

4. A continuación, aparecerá un cuadro de diálogo indicando cuántas celdas se han seleccionado; haga clic en el botón Aceptar para cerrarlo.

5. Haga clic con el botón derecho en las celdas seleccionadas y elija Eliminar en el menú contextual.

6. En el cuadro de diálogo Eliminar que aparece, active la opción Desplazar celdas hacia arriba y haga clic en el botón Aceptar.

Hasta ahora, todos los Valores duplicados, incluidas las primeras apariciones, se han eliminado masivamente.

En la mayoría de los casos, identificamos los Valores duplicados en una columna y, a continuación, eliminamos los Fila completa según los Valores duplicados. Para ser honestos, esta operación es muy similar a eliminar Valores duplicados de una única columna. Por lo tanto, podemos utilizar soluciones similares para Eliminar filas basándonos en duplicados de la columna especificada.

El primer método consiste en aplicar la función integrada Eliminar duplicados para eliminar filas según los duplicados de la columna especificada. Simplemente seleccione el rango del que desea eliminar filas, haga clic en Datos > Eliminar duplicados para activar la función, marque únicamente la columna especificada en el cuadro de diálogo Eliminar duplicados y, a continuación, haga clic en Aceptar para finalizar la operación de eliminación.

También podemos usar el formato condicional combinado con el filtro para eliminar filas según los valores duplicados en la columna especificada. En primer lugar, resalte el rango de filas según los valores duplicados en una columna determinada mediante una regla de formato condicional ()haga clic para ver cómo). A continuación, filtre el rango por color. Luego, elimine fácilmente todas las filas filtradas. Por último, quite o desactive el filtro y comprobará que solo permanecen las filas con valores únicos en la columna indicada.

Alternativamente, puede añadir una columna auxiliar y aplicar la fórmula =COUNTIF($C$3:$C$21,C3) para marcar los duplicados en la columna especificada. A continuación, filtre los valores mayores que 1 en la columna auxiliar y elimine fácilmente todas las filas filtradas. Al quitar el filtro, verá que solo quedan filas con valores únicos en la columna indicada.

El complemento de terceros Kutools para Excel también ofrece una función extraordinariamente útil: Seleccionar Celdas Duplicadas/Únicas. Con ella, podrás seleccionar rápidamente filas según los valores duplicados en la columna especificada y, a continuación, eliminar fácilmente esas filas seleccionadas mediante el menú contextual.

La función de Kutools para Excel Combinar filas avanzado también permite eliminar rápidamente filas en función de valores duplicados en la columna clave especificada.

Podemos añadir una columna auxiliar y aplicar una fórmula para identificar los Valores duplicados entre dos columnas, y luego filtrar y eliminar fácilmente los Valores duplicados.

1. Añada una columna en blanco junto a la columna especificada de la que va a eliminar valores duplicados.

2. En la primera celda de la columna auxiliar (excluyendo la celda de encabezado), introduce la fórmula =IF(ISERROR(MATCH(C2,$A$2:$A$13,0)),«Unique»,«Duplicate») y, a continuación, arrastra el controlador de autorrelleno hacia abajo para copiar la fórmula en las demás celdas.

Notas: En la fórmula anterior,
(1) C2 es la primera celda de la columna especificada de la que va a eliminar valores duplicados;
(2) $A$2:$A$13 es la otra columna con la que debe comparar.
(3) Esta fórmula devolverá Duplicado si el valor correspondiente está duplicado respecto a los valores de la otra columna, y devolverá Único si es diferente de los valores de esa columna.

3. Seleccione la columna auxiliar y haga clic en Datos > Filtro.

4. Haga clic en el icono de filtro del encabezado de la columna auxiliar, active únicamente la opción Duplicado en el menú desplegable y, a continuación, haga clic en el botón Aceptar.

5. Ahora todos los valores duplicados están filtrados. Seleccione las celdas filtradas, haga clic con el botón derecho y elija Eliminar fila en el menú contextual. A continuación, haga clic en Aceptar en el cuadro de diálogo de confirmación emergente.

6. Entonces, todos los valores duplicados se eliminan de la columna especificada. Haga clic nuevamente en Datos > Filtro para cancelar el filtro.

Entonces verá que solo quedan valores únicos en la columna especificada. Puede eliminar la columna auxiliar si lo desea.

Notas: Este método eliminará filas completas en función de los valores duplicados en la columna especificada.

Si tiene instalado Kutools para Excel, puede utilizar su increíble función Seleccionar celdas iguales/diferentes para seleccionar rápidamente los valores duplicados entre dos columnas y eliminarlos fácilmente.

1. Haga clic en Kutools > Seleccionar > Seleccionar celdas iguales/diferentes para activar esta función.

2. En el cuadro de diálogo Seleccionar celdas iguales/diferentes, especifique ambas columnas en los campos Buscar valores en y Según, active las opciones Celda única y Valores iguales, y haga clic en el botón Aceptar. Vea la captura de pantalla:

3. A continuación, todos los valores duplicados de las dos columnas se seleccionan en la primera columna (la que especificó en el campo)Buscar valor en). Haga clic en el botón Aceptar del cuadro de diálogo emergente.

4. Puede pulsar la tecla Supr para eliminar directamente estos valores duplicados o hacer clic con el botón derecho sobre ellos y seleccionar Eliminar en el menú contextual.