Als u dubbele cellen in slechts één kolom of lijst wilt zoeken, kunt u de COUNTIF functie gebruiken om eenvoudig dubbele gegevens te vinden en te tellen.
11. Selecteer een lege cel naast de kolom waarin u duplicaten wilt zoeken.

2. Typ de formule =COUNTIF($C$3:$C$12, C3)>1 in en druk op Enter.

3. Sleep de vulgreep van de formulecel om deze 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 wilt zoeken. Omdat de kolom vast blijft wanneer u de formule naar andere cellen kopieert, is het meestal een absolute verwijzing met “$”.
(2) C3 is de eerste cel van de opgegeven kolom. Dit is een relatieve verwijzing omdat deze automatisch moet wijzigen wanneer u de formule naar andere cellen kopieert.
(3) Deze formule geeft Ja of Nee. Ja betekent dat de betreffende waarde dubbel voorkomt, terwijl Nee aangeeft dat de betreffende waarde uniek is in de kolom.
(4) Soms zijn Ja of Nee niet direct duidelijk. U kunt de oorspronkelijke formule combineren met de ALS-functie om direct Duplicaten te laten weergeven.
=IF(COUNTIF($C$3:$C$12, C3)>1,"Duplicaten","")

Soms wilt u twee kolommen vergelijken en de dubbele waarden vinden. Stel, u heeft twee lijsten met namen en u wilt de dubbele namen in de tweede lijst vinden ten opzichte van de eerste. U kunt hiervoor de VLOOKUP- en IFERROR-functie 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 Enter.

3. Sleep de vulgreep van de formulecel om de formule naar andere cellen te kopiëren zoals nodig.

Opmerkingen: In bovenstaande formule,
(1) D3 is de eerste cel in de tweede namenlijst. Deze verwijzing moet automatisch wijzigen bij het kopiëren van de formule, dus het is een relatieve verwijzing.
(2) $B$3:$B$18 is de eerste namenlijst. Dit is een absolute verwijzing omdat het bereik vast moet blijven bij het kopiëren van de formule.
(3) Wanneer een naam dubbel voorkomt met namen in de eerste lijst, geeft de formule de naam terug; anders blijft het veld leeg.
(4) U kunt ook de formule gebruiken =IF(COUNTIF($B$3:$B$18,D3)>0,"Duplicaten","") om dubbele namen in de tweede lijst te vinden ten opzichte van de eerste lijst. Deze formule geeft "Duplicaten" terug als de betreffende naam dubbel voorkomt.

(5) Als u duplicaten wilt zoeken in twee kolommen op verschillende bladen, hoeft u alleen de bladnaam toe te voegen voor de verwijzing van de te vergelijken kolom. In ons voorbeeld wijzigt u $B$3:$B$18 naar Sheet1!$B$3:$B$18 in de formule.

De hierboven besproken formules houden geen rekening met hoofdletters bij het zoeken naar duplicaten, dus "appel" wordt als dubbel gezien met "APPEL". U kunt een matrixformule gebruiken om dubbele waarden in één kolom te vinden waarbij hoofdlettergebruik wordt meegenomen.

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

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

3. Sleep de vulgreep 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 wilt zoeken. Dit is een absolute verwijzing omdat deze vast blijft bij het kopiëren van de matrixformule.
(2) C4 is de eerste cel in de kolom. Dit is een relatieve verwijzing, omdat deze automatisch moet wijzigen bij het kopiëren van de matrixformule.
(3) Als de betreffende cel dubbel is, geeft de matrixformule "Dubbel" terug, anders blijft het veld leeg.

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

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

2. Klik op Start > Voorwaardelijke opmaak > Regels voor celmarkering > Dubbele waarden. Zie onderstaande afbeelding:

3. In het pop-upvenster Dubbele waarden selecteert u Dubbel in de eerste vervolgkeuzelijst, kiest u een markeerscenario in de tweede lijst en klikt u op OK.

Opmerkingen: Als de standaard markeerscenario's niet voldoen, kunt u Aangepaste opmaak kiezen in de tweede lijst en vervolgens de markeerkleur, het lettertype en de celranden instellen in het pop-upvenster Cellen opmaken.

Nu ziet u dat alle dubbele cellen in de geselecteerde kolom zijn gemarkeerd, zoals hieronder weergegeven.

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

Sommige gebruikers willen liever rijen markeren op basis van dubbele cellen in een bepaalde kolom. In dat geval kunt u een aangepaste voorwaardelijke opmaakregel maken.

1. Selecteer het bereik (zonder de koprij) waarin u rijen wilt markeren op basis van dubbele cellen.

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

3In het dialoogvenster Nieuwe opmaakregel:
(1) Klik om te kiezen voor Een formule gebruiken om te bepalen welke cellen worden opgemaakt optie;
(2) Typ de formule =COUNTIF($C$3:$C$12,$C3)>1 in het Waarden opmaken waarvoor deze formule waar is veld;
TipsIn de formule, $C$3:$C$12 is de kolom met dubbele cellen, en $C3 is de eerste cel in de kolom.
(3) Klik op de Opmaak knop.

4. Stel in het dialoogvenster Cellen opmaken de vulkleur, het lettertype en de celranden in zoals gewenst en klik achtereenvolgens op OK om de instellingen op te slaan.

Nu zijn in het geselecteerde bereik de rijen gemarkeerd op basis van dubbele cellen in de opgegeven kolom. Zie afbeelding:

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

1. Selecteer het bereik zonder de koprij.

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

3In het dialoogvenster Nieuwe opmaakregel:
(1) Klik om te kiezen voor de Een formule gebruiken om te bepalen welke cellen worden opgemaakt optie;
(2) In het Waarden opmaken waarvoor deze formule waar is veld, typ de formule =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1;
(3) Klik op de Opmaak knop.

OpmerkingenIn 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 de kolom;
(3) $D$3:$D$12 is de derde kolom in het bereik, en $D3 is de eerste cel in de kolom;
(4) Als uw bereik meer kolommen bevat, kunt u de kolomverwijzingen en hun eerste cellen achtereenvolgens toevoegen in de formule.

4. Stel in het dialoogvenster Cellen opmaken de markeerkleur, het lettertype, de celranden enz. in zoals gewenst en klik vervolgens achtereenvolgens op OK om de instelling op te slaan.

Nu zijn de dubbele rijen geïdentificeerd en gemarkeerd in het geselecteerde bereik. Zie afbeelding:

U heeft wellicht gemerkt dat met bovenstaande methoden alle duplicaten worden geïdentificeerd of gemarkeerd. Soms wilt u alleen zien welke items dubbel zijn behalve de eerste instantie. Dit kan ook met de functie Voorwaardelijke opmaak en een andere formule.

1Selecteer de kolom met koptekst.
TipsAls u rijen wilt markeren op basis van duplicaten in één kolom behalve de eerste instantie, selecteer dan het bereik zonder koprij.

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

3In het dialoogvenster Nieuwe opmaakregel:
(1) Klik om te markeren de Een formule gebruiken om te bepalen welke cellen worden opgemaakt optie;
(2) In het Waarden opmaken waarvoor deze formule waar is veld, typ de formule =COUNTIF($C$3:$C3, C3)>1;
TipsOm rijen te markeren op basis van duplicaten in één kolom, typ de formule =COUNTIF($C$3:$C3, $C3)>1.
(3) Klik op de Opmaak knop.

4. Stel in het pop-upvenster Cellen opmaken de markeerkleur, het lettertype, de celranden enz. in zoals gewenst en klik vervolgens op OK om de instellingen op te slaan.

Nu ziet u dat de dubbele cellen behalve de eerste instantie in de geselecteerde kolom (of rijen op basis van duplicaten in de opgegeven kolom) zijn gemarkeerd. Zie afbeelding:

Wanneer we dubbele cellen of rijen markeren met de functie Voorwaardelijke opmaak, worden alle duplicaten met dezelfde kleur gemarkeerd. Als echter verschillende reeksen duplicaten met verschillende kleuren worden gemarkeerd, is het makkelijker te lezen en te onderscheiden. Hiervoor kan een VBA-script in Excel uitkomst bieden.

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

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

VBA: Dubbele cellen markeren 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 F5 of klik op het pictogram Uitvoeren om deze VBA uit te voeren.

4. In het pop-upvenster van Kutools voor Excel selecteert u de kolom waarin u dubbele cellen met verschillende kleuren wilt markeren en klikt u op OK.

Elke reeks dubbele waarden wordt nu gemarkeerd met een eigen kleur.

De eerste tool die ik introduceer is de functie Dubbele & Unieke cellen selecteren, geleverd door Kutools voor Excel. Met deze functie kunt u eenvoudig unieke of dubbele cellen vinden.

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

2. Klik op Kutools > Selecteren > Dubbele & Unieke cellen selecteren.

3In het dialoogvenster Dubbele & Unieke cellen selecteren vinkt u de gewenste opties aan en klikt u op de Ok knoppen om de bewerking te voltooien.

OpmerkingenIn het dialoogvenster Dubbele & Unieke cellen selecteren:
(1) Als u alle duplicaten behalve de eerste instantie wilt selecteren of markeren, vink dan de optie Duplicaatwaarden (Eerste overeenkomst uitsluiten) aan. Anders vinkt u de optie Duplicaatwaarden (Eerste overeenkomst opnemen) aan.
(2) Als u de duplicaten wilt markeren, vink dan de optie Vul achtergrondkleur aan en kies een markeerkleur naar wens.
(3) Als u rijen wilt selecteren of markeren op basis van duplicaten in de geselecteerde kolom, vink dan de optie Selecteer hele rij aan.
(4) Als u dubbele waarden wilt selecteren of markeren met hoofdlettergevoeligheid, vink dan de optie Hoofdlettergevoelig aan.

Kutools voor Excel biedt ook een handige tool – Cellen vergelijken, waarmee u eenvoudig dubbele cellen in twee kolommen kunt vinden en markeren.

1. Klik op Kutools > Cellen vergelijken om het dialoogvenster Gelijke & Verschillende cellen selecteren te openen.

2. Geef in het dialoogvenster Gelijke & Verschillende cellen selecteren de twee kolommen op in de velden Waarden zoeken in en Volgens, vink de optie Zelfde waarde aan en andere opties naar wens.

Opmerkingen:
(1) Als u dubbele rijen wilt zoeken, vink dan Per rij aan; en om dubbele cellen te zoeken, vink de optie Per cel aan in het Methode gedeelte;
(2) Vink de optie Vul achtergrondkleur aan en kies een markeerkleur als u de dubbele rijen of cellen wilt markeren;
(3) Vink de optie Selecteer hele rij aan als u de hele rij wilt selecteren of markeren op basis van de duplicaten;
(4) Vink de optie Hoofdlettergevoelig aan als u hoofdlettergevoelige duplicaten wilt zoeken of markeren.

3. Klik achtereenvolgens op Ok om de instellingen af te ronden.

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

Met deze methode identificeert en markeert u dubbele cellen met een voorwaardelijke opmaakregel en filtert u vervolgens eenvoudig op markeerkleur in Excel.

1. Pas voorwaardelijke opmaak toe om duplicaten te zoeken en markeren in de opgegeven kolom. (Klik om te zien hoe)

2Klik op de kolomkop van de opgegeven kolom en klik op Gegevens > Filteren.

3Klik vervolgens op het filterpictogram in de kolomkop en kies Filteren op kleuren selecteer vervolgens de opgegeven voorwaardelijke opmaakkleur in de vervolgkeuzelijst. Zie afbeelding:

Nu ziet u dat alleen de rijen met dubbele cellen zijn gefilterd. Zie afbeelding:

U kunt ook duplicaten identificeren met een formule in een hulpkolom en vervolgens eenvoudig duplicaten filteren met de 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,"Dubbel","") in en sleep de vulgreep van deze cel om de formule naar andere cellen te kopiëren.

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

3. Klik op de kolomkop – Duplicaten en klik op Gegevens > Filteren.

4Klik vervolgens op het filterpictogram in de kolomkop, vink alleen Dubbelaan en klik op de OK knop. Zie afbeelding:

Nu zijn alleen de rijen met dubbele waarden gefilterd. Zie afbeelding:

In het algemeen kunt u =COUNTIF(bereik, criteria) gebruiken om het totale aantal keren dat een bepaalde waarde voorkomt in het opgegeven bereik te tellen. Bijvoorbeeld, om te tellen hoe vaak “Apple” voorkomt in de lijst A2:A10, gebruikt u de formule =COUNTIF(A2:A10, "Apple").

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

U kunt een matrixformule gebruiken om hoofdlettergevoelige dubbele waarden met criteria in Excel te tellen. Bijvoorbeeld, om te tellen hoe vaak de waarde "Apple" voorkomt in de lijst B2:B21 met hoofdlettergevoeligheid, doet u het volgende:

1. Selecteer een lege cel.

2. Voer de formule =SUM(--EXACT(B2:B20,E2)) in.

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

OpmerkingenIn de matrixformule,
(1) B2:B20 is de kolom waarin u duplicaten wilt tellen. (2) E2 is de cel met de opgegeven waarde waarvan u het aantal wilt tellen. U kunt de celverwijzing wijzigen in de waarde tussen aanhalingstekens, bijvoorbeeld "Apple".

Soms wilt u duplicaten tellen met twee of meer criteria. U kunt hiervoor de COUNTIFS functie gebruiken.
Bijvoorbeeld, er is een fruitsverkooptabel zoals hieronder weergegeven. Hier willen we het aantal keren tellen dat appel is verkocht op7/5/2020 en het verkoopbedrag groter is dan300. U telt de duplicaten met deze criteria als volgt:

1. Selecteer een lege cel.

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

3. Druk op Enter om het resultaat te krijgen.

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

Om alle duplicaten in een kolom te tellen behalve de eerste instantie, doet u het volgende:

1. Selecteer een lege cel naast de kolom.

2Voer de formule in =IF(COUNTIF($B$3:B3,B3)>1,"JA","")en sleep vervolgens de vulgreep naar beneden om de formule naar andere cellen te kopiëren.

OpmerkingenIn bovenstaande formule,
(1) $B$3:B3 is het bereik waarin u de duplicaten telt. In $B$3:B3 verandert B3 automatisch bij het kopiëren van de formule.
(2) B3 is de eerste cel in de opgegeven kolom.
(3) Deze formule geeft JA of leeg terug. JA betekent dat de betreffende waarde dubbel is, leeg betekent uniek.

Nu zijn alle duplicaten in de opgegeven kolom geïdentificeerd. U kunt de formuleresultaten tellen om het totale aantal duplicaten te krijgen.

3. Selecteer een lege cel.

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

OpmerkingenIn bovenstaande formule,
(1) C3:C16 is het bereik waarin we de formule hebben toegepast om duplicaten te identificeren in de vorige stap.
(2) JA is de waarde die door de vorige formule is teruggegeven.

Nu hebben we het totale aantal dubbele waarden in de opgegeven kolom. Het totale aantal duplicaten is exclusief de eerste instantie.

Om het aantal duplicaten inclusief de eerste instantie in Excel te tellen, kunt u een matrixformule gebruiken.

1. Selecteer een lege cel.

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

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

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

Om uw werk te vereenvoudigen en u te besparen van het onthouden van lange formules, kunt u de functie Dubbele & Unieke cellen selecteren van Kutools voor Excel gebruiken om snel het aantal dubbele waarden in de opgegeven lijst of kolom te tellen, exclusief of inclusief de eerste instantie.

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

2. Vink in het dialoogvenster Dubbele & Unieke cellen selecteren de optie Duplicaatwaarden (Eerste overeenkomst uitsluiten) of Duplicaatwaarden (Eerste overeenkomst opnemen) aan, en klik op Ok.

3. Nu zijn alle dubbele waarden inclusief of exclusief de eerste instantie geselecteerd en verschijnt er direct een dialoogvenster met het aantal geselecteerde cellen. Zie afbeelding hierboven.

Stel, u wilt twee namenlijsten vergelijken en het aantal duplicaten ertussen tellen. Hoe lost u dit snel op? Dit kan ook met een formule in Excel.

1. Selecteer een lege cel.

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

3. Druk op Enter .

OpmerkingenIn 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 waarop u duplicaten wilt baseren.

U kunt ook een externe invoegtoepassing, Kutools voor Excel, gebruiken om snel het totale aantal dubbele cellen tussen twee kolommen te tellen.

1. Klik op Kutools > Selecteren > Gelijke & Verschillende cellen selecteren.

2In het dialoogvenster Gelijke & Verschillende cellen selecteren:
(1) Geef de twee kolommen op in het veld Waarden zoeken in en Volgens apart.
(2) Vink de optie Per cel aan.
(3) Vink de optie Zelfde waarde aan.

4. Klik op Ok.

Nu zijn alle dubbele cellen in de eerste kolom geselecteerd en verschijnt er direct een dialoogvenster met het aantal geselecteerde dubbele cellen. Zie afbeelding:

Opmerkingen: Met deze functie telt u het totale aantal dubbele waarden in de kolom die u hebt opgegeven in het Waarden zoeken in veld in het dialoogvenster Gelijke & Verschillende cellen selecteren. Als u het totale aantal dubbele waarden in de tweede kolom wilt tellen, gebruikt u de functie Gelijke & Verschillende cellen selecteren opnieuw en geeft u de tweede kolom op in het veld Waarden zoeken in.

U kunt elke dubbele waarde snel één keer tellen met een formule als volgt:

1. Selecteer een lege cel.

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

3. Druk op Enter om het resultaat te krijgen.

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

Bij het tellen van een lijst kunt u elke dubbele waarde één keer tellen met hoofdlettergevoeligheid door een matrixformule in Excel toe te passen.

1. Selecteer een lege cel.

2. Voer de matrixformule =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)) in.

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

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

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

1. Selecteer een lege cel.

2. Klik op Kutools > Formulehulp > Aantal > Unieke cellen tellen (inclusief de eerste duplicaatwaarde).

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

Het resultaat wordt direct in de geselecteerde cel weergegeven.

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

1Selecteer de kolom waarin u elke reeks dubbele waarden wilt tellen en klik op Gegevens > Sorteren oplopend of Sorteren aflopend.

2. Vink in het pop-upvenster Sorteerwaarschuwing de optie Selectie uitbreiden aan en klik op Sorteren.

Nu ziet u dat de selectie is gesorteerd op de dubbele waarden van de opgegeven kolom.

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

4In het dialoogvenster Subtotaal:
(1) Selecteer de opgegeven kolom in de Bij elke wijziging in vervolgkeuzelijst;
(2) Selecteer Tellen in de Functie gebruiken vervolgkeuzelijst;
(3) Vink alleen de opgegeven kolom aan in de Subtotaal toevoegen aan lijst;
(4) Klik op de OK knop.

Nu ziet u dat elke reeks dubbele waarden is geteld en het resultaat onder elke reeks dubbele waarden is toegevoegd, zie afbeelding hierboven.

U kunt ook een draaitabel maken om snel elke reeks dubbele waarden in Excel te tellen.

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

2. Geef in het dialoogvenster Draaitabel maken aan waar u de nieuwe draaitabel wilt plaatsen en klik op OK.

3. Sleep in het veld Draaitabelvelden de opgegeven kolom naar zowel Rijen als Waarden. Nu ziet u dat elke reeks dubbele waarden in de opgegeven kolom in bulk is geteld. Zie afbeelding:

Als u Kutools voor Excel al 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 Geavanceerd samenvoegen van rijen wijzigt het geselecteerde bereik en verwijdert rijen op basis van dubbele waarden in de opgegeven sleutelkolom. Maak een back-up of kopie van uw gegevens voordat u onderstaande stappen uitvoert.

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

2. Selecteer het oorspronkelijke gegevensbereik en de nieuwe kolom samen en klik op Kutools > Samenvoegen & Splitsen > Geavanceerd samenvoegen van rijen.

3In het dialoogvenster Geavanceerd samenvoegen van rijen:
(1) Selecteer de opgegeven kolom waarin u elke reeks dubbele waarden wilt tellen en klik op Sleutelkolom.
(2) Selecteer de nieuwe kolom (Aantal) en klik op Berekenen > Tellen.
(3) Geef indien nodig combinaties of berekeningen op voor andere kolommen.
(4) Klik op de Ok knop.

Nu ziet u dat elke reeks dubbele waarden in de opgegeven kolom in bulk is geteld. Zie afbeelding:

Stel, er staat een lijst met fruit in een kolom. Sommige fruitsoorten komen meerdere keren voor. Nu wilt u elk dubbel fruit markeren in de volgorde waarin het voorkomt. Hoe pakt u dit aan? In dit onderdeel leest u hoe u dit eenvoudig met een formule in Excel doet.

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

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 vulgreep van deze formulecel om de formule naar andere cellen te kopiëren.

OpmerkingenIn bovenstaande formule,
(1) $C$3:$C$14 is de opgegeven kolom waarin u de dubbele waarden op volgorde wilt tellen.
(2) C3 is de eerste cel in de opgegeven kolom.
(3) Als de betreffende waarde dubbel is, geeft deze formule het volgnummer1,2,3… terug op basis van de volgorde; als de waarde uniek is, blijft het veld leeg.

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

1. Selecteer de kolom waarin u alle dubbele waarden behalve de eerste instantie wilt verwijderen.

2. Klik op Gegevens > Dubbele items verwijderen.

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

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

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

Tips: Als u in de vorige stap de optie Selectie uitbreiden heeft aangevinkt, worden hier alle kolommen weergegeven. Toch moet u alleen de opgegeven kolom aanvinken.

5. Vervolgens verschijnt er een dialoogvenster met het aantal verwijderde dubbele waarden. Klik op OK om 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.

2In het dialoogvenster Geavanceerd filteren:
(1) Vink de optie Kopiëren naar een andere locatie aan;
(2) In het Lijstbereik veld selecteert u de opgegeven kolom waarvan u dubbele waarden wilt verwijderen;
(3) In het Kopiëren naar veld geeft u het bereik op waar u de kolom naartoe wilt plakken;
(4) Vink de optie Alleen unieke records aan.
(5) Klik op de OK knop.

Nu ziet u dat de opgegeven kolom is geplakt naar het opgegeven bereik met alle dubbele waarden verwijderd behalve de eerste instantie. Zie afbeelding:

U kunt ook een VBA gebruiken om snel dubbele waarden behalve de eerste instantie uit een kolom in Excel te verwijderen.

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

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

VBA: Duplicaten verwijderen behalve de eerste instantie

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 pop-upvenster het bereik op waarvan u dubbele waarden wilt verwijderen en klik op OK.

Alle dubbele waarden, behalve de eerste instantie, worden direct verwijderd uit het opgegeven bereik.

Opmerking: Deze VBA-code is hoofdlettergevoelig.

We kunnen alle dubbele waarden inclusief de eerste instantie in een kolom of lijst markeren met een voorwaardelijke opmaakregel en vervolgens alle dubbele waarden filteren op markeerkleur. Daarna selecteren we alle gefilterde dubbele cellen en verwijderen deze in bulk.

1. Pas voorwaardelijke opmaak toe om dubbele waarden te markeren. (Klik om te zien hoe)

2. Selecteer de kolom waarvan u dubbele waarden (inclusief de eerste instantie) wilt verwijderen en klik op Gegevens > Filteren.

3Klik op het filterpictogram in de kolomkop van de opgegeven kolom. Kies in het menu Filteren op kleuren geef vervolgens de markeerkleur op in het submenu.

Nu zijn alle dubbele waarden gefilterd.

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

5. Nu zijn alle dubbele waarden in bulk verwijderd. Laat de gefilterde lijst geselecteerd en klik opnieuw op Filteren > Gegevens om het filter op te heffen.

Nu ziet u dat alle dubbele cellen inclusief de eerste instantie in bulk zijn verwijderd en alleen unieke waarden overblijven.

Opmerkingen: Met deze methode verwijdert u rijen op basis van de dubbele waarden in de opgegeven kolom.

U kunt ook een formule gebruiken om dubbele waarden inclusief de eerste instantie te identificeren in een hulpkolom, vervolgens dubbele waarden filteren op basis van de formuleresultaten en deze gefilterde dubbele waarden in bulk verwijderen.

1. Voeg een hulpkolom toe naast de opgegeven kolom, voer de formule =COUNTIF($B$3:$B$11,B3) in de eerste cel van de hulpkolom in en sleep de vulgreep naar beneden om de formule naar andere cellen te kopiëren. Zie afbeelding:

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

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

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

4. Nu zijn 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 pop-upvenster op OK om door te gaan.

6. Nu zijn alle dubbele waarden en hun rijen in bulk verwijderd. Klik vervolgens opnieuw op Gegevens > Filteren om het filter op te heffen.

Nu ziet u dat alle dubbele waarden inclusief de eerste instantie in bulk zijn verwijderd.

Als u Kutools voor Excel hebt geïnstalleerd, kunt u ook de functie Dubbele & Unieke cellen selecteren gebruiken om snel dubbele waarden inclusief of exclusief de eerste instantie te selecteren en te verwijderen in Excel.

1. Selecteer de kolom waarvan u duplicaten wilt verwijderen.

2. Klik op Kutools > Selecteren > Dubbele & Unieke cellen selecteren.

3. Vink in het dialoogvenster Dubbele & Unieke cellen selecteren de optie Duplicaatwaarden (Eerste overeenkomst opnemen) aan en klik op Ok.

Opmerkingen:
(1) Om dubbele waarden exclusief de eerste instantie te selecteren en te verwijderen, vinkt u de optie Duplicaatwaarden (Eerste overeenkomst uitsluiten) aan.
(2) Om rijen te selecteren en te verwijderen op basis van dubbele waarden in de opgegeven kolom, vinkt u de optie Selecteer hele rij aan.
(3) Om dubbele waarden met hoofdlettergevoeligheid te selecteren en te verwijderen, vinkt u de optie Hoofdlettergevoelig aan.
(4) Om dubbele cellen of rijen te selecteren, markeren en verwijderen, vinkt u de opties Vul achtergrondkleur of Vul lettertypekleur aan en geef de gewenste vul- of lettertypekleuren op.

4. Vervolgens verschijnt er een dialoogvenster met het aantal geselecteerde cellen. Klik op OK om te sluiten.

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

6. Vink in het dialoogvenster Verwijderen de optie Cellen omhoog verschuiven aan en klik op OK.

Nu zijn alle dubbele waarden inclusief de eerste instantie in bulk verwijderd.

In de meeste gevallen identificeren we dubbele waarden in een kolom en verwijderen we vervolgens de volledige rijen op basis van de duplicaten. Deze handeling lijkt sterk op het verwijderen van dubbele waarden uit een enkele kolom. Daarom kunnen we vergelijkbare oplossingen gebruiken om rijen te verwijderen op basis van duplicaten in de opgegeven kolom.

De eerste methode is het gebruik van de ingebouwde Dubbele items verwijderen functie om rijen te verwijderen op basis van duplicaten in de opgegeven kolom. Selecteer het bereik waarin 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 vervolgens op OK om de verwijdering uit te voeren.

U kunt ook de functies Voorwaardelijke opmaak en Filteren gebruiken om rijen te verwijderen op basis van dubbele waarden in de opgegeven kolom. Markeer eerst rijen op basis van dubbele waarden in een bepaalde kolom met een voorwaardelijke opmaakregel (klik om te zien hoe). Filter vervolgens het bereik op kleur. Verwijder daarna eenvoudig alle gefilterde rijen. Tot slot wist u het filter en ziet u alleen rijen met unieke waarden in de opgegeven kolom.

U kunt ook een hulpkolom toevoegen en de formule =COUNTIF($C$3:$C$21,C3) gebruiken om duplicaten in de opgegeven kolom te identificeren. Filter vervolgens de getallen groter dan1 in de hulpkolom en verwijder eenvoudig alle gefilterde rijen. Na het wissen van het filter ziet u alleen rijen met unieke waarden in de opgegeven kolom.

De externe invoegtoepassing Kutools voor Excel biedt ook een zeer handige functie, Dubbele & Unieke cellen selecteren, waarmee u snel rijen op basis van dubbele waarden in de opgegeven kolom kunt selecteren en vervolgens deze geselecteerde rijen eenvoudig kunt verwijderen via het contextmenu.

De functie Geavanceerd samenvoegen van rijen van Kutools voor Excel kan ook snel rijen verwijderen op basis van dubbele waarden in de opgegeven sleutelkolom.

U kunt een hulpkolom toevoegen en een formule gebruiken om de dubbele waarden tussen twee kolommen te identificeren en vervolgens de duplicaten eenvoudig filteren en verwijderen.

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

2. Typ in de eerste cel van de hulpkolom (zonder de kopcel) de formule =IF(ISERROR(MATCH(C2,$A$2:$A$13,0)),"Uniek","Dubbel") en sleep de vulgreep naar beneden om de formule naar andere cellen te kopiëren.

OpmerkingenIn bovenstaande formule,
(1) C2 is de eerste cel in de opgegeven kolom waarvan u dubbele waarden wilt verwijderen;
(2) $A$2:$A$13 is de andere kolom waarmee we moeten vergelijken.
(3) Deze formule geeft Dubbel terug als de betreffende waarde dubbel is met waarden in de andere kolom en Uniek als deze verschilt van waarden in de andere kolom.

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

4Klik op het filterpictogram in de kop van de hulpkolom, vink vervolgens in het menu alleen Dubbelaan en klik op de OK knop.

5. Nu zijn alle dubbele waarden gefilterd. Selecteer de gefilterde cellen, klik met de rechtermuisknop en kies Rij verwijderen in het contextmenu. Klik vervolgens op OK in het pop-upvenster.

6. Nu zijn alle dubbele waarden uit de opgegeven kolom verwijderd. Klik vervolgens opnieuw op Gegevens > Filteren om het filter op te heffen.

Nu ziet u dat alleen unieke waarden overblijven in de opgegeven kolom. U kunt de hulpkolom verwijderen indien gewenst.

Opmerkingen: Met deze methode verwijdert u de volledige rijen op basis van de dubbele waarden in de opgegeven kolom.

Als u Kutools voor Excel hebt geïnstalleerd, kunt u de handige functie Gelijke & Verschillende cellen selecteren gebruiken om snel de dubbele waarden tussen twee kolommen te selecteren en vervolgens eenvoudig te verwijderen.

1. Klik op Kutools > Selecteren > Gelijke & Verschillende cellen selecteren om deze functie te activeren.

2. Geef in het dialoogvenster Gelijke & Verschillende cellen selecteren beide kolommen apart op in de velden Waarden zoeken in en Volgens, vink de opties Per cel en Zelfde waarde aan en klik op Ok. Zie afbeelding:

3. Nu zijn alle dubbele waarden tussen de twee kolommen geselecteerd in de eerste kolom (de kolom die u hebt opgegeven in het veld Waarde zoeken in). Klik op OK in het pop-upvenster.

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