Duplicaten zoeken, markeren, filteren, tellen en verwijderen in Excel

Wanneer u dubbele cellen zoekt in slechts één kolom of lijst, kunt u de AANTAL.ALS-functie gebruiken om snel dubbele gegevens te vinden en te tellen.
1. Selecteer een lege cel naast de kolom waarin u dubbele waarden wilt zoeken.

2. Typ de formule =AANTAL.ALS($C$3:$C$12;C3)>1 en druk op de Enter-toets.

3. Sleep de automatisch aanvullen-greep van de formulecel om de formule naar andere cellen te kopiëren.

Opmerkingen: In de formule =COUNTIF($C$3:$C$12, C3)>1,
(1) $C$3:$C$12 is de kolom of lijst waarin u dubbele waarden zult vinden. Omdat deze kolom statisch moet blijven wanneer u de formule naar andere cellen kopieert, wordt er doorgaans een absolute verwijzing met ‘$’ gebruikt.
(2) C3 is de eerste cel van de opgegeven kolom. Het betreft een relatieve verwijzing, omdat deze automatisch moet aanpassen wanneer u de formule naar andere cellen kopieert.
(3) Deze formule retourneert WAAR of ONWAAR. WAAR betekent dat de bijbehorende waarde een duplicaat is, terwijl ONWAAR aangeeft dat de waarde uniek is in de kolom.
(4) Soms zijn WAAR en ONWAAR niet direct intuïtief te begrijpen. U kunt de oorspronkelijke formule combineren met de ALS-functie om direct Duplicaten te tonen:
=IF(COUNTIF($C$3:$C$12, C3)>1,"Duplicates",„")

In sommige gevallen moet u twee kolommen vergelijken om dubbele waarden te identificeren. Stel bijvoorbeeld dat u twee naamlijsten hebt en u de dubbele namen in de tweede lijst wilt vinden ten opzichte van de eerste. Daarvoor kunt u eenvoudig de functies VERT.ZOEKEN en ALS.FOUT gebruiken.

1. Selecteer een lege cel naast de tweede namenlijst.

2. Voer de formule =IFERROR(VLOOKUP(D3,$B$3:$B$18,1,0),„") in en druk op de Enter-toets.

3. Sleep de automatische invulgreep van de formulecel om de formule naar andere cellen te kopiëren—precies zoals u wilt.

Opmerkingen: In bovenstaande formule:
(1) D3 is de eerste cel in de tweede namenlijst. De verwijzing moet automatisch meeveranderen wanneer we de formule naar andere cellen kopiëren; daarom is deze relatief.
(2) $B$3:$B$18 is de voornaamlijst. Het betreft een absolute verwijzing, omdat het bereik ongewijzigd moet blijven wanneer we de formule naar andere cellen kopiëren.
(3) Wanneer een naam overeenkomt met namen in de eerste lijst, retourneert de formule die naam; anders retourneert deze een lege waarde.
(4) U kunt ook de formule =IF(COUNTIF($B$3:$B$18,D3)>0,"Duplicates",„") gebruiken om dubbele namen in de tweede lijst te identificeren ten opzichte van de eerste lijst. Deze formule retourneert „Duplicaten“ als de betreffende naam een duplicaat is.

(5) Als u twee kolommen op verschillende werkbladen wilt vergelijken op duplicaten, hoeft u alleen de bladnaam toe te voegen vóór de kolomverwijzing waarmee u vergelijkt. In ons voorbeeld wijzigt u $B$3:$B$18 in Blad1!$B$3:$B$18 in de formule.

De hierboven beschreven formules zijn niet hoofdlettergevoelig bij het zoeken naar dubbele waarden, waardoor „apple“ als duplicaat wordt beschouwd van „APPLE“. U kunt een matrixformule gebruiken om dubbele waarden in één kolom te vinden met behoud van hoofdlettergevoeligheid.

1. Selecteer een lege cel naast de kolom waarin u dubbele waarden wilt zoeken.

2. Typ de matrixformule =IF(SUM((--EXACT($C$3:$C$12,C3)))<=1,"",„Duplicate") en druk op Ctrl+Shift+Enter.

3. Sleep de automatische invulgreep van de formulecel om de matrixformule naar andere cellen te kopiëren.

Opmerkingen: In bovenstaande matrixformule:
(1) $C$3:$C$12 is de kolom waarin u dubbele waarden moet vinden. De verwijzing is absoluut, omdat deze statisch blijft wanneer u de matrixformule naar andere cellen kopieert.
(2) C4 is de eerste cel in de kolom. De verwijzing is relatief, zodat deze automatisch aanpast wanneer u de matrixformule naar andere cellen kopieert.
(3) Als de bijbehorende cel een duplicaat is, retourneert de matrixformule „Duplicate“; anders retourneert deze een lege waarde.

U kunt de functie Voorwaardelijke opmaak gebruiken om duplicaatcellen snel te markeren in een kolom of een bereik.

1. Selecteer de kolom waarin u dubbele cellen wilt markeren.

2. Klik op Start > Voorwaardelijke opmaak gebruiken > Cellen markeren op basis van regels > Dubbele waarden. Zie de schermafbeelding hieronder:

3. Selecteer in het verschijnende Dubbele waarden-dialoogvenster Duplicaat in de eerste keuzelijst, kies een markeerscenario in de tweede keuzelijst en klik op de knop OK.

Opmerkingen: Als de vooraf ingestelde markeerscenario’s niet aan uw behoeften voldoen, kunt u Aangepast formaat selecteren in de tweede keuzelijst en vervolgens de markeerkleur, het lettertype en de celranden instellen zoals gewenst in het verschijnende dialoogvenster Celopmaak instellen.

Vervolgens ziet u dat alle duplicaatcellen in de geselecteerde kolom zijn gemarkeerd, zoals in de onderstaande schermafbeelding wordt weergegeven.

Opmerkingen:
(1) Nadat de duplicaatcellen zijn gemarkeerd, kunt u deze eenvoudig filteren. ()Klik om te zien hoe)
(2) Nadat de duplicaatcellen zijn gemarkeerd, kunt u de duplicaten ook eenvoudig in bulk verwijderen. ()Klik om te zien hoe)

Sommige gebruikers geven er de voorkeur aan om Gemarkeerd rijbereik op basis van duplicaatcellen in een bepaalde kolom. In dit geval kunnen we een aangepaste Voorwaardelijke opmaak gebruiken-regel maken om dit te realiseren.

1. Selecteer het bereik (exclusief de koprij) waarop u Gemarkeerd rijbereik op basis van duplicaatcellen wilt toepassen.

2. Klik op Start > Voorwaardelijke opmaak gebruiken > Nieuwe regel.

3. In het dialoogvenster Nieuwe opmaakregel:
(1) selecteert u Gebruik een formule om te bepalen welke cellen moeten worden opgemaakt;
(2) typt u de formule =COUNTIF($C$3:$C$12,$C3)>1 in het vak Opmaakwaarden waarvoor deze formule WAAR is;
Tips: In de formule is $C$3:$C$12 de kolom met duplicaatcellen en is $C3 de eerste cel in de kolom.
(3) Klikt u op de knop Opmaak.

4. Geef in het dialoogvenster Celopmaak instellen de gewenste vulkleur, het lettertype en de celranden op, en klik achtereenvolgens op de knoppen OK om de instellingen op te slaan.

Tot nu toe worden in het Bereik selecteren rijen gemarkeerd op basis van duplicaatcellen in de opgegeven kolom. Zie schermafbeelding:

Om dubbele rijen in een bepaald bereik te markeren, kunt u ook de functie Voorwaardelijke opmaak hiervoor gebruiken.

1. Selecteer het bereik, exclusief de koprij.

2. Klik op Start > Voorwaardelijke opmaak gebruiken > Nieuwe regel.

3. In het dialoogvenster Nieuwe opmaakregel:
(1) Selecteer de optie Een formule gebruiken om te bepalen welke cellen moeten worden opgemaakt;
(2) Voer in het vak Opmaakwaarden waarvoor deze formule WAAR is de formule =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1 in;
(3) Klik op de knop Opmaak.

Opmerkingen: In de formule =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1:
(1)$B$3:$B$12 is de eerste kolom in het bereik en $B3 is de eerste cel in deze kolom;
(2)$C$3:$C$12 is de tweede kolom in het bereik en $C3 is de eerste cel in deze kolom;
(3)$D$3:$D$12 is de derde kolom in het bereik en $D3 is de eerste cel in deze kolom;
(4) Als uw bereik meer kolommen bevat, kunt u achtereenvolgens de kolomverwijzingen en de bijbehorende eerste cellen aan de formule toevoegen.

4. Stel in het dialoogvenster Celopmaak de markeringskleur, het lettertype, celranden enzovoort in zoals u dat wilt, en klik daarna achtereenvolgens op de knoppen OK om de instelling op te slaan.

Tot zover zijn de Dubbele rij geïdentificeerd en gemarkeerd in de Bereik selecteren. Zie schermafbeelding:

U hebt waarschijnlijk opgemerkt dat alle duplicaten met de bovenstaande methoden worden geïdentificeerd of gemarkeerd. Soms wilt u echter alleen zien welke items gedupliceerd zijn, exclusief het eerste exemplaar. Dat kunt u ook realiseren met de functie Voorwaardelijke opmaak gebruiken en een andere formule.

1. Selecteer de kolom, inclusief de koptekst.
Tip: Als u ‘Gemarkeerd rijbereik’ wilt toepassen op basis van duplicaten in één kolom – met uitzondering van de eerste voorkomens – selecteert u het bereik zonder de koptekstrij.

2. Klik op Start > Voorwaardelijke opmaak gebruiken > Nieuwe regel.

3. In het dialoogvenster Nieuwe opmaakregel:
(1) Klik om de optie Een formule gebruiken om te bepalen welke cellen moeten worden opgemaakt te selecteren;
(2) Typ in het vak Opmaakwaarden waarvoor deze formule WAAR is de formule =COUNTIF($C$3:$C3, C3)>1;
Tips: Om een gemarkeerd rijbereik toe te passen op basis van duplicaten in één kolom, typt u de formule =COUNTIF($C$3:$C3, $C3)>1.
(3) Klik op de knop Opmaak.

4. Stel in het verschijnende dialoogvenster Celopmaak instellen de markeringskleur, het lettertype, de celranden enzovoort in zoals u wilt, en klik vervolgens op de knop OK om de instellingen op te slaan.

Vervolgens ziet u dat de duplicaatcellen, met uitzondering van de eerste exemplaren in de geselecteerde kolom (of rijen op basis van duplicaten in de opgegeven kolom), worden gemarkeerd. Zie schermafbeelding:

Wanneer u duplicaatcellen of -rijen markeert met de functie Voorwaardelijke opmaak, worden alle duplicaten in dezelfde kleur weergegeven. Maar als elke reeks dubbele waarden een unieke kleur krijgt, is het veel eenvoudiger om de verschillen te herkennen en te lezen. Daarvoor kunt u VBA gebruiken om dit in Excel te realiseren.

1. Druk tegelijkertijd op de toetsen Alt + F11 om het venster Microsoft Visual Basic for Applications te openen.

2. Klik in het venster op Invoegen > Module en plak vervolgens onderstaande code in het nieuwe modulevenster.

VBA: Markeer duplicaatcellen in verschillende kleuren:

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. Druk op de toets F5 of klik op het pictogram Uitvoeren om deze VBA uit te voeren.

4. Selecteer in het verschijnende dialoogvenster van Kutools voor Excel de kolom waarin u duplicaatcellen met verschillende kleuren wilt markeren en klik op de knop OK.

Elke reeks Dubbele waarden wordt nu gemarkeerd met een afwijkende kleur.

De eerste tool die ik ga introduceren, is de functie Selecteer duplicaat/unieke cellen, aangeboden door Kutools voor Excel. Met deze functie vindt u in een handomdraai unieke of dubbele cellen.

1. Selecteer de kolom of het bereik waarin u dubbele cellen wilt vinden en markeren.

2. Klik op Kutools > Selecteren > Selecteer duplicaat/unieke cellen.

3. Schakel in het dialoogvenster Selecteer duplicaat/unieke cellen de gewenste opties in en klik op de knop Ok om de bewerking af te ronden.

Opmerkingen: In het dialoogvenster Selecteer duplicaat/unieke cellen:
(1) Als u alle duplicaten wilt selecteren of markeren, met uitzondering van de eerste exemplaren, vinkt u de optie Duplicaatwaarden (Eerste overeenkomst uitsluiten) aan. Schakel anders de optie Duplicaatwaarden (Eerste overeenkomst opnemen) in.
(2) Als u de duplicaten wilt markeren, vinkt u de optie Vul achtergrondkleur aan en kiest u een markeringskleur naar wens.
(3) Als u het gehele rijbereik wilt selecteren of markeren op basis van duplicaten in de geselecteerde kolom, vinkt u de optie Selecteer Gehele Rij aan.
(4) Als u dubbele waarden hoofdlettergevoelig wilt selecteren of markeren, vinkt u de optie Hoofdlettergevoelig aan.

Kutools voor Excel biedt ook een geweldige tool – Cellen vergelijken – om duplicaatcellen in twee kolommen eenvoudig te vinden en markeren.

1. Klik op Kutools > Cellen vergelijken om het dialoogvenster Selecteer Dezelfde/Verschillende Cellen te openen.

2. Geef in het dialoogvenster Selecteer Dezelfde/Verschillende Cellen de twee kolommen op in de vakken Waarden zoeken in en Volgens, schakel de optie Gelijke waarden in en vink andere opties naar wens aan.

Opmerkingen:
(1) Als u Dubbele rij wilt zoeken, vinkt u de optie Elke rijaan; en om dubbele cellen te vinden, vinkt u de optie Enkele celaan in het gedeelte Gebaseerd op;
(2) Vink de optie Vul achtergrondkleuraan en geef een markeerkleur op als u de Dubbele rij of cellen wilt markeren;
(3) Vink de optie Selecteer Gehele Rijaan als u de Gehele rij op basis van de duplicaten wilt selecteren of markeren;
(4) Vink de optie Hoofdlettergevoeligaan als u hoofdlettergevoelige duplicaten wilt zoeken of markeren.

3. Klik achtereenvolgens op de knoppen OK om de instellingen af te ronden.

Vervolgens ziet u dat de duplicaten in de kolom die u hebt opgegeven in het vak Waarden zoeken in zijn geïdentificeerd en gemarkeerd.

Met deze methode identificeert en markeert u dubbele cellen met behulp van een Voorwaardelijke opmaak gebruiken-regel en filtert u vervolgens eenvoudig op de markeerkleur in Excel.

1. Gebruik Voorwaardelijke opmaak om duplicaten in de opgegeven kolom te vinden en te markeren. ()Klik hier om te zien hoe)

2. Klik op de koptekst om kolommen te selecteren van de opgegeven kolom en klik vervolgens op Gegevens > Filter.

3. Klik vervolgens op het filterpictogram in de kolomkop, selecteer Filteren op kleur en kies daarna de opgegeven kleur van de Voorwaardelijke opmaak in de keuzelijst. Zie schermafbeelding:

Vervolgens ziet u dat alleen de rijen met dubbele cellen worden gefilterd. Zie schermafbeelding:

U kunt ook een Dubbele waarden markeren gebruiken met een formule in een hulpkolom en vervolgens eenvoudig duplicaten filteren met die hulpkolom in Excel.

1. Voeg naast de oorspronkelijke gegevens een hulpkolom toe en typ Dubbel als kolomkop.

2. Selecteer de eerste lege cel onder de kolomkop, voer de formule =IF(COUNTIF($C$3:$C$12,C3)>1,"Duplicate",„") in en sleep de automatisch-aanvulgreep van deze cel om de formule naar andere cellen te kopiëren.

Opmerkingen: In de bovenstaande formule is $C$3:$C$12 de kolom met dubbele gegevens en is C3 de eerste cel (exclusief de koptekstcel) in die kolom.

3. Klik op de koptekst – Dubbels – en klik op Gegevens > Filter.

4. Klik daarna op het filterpictogram in de kolomkop, vink alleen Dubbel aan en klik op OK. Zie de schermafbeelding:

Tot nu toe zijn alleen rijen met Dubbele waarden gefilterd. Zie schermafbeelding:

Over het algemeen kunt u de functie =AANTAL.ALS(bereik; criterium) gebruiken om te tellen hoe vaak een bepaalde waarde voorkomt binnen een beperkt bereik. Bijvoorbeeld: om te tellen hoe vaak „Apple” voorkomt in het bereik A2:A10, gebruikt u de formule =AANTAL.ALS(A2:A10; „Apple") om het aantal keren te bepalen dat deze waarde voorkomt.

De formule =AANTAL.ALS(bereik; criterium) telt echter alleen de opgegeven dubbele waarde. Hoe tel je dubbele waarden met twee of meer criteria? En hoe tel je hoofdlettergevoelige duplicaten met criteria? De onderstaande methoden helpen u deze problemen op te lossen.

We kunnen een matrixformule gebruiken om hoofdlettergevoelige Dubbele waarden met criteria te tellen in Excel. Bijvoorbeeld: om te tellen hoe vaak de waarde „Apple” voorkomt in de lijst B2:B21 met Hoofdlettergevoelig, gaat u als volgt te werk:

1. Selecteer een lege cel.

2. Voer de formule =SOM(--EXACT(B2:B20;E2)) in.

3. Druk op Ctrl + Shift + Enter om het telresultaat te krijgen.

Opmerkingen: In de matrixformule is
(1)B2:B20de kolom waarin u duplicaten telt.
(2)E2de cel met de opgegeven waarde waarvan u het aantal voorkomens wilt tellen. U kunt de celverwijzing vervangen door de waarde tussen aanhalingstekens, bijvoorbeeld „Apple".

Soms wilt u duplicaten tellen op basis van twee of meer criteria. Daarvoor kunt u de functie AANTALLEN.ALS gebruiken.
Bijvoorbeeld: er is een fruitverkooptabel zoals hieronder weergegeven. U moet het aantal keren tellen dat ‘appel’ is verkocht op 7/5/2020 én waarbij het verkoopbedrag hoger is dan 300. Zo telt u de duplicaten die aan deze criteria voldoen:

1. Selecteer een lege cel.

2. Voer de formule =COUNTIFS(B3:B20,G4,C3:C20,G3,D3:D20,„>300") in.

3. Druk op de Enter-toets om het telresultaat te krijgen.

Opmerkingen: In de bovenstaande formule is
(1)B3:B20 de (eerste) datumkolom en G4 het datumcriterium;
(2)C3:C20 de (tweede) fruitkolom en G3 het fruitcriterium;
(3)D3:D20 de (derde) bedragkolom en „>300" het bedragcriterium.
(4) Als uw tabel meer kolommen en criteria bevat, kunt u extra kolomverwijzingen en criteria toevoegen.

Om alle duplicaten in een kolom te tellen, met uitzondering van de eerste voorkomens, gaat u als volgt te werk:

1. Selecteer een lege cel naast de kolom.

2. Voer de formule =IF(COUNTIF($B$3:B3,B3)>1,"YES",„") in en sleep de automatisch aanvullen-greep naar beneden om de formule naar andere cellen te kopiëren.

Opmerkingen: In de bovenstaande formule is
(1)$B$3:B3 het bereik waarin duplicaten worden geteld. In $B$3:B3 wordt B3 automatisch aangepast wanneer u de formule naar andere cellen kopieert.
(2)B3 de eerste cel in de opgegeven kolom.
(3) Deze formule retourneert JA of een lege waarde: JA betekent dat de betreffende waarde een duplicaat is, terwijl een lege waarde aangeeft dat deze uniek is.

Vervolgens zijn alle duplicaten in de opgegeven kolom geïdentificeerd. We kunnen de formuleresultaten tellen om het totale aantal duplicaten te krijgen.

3. Selecteer een lege cel.

4. Voer de formule =COUNTIF(C3:C16,„YES") in en druk op de toets Enter.

Opmerkingen: In de bovenstaande formule:
(1) C3:C16 is het bereik waarop we in de vorige stap de formule voor het markeren van dubbele waarden hebben toegepast.
(2) JA is de waarde die door de vorige formule wordt geretourneerd.

Vervolgens krijgen we het totale aantal Dubbele waarden binnen de opgegeven kolom. Het totale aantal duplicaten omvat de eerste voorkomens niet.

Om het aantal duplicaten inclusief de eerste voorkomens in Excel te tellen, kunt u een matrixformule toepassen om dit te bereiken.

1. Selecteer een lege cel.

2. Voer de formule in: =ROWS(B3:B16)-SUM(IF(COUNTIF(B3:B16,B3:B16)=1,1,0)).

3. Druk op Ctrl+Shift+Enter om het telresultaat te retourneren.

Opmerkingen: In de bovenstaande formule is B3:B16 de opgegeven kolom waarin we duplicaten, inclusief de eerste voorkomens, willen tellen.

Om uw werk te vereenvoudigen en uzelf te ontlasten van het onthouden van lange, saaie formules, kunt u de functie Selecteer duplicaat/unieke cellen van Kutools voor Excel gebruiken om snel het aantal dubbele waarden in de opgegeven lijst of kolom te tellen—exclusief of inclusief de eerste voorkomens.

1. Selecteer de kolom waarin u het aantal dubbele waarden wilt tellen en klik op Kutools > Selecteren > Dubbele/Unieke cellen selecteren.

2. In het dialoogvenster Selecteer duplicaat/unieke cellen vinkt u, naar behoefte, de optie Duplicaatwaarden (Eerste overeenkomst uitsluiten) of Duplicaatwaarden (Eerste overeenkomst opnemen) aan en klikt u op de knop Ok.

3. Vervolgens worden alle dubbele waarden – inclusief of exclusief de eerste voorkomens – geselecteerd, en verschijnt er tegelijkertijd een dialoogvenster met het aantal geselecteerde cellen. Zie de bovenstaande schermafbeelding.

Stel dat u twee Namenlijst wilt vergelijken en het aantal duplicaten daartussen wilt tellen. Hoe kunt u dit probleem snel oplossen? We kunnen dit ook met een formule in Excel doen.

1. Selecteer een lege cel.

2. Voer de formule =SUMPRODUCT(--(ISNUMBER(MATCH(B3:B12,D3:D18,0)))) in.

3. Druk op de Enter-toets.

Opmerkingen: In de bovenstaande formule:
(1) B3:B12 is de eerste kolom met namen waarin u duplicaten wilt tellen.
(2) D3:D18 is de tweede kolom met namen waarmee u duplicaten wilt vergelijken.

Als alternatief kunnen we een add-in van derden gebruiken: Kutools voor Excel, waarmee we eenvoudig het totale aantal duplicaatcellen tussen twee kolommen kunnen tellen.

1. Klik op Kutools > Selecteren > Selecteer Dezelfde/Verschillende Cellen.

2. In het dialoogvenster Selecteer dezelfde & verschillende cellen:
(1) Geef de twee kolommen op in de vakken Zoek waarden in en Volgens.
(2) Vink de optie Enkele cel aan.
(3) Vink de optie Dezelfde waarden aan.

4. Klik op de knop Ok.

Vervolgens worden alle duplicaatcellen in de eerste kolom geselecteerd en verschijnt tegelijkertijd een dialoogvenster dat laat zien hoeveel duplicaatcellen zijn geselecteerd. Zie schermafbeelding:

Opmerkingen: Deze functie telt het totale aantal dubbele waarden in de kolom die u hebt opgegeven in het vak Zoek waarden in in het dialoogvenster Selecteer Dezelfde/Verschillende Cellen. Als u het totale aantal dubbele waarden in de tweede kolom wilt tellen, past u de functie Selecteer Dezelfde/Verschillende Cellen opnieuw toe en geeft u daarbij de tweede kolom op in het vak Zoek waarden in.

U kunt elk duplicaatwaarde één keer snel tellen met een formule als volgt:

1. Selecteer een lege cel.

2. Voer de formule in: =SUMPRODUCT((C3:C19<>„")/COUNTIF(C3:C19,C3:C19&„")).

3. Druk op de Enter-toets om het telresultaat te verkrijgen.

Opmerkingen: In de bovenstaande formule is C3:C19 de opgegeven kolom waarin u elke duplicaatwaarde één keer wilt tellen.

Bij het tellen van een lijst kunt u elk duplicaatwaarde één keer tellen met Hoofdlettergevoelig door een matrixformule toe te passen in Excel.

1. Selecteer een lege cel.

2. Voer de matrixformule in:=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. Druk tegelijkertijd op Ctrl+Shift+Enter om het telresultaat te retourneren.

Opmerkingen: In de bovenstaande matrixformule is C3:C19 de opgegeven kolom waarin u elke reeks duplicaten één keer telt, hoofdlettergevoelig.

Als u Kutools voor Excel hebt geïnstalleerd, kunt u ook de functie Cellen met unieke waarden tellen gebruiken om elke reeks dubbele waarden in Excel snel één keer te tellen.

1. Selecteer een lege cel.

2. Klik op Kutools > Formulehulp > Statistisch > Tel het aantal unieke waarden in een bereik (inclusief de eerste duplicaat).

3. Geef in het dialoogvenster Formulehulp in het vak Bereik de kolom op waarin u duplicaten één keer wilt tellen en klik op de knop Ok.

Vervolgens wordt het telresultaat direct in de geselecteerde cel ingevuld.

We kunnen de functie Subtotaal gebruiken om elke reeks dubbele waarden in een kolom in Excel te tellen.

1. Selecteer de kolom waarin u elke reeks dubbele waarden wilt tellen en klik op Gegevens > Oplopend of Sorteer Z naar A.

2. In het opduikende sorteervoorwaarschuwingsvenster vinkt u de optie Selectie uitbreiden aan en klikt u op de knop Sorteren.

Vervolgens ziet u dat de selectie is gesorteerd op basis van de Dubbele waarden van de opgegeven kolom.

3. Houd de selectie geselecteerd en klik op Gegevens > Subtotaal.

4. In het Subtotaal-venster:
(1) Selecteer de opgegeven kolom in de keuzelijst Bij wijziging in;
(2) Selecteer Aantal in de keuzelijst Functie gebruiken;
(3) Vink alleen de opgegeven kolom aan in het vak Subtotaal toevoegen aan;
(4) Klik op de knop OK.

Vervolgens ziet u dat elke reeks dubbele waarden is geteld en dat het telresultaat direct onder elke reeks dubbele waarden is geplaatst – zie de bovenstaande schermafbeelding.

We kunnen ook een Draaitabel maken om eenvoudig en snel elke reeks dubbele waarden in Excel te tellen.

1. Selecteer het bereik met de opgegeven kolom en klik op Invoegen > PivotTable.

2. Geef in het venster Draaitabel maken op waar u de nieuwe draaitabel wilt plaatsen en klik op de OK-knop.

3. Sleep in het deelvenster Draaitabelvelden de opgegeven kolom naar zowel de secties Rijen als Waarden. Vervolgens ziet u dat elke reeks dubbele waarden in de opgegeven kolom in één keer is geteld. Zie schermafbeelding:

Als u al Kutools voor Excel hebt geïnstalleerd, kunt u de gebruiksvriendelijke functie Geavanceerd samenvoegen van rijen gebruiken om snel elke reeks dubbele waarden in de opgegeven kolom te tellen.

Opmerkingen: De functie Geavanceerde rijen combineren past het geselecteerde bereik aan en verwijdert rijen op basis van dubbele waarden in de opgegeven sleutelkolom. Om uw gegevens te behouden, raden we u aan eerst een back-up te maken of uw gegevens elders te kopiëren vóór de onderstaande bewerkingen.

1. Voeg direct rechts van het oorspronkelijke gegevensbereik een lege kolom toe en noem deze nieuwe kolom Aantal.

2. Selecteer tegelijkertijd het oorspronkelijke gegevensbereik en de nieuwe kolom, en klik op Kutools > Samenvoegen en splitsen > Geavanceerd samenvoegen van rijen.

3. In het venster Geavanceerd samenvoegen van rijen:
(1) Selecteer de gewenste kolom waarvan u elke reeks dubbele waarden wilt tellen en klik vervolgens op Primaire sleutel.
(2) Selecteer de nieuwe kolom (Aantal) en klik daarna op Berekenen > Aantal.
(3) Geef indien nodig combinatie- of berekeningstypen op voor andere kolommen.
(4) Klik op de Ok-knop.

Vervolgens ziet u dat elke reeks dubbele waarden in de opgegeven kolom in één keer is geteld. Zie schermafbeelding:

Stel dat u een lijst met fruitsoorten in een kolom heeft, waarin sommige soorten meerdere keren voorkomen. U wilt elk dubbele voorkomen van een fruitsoort markeren — in de volgorde waarin het verschijnt. Hoe pakt u dat aan? In dit gedeelte wordt een eenvoudige formule beschreven waarmee u dit direct in Excel kunt realiseren.

1. Voeg direct rechts van de oorspronkelijke gegevens een lege kolom toe.

2. Voer de formule =IF(COUNTIF($C$3:$C$14,C3)>1,COUNTIF(C$3:C3,C3),„") in de eerste cel van de toegevoegde lege kolom in.

3. Sleep de AutoAanvulhendel van deze formulecel om de formule naar andere cellen te kopiëren.

Opmerkingen: In bovenstaande formule:
(1) $C$3:$C$14 is de opgegeven kolom waarin u dubbele waarden in volgorde wilt tellen.
(2) C3 is de eerste cel in de opgegeven kolom.
(3) Als de bijbehorende waarde een duplicaat is, retourneert deze formule reeksnummers 1, 2, 3… op basis van de volgorde van verschijnen; als de waarde uniek is, retourneert de formule een lege cel.

U kunt de functie Dubbele items verwijderen gebruiken om alle dubbele waarden direct te verwijderen, behalve het eerste voorkomen.

1. Selecteer de kolom waarin u alle dubbele waarden wilt verwijderen, behalve het eerste voorkomen.

2. Klik op Gegevens > Dubbele items verwijderen.

3. Vink in het waarschuwingsvenster Dubbele items verwijderen de optie Doorgaan met de huidige selectie aan en klik op de knop Dubbele items verwijderen.

Tips: Om rijen te verwijderen op basis van dubbele waarden in de selectie, vinkt u de optie Selectie uitbreiden aan.

4. Vink in het venster Dubbele items verwijderen alleen de opgegeven kolom aan en klik op de OK-knop.

Tips: Als u in de vorige stap de optie Selectie uitbreiden hebt aangevinkt, worden hier alle kolommen weergegeven. Vink echter alleen de opgegeven kolom aan.

5. Vervolgens verschijnt een dialoogvenster met het aantal verwijderde dubbele waarden. Klik op de OK-knop om het te sluiten.

U kunt ook de functie Geavanceerd filteren gebruiken om eenvoudig alle dubbele waarden uit de opgegeven kolom te verwijderen.

1. Klik op Gegevens > Geavanceerd.

2. In het venster Geavanceerd filteren:
(1) Vink de optie Kopiëren naar een andere locatie aan;
(2) Selecteer in het vak Lijstbereik de kolom waaruit u dubbele waarden wilt verwijderen;
(3) Geef in het vak Kopiëren naar het bereik op waar u de kolom naartoe wilt kopiëren;
(4) Vink de optie Alleen unieke records aan.
(5) Klik op de OK-knop.

Vervolgens ziet u dat de opgegeven kolom naar de Beperkt bereik is gekopieerd met alle Dubbele waarden verwijderd, behalve de eerste voorkomens. Zie schermafbeelding:

U kunt ook een VBA-macro gebruiken om snel Dubbele waarden te verwijderen, behalve de eerste voorkomens, uit een kolom in Excel.

1. Druk op Alt+F11 om het Microsoft Visual Basic for Applications-venster te openen.

2. Klik op Invoegen > Module en plak de onderstaande VBA-code in het nieuwe modulevenster.

VBA: Dubbele waarden verwijderen, behalve de eerste voorkomens

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. Druk op F5 of klik op de knop Uitvoeren om deze VBA uit te voeren.

4. Geef in het verschijnende dialoogvenster het bereik op waaruit u dubbele waarden wilt verwijderen en klik op de knop OK.

Alle Dubbele waarden, behalve de eerste voorkomens, worden onmiddellijk verwijderd uit de Beperkt bereik.

Opmerking: Deze VBA-code is Hoofdlettergevoelig.

We kunnen alle Dubbele waarden, inclusief de eerste voorkomens, markeren in een kolom of lijst met een Voorwaardelijke opmaak gebruiken-regel en vervolgens alle Dubbele waarden filteren op basis van de markeerkleur. Daarna kunnen we alle gefilterde dubbele cellen selecteren en deze in één keer verwijderen.

1. Gebruik voorwaardelijke opmaak om dubbele waarden te markeren. ()Klik hier voor instructies)

2. Selecteer de kolom waaruit u dubbele waarden (inclusief de eerste voorkomens) wilt verwijderen en klik op Gegevens > Filter.

3. Klik op het filterpictogram in de kolomkop van de betreffende kolom. Selecteer in het vervolgmenu Filteren op kleur en kies daarna de markeerkleur in het submenu.

Vervolgens worden alle Dubbele waarden gefilterd.

4. Selecteer alle gefilterde cellen, klik met de rechtermuisknop en kies Rij verwijderen in het contextmenu. Klik in het bevestigingsvenster op de knop OK om door te gaan.

5. Vervolgens worden alle dubbele waarden in één keer verwijderd. Houd de gefilterde lijst geselecteerd en klik opnieuw op Filter > Gegevens om het filter op te heffen.

Tot nu toe ziet u dat alle dubbele cellen, inclusief de eerste voorkomens, in één keer zijn verwijderd en alleen unieke waarden overblijven.

Opmerking: Met deze methode worden rijen verwijderd op basis van dubbele waarden in de opgegeven kolom.

We kunnen ook een formule toepassen om Dubbele waarden, inclusief de eerste voorkomens, te identificeren in een hulpkolom, vervolgens Dubbele waarden filteren op basis van de formuleresultaten en uiteindelijk deze gefilterde Dubbele waarden in één keer verwijderen.

1. Voeg een hulpkolom toe naast de opgegeven kolom, voer in de eerste cel van die hulpkolom de formule =AANTAL.ALS($B$3:$B$11;B3) in en sleep de automatisch-aanvullen-greep omlaag om de formule naar de andere cellen te kopiëren. Zie schermafbeelding:

Opmerking: In de bovenstaande formule is $B$3:$B$11 de opgegeven kolom waaruit u dubbele waarden wilt verwijderen, en is B3 de eerste cel in die kolom.

2. Selecteer de hulpkolom en klik op Gegevens > Filter.

3. Klik op het filterpictogram in de kop van de hulpkolom, vink in het vervolgmenu alle waarden aan behalve 1, en klik op de knop OK. Zie schermafbeelding:

4. Nu worden alle dubbele waarden gefilterd. Selecteer de gefilterde cellen in de hulpkolom, klik met de rechtermuisknop en kies Rij verwijderen in het contextmenu.

5. Klik in het bevestigingsvenster op de knop OK om door te gaan.

6. Alle dubbele waarden en hun bijbehorende rijen zijn nu in één keer verwijderd. Klik opnieuw op Gegevens > Filter om het filter op te heffen.

Vervolgens ziet u dat alle Dubbele waarden, inclusief de eerste voorkomens, in één keer zijn verwijderd.

Als u Kutools voor Excel hebt geïnstalleerd, kunt u ook de functie Selecteer duplicaat/unieke cellen gebruiken om eenvoudig dubbele waarden te selecteren en verwijderen in Excel—met of zonder het eerste voorkomen.

1. Selecteer de kolom waaruit u dubbele items wilt verwijderen.

2. Klik op Kutools > Selecteren > Selecteer duplicaat/unieke cellen.

3. Vink in het dialoogvenster Selecteer duplicaat/unieke cellen de optie Duplicaatwaarden (Eerste overeenkomst opnemen) aan en klik op de knop Ok.

Opmerkingen:
(1) Om dubbele waarden te verwijderen, exclusief de eerste voorkomens, vinkt u de optie Duplicaatwaarden (Eerste overeenkomst uitsluiten) aan.
(2) Om rijen te verwijderen op basis van dubbele waarden in de opgegeven kolom, vinkt u de optie Selecteer Gehele Rij aan.
(3) Om dubbele waarden hoofdlettergevoelig te verwijderen, vinkt u de optie Hoofdlettergevoelig aan.
(4) Om dubbele cellen of rijen te selecteren, markeren en verwijderen, vinkt u de optie Vul achtergrondkleur of Vul lettertypekleur aan en geeft u de gewenste opvulling of letterkleur op.

4. Vervolgens verschijnt een dialoogvenster met het aantal geselecteerde cellen. Klik op de knop OK om dit venster te sluiten.

5. Klik met de rechtermuisknop op de geselecteerde cellen en kies Verwijderen in het contextmenu.

6. Vink in het venster Verwijderen de optie Cellen naar boven verplaatsen aan en klik op de knop OK.

Tot nu toe zijn alle Dubbele waarden, inclusief de eerste voorkomens, in één keer verwijderd.

In de meeste gevallen identificeren we Dubbele waarden in een kolom en verwijderen we vervolgens de Gehele rij op basis van de Dubbele waarden. Eerlijk gezegd lijkt deze bewerking sterk op het verwijderen van Dubbele waarden uit één kolom. Daarom kunnen we vergelijkbare oplossingen gebruiken om Rijen verwijderen te verwijderen op basis van duplicaten in de opgegeven kolom.

De eerste methode is het gebruik van de ingebouwde functie Dubbele items verwijderen om rijen te verwijderen op basis van duplicaten in de opgegeven kolom. Selecteer eenvoudig het bereik waaruit u rijen wilt verwijderen, klik op Gegevens > Dubbele items verwijderen om de functie te activeren, vink alleen de opgegeven kolom aan in het dialoogvenster Dubbele items verwijderen en klik op OK om de verwijderactie af te ronden.

U kunt ook de functie Voorwaardelijke opmaak combineren met een filterom rijen met dubbele waarden in de opgegeven kolom te verwijderen. Eerst past u een voorwaardelijke opmaakregel toe om rijen met dubbele waarden in een bepaalde kolom te markeren ()klik om te bekijken hoe). Vervolgens filtert u het bereik op kleur, verwijdert u eenvoudig alle zichtbare (gefilterde) rijen en schakelt u ten slotte het filter uit of wist u het—zodat alleen rijen met unieke waarden in de opgegeven kolom overblijven.

U kunt ook een hulpkolom toevoegen en de formule =COUNTIF($C$3:$C$21;C3) toepassen om dubbele waarden in de opgegeven kolom te identificeren. Filter vervolgens alle getallen groter dan 1 in de hulpkolom en verwijder de gefilterde rijen eenvoudig. Nadat u het filter hebt gewist, ziet u dat alleen rijen met unieke waarden in de opgegeven kolom zijn overgebleven.

De add-in van derden Kutools voor Excel biedt ook een buitengewoon handige functie: Selecteer duplicaat/unieke cellen. Hiermee selecteert u razendsnel rijen op basis van dubbele waarden in de opgegeven kolom, waarna u deze geselecteerde rijen eenvoudig kunt verwijderen via het contextmenu.

Met de Geavanceerd samenvoegen van rijen-functie van Kutools voor Excel kunt u ook snel rijen verwijderen op basis van dubbele waarden in de opgegeven sleutelkolom.

We kunnen een hulpkolom toevoegen en een formule toepassen om de Dubbele waarden tussen twee kolommen te identificeren, en vervolgens de Dubbele waarden eenvoudig filteren en verwijderen.

1. Voeg een lege kolom toe naast de opgegeven kolom waaruit u dubbele waarden wilt verwijderen.

2. Typ in de eerste cel van de hulpkolom (exclusief de koptekstcel) de formule =IF(ISERROR(MATCH(C2,$A$2:$A$13,0)),"Unique",„Duplicate") en sleep vervolgens de automatisch-aanvullen-greep omlaag om de formule naar andere cellen te kopiëren.

Opmerkingen: In de bovenstaande formule is
(1) C2 de eerste cel in de opgegeven kolom waaruit u dubbele waarden wilt verwijderen;
(2) $A$2:$A$13 de andere kolom waarmee vergeleken moet worden;
(3) deze formule geeft Duplicate terug als de betreffende waarde een duplicaat is van waarden in de andere kolom, en geeft Unique terug als deze afwijkt van waarden in die kolom.

3. Selecteer de hulpkolom en klik op Gegevens > Filter.

4. Klik op het filterpictogram in de koptekst van de hulpkolom, vink vervolgens in het vervolgmenu alleen Duplicate aan en klik op de knop OK.

5. Alle dubbele waarden zijn nu gefilterd. Selecteer de gefilterde cellen, klik met de rechtermuisknop en kies Rij verwijderen in het contextmenu. Klik vervolgens op OK in het bevestigingsvenster dat verschijnt.

6. Vervolgens zijn alle dubbele waarden verwijderd uit de opgegeven kolom. Klik nogmaals op Gegevens > Filter om het filter uit te schakelen.

Vervolgens ziet u dat alleen unieke waarden zijn overgebleven in de opgegeven kolom. U kunt de hulpkolom verwijderen indien gewenst.

Opmerkingen: Met deze methode worden gehele rijen verwijderd op basis van dubbele waarden in de opgegeven kolom.

Als u Kutools voor Excel hebt geïnstalleerd, kunt u de indrukwekkende functie Selecteer Dezelfde/Verschillende Cellen gebruiken om snel dubbele waarden tussen twee kolommen te selecteren en deze eenvoudig te verwijderen.

1. Klik op Kutools > Selecteren > Selecteer Dezelfde/Verschillende Cellen om deze functie in te schakelen.

2. Geef in het dialoogvenster Selecteer Dezelfde/Verschillende Cellen beide kolommen op in de vakken Waarden zoeken in en Volgens, vink de opties Enkele cel en Gelijke waarden aan en klik op de knop Ok. Zie schermafbeelding:

3. Vervolgens worden alle dubbele waarden in de twee kolommen geselecteerd in de eerste kolom (de kolom die u hebt opgegeven in het vak)Waarde zoeken in). Klik op de knop OK in het pop-upvenster.

4. U kunt op de toets Delete drukken om deze dubbele waarden direct te verwijderen, of met de rechtermuisknop klikken en Verwijderen selecteren in het contextmenu.