Ontdubbeling in Excel: dubbele waarden vinden en verwijderen

Het ontdubbelen van data is een veelvoorkomend probleem in Excel. Excel biedt zelf een handige functie om simpele ontdubbeling mee uit te voeren, maar deze ontdubbeling is definitief en moeilijk te controleren.

Onze Excel experts worden vaak ingeroepen om te helpen bij de ontdubbeling van complexe bestanden, bijvoorbeeld als er meerdere bronnen moeten worden gecombineerd.

Eenvoudige ontdubbeling

Excel biedt zelf prima tools voor eenvoudige ontdubbeling. Als je bijvoorbeeld een lijst met e-mailadressen hebt, dan kun je Excel de dubbele waarden eenvoudig laten vinden en verwijderen.

Dubbele waarden vinden

Maak een selectie van het gebied waarin je Excel wilt laten zoeken naar dubbele waarden:

Ga in je hoofdmenu naar Home > Hightlight Cells Rules > Duplicate Values:

Klik op ‘OK’ in het pop-up venster:

Als resultaat toont Excel alle waarden die meer dan 1 keer voorkomen.

Dubbele waarden verwijderen

Om de dubbele waarden vervolgens te verwijderen selecteer je opnieuw de betreffende kolom en klik je op Data > Remove Duplicates:

Zodra je op ‘OK’ klikt in het dialoogvenster verwijdert Excel alle dubbele data (er blijft dus altijd een unieke waarde over). In het pop-up scherm zie je precies hoeveel waarden er verwijderd zijn en hoeveel er zijn overgebleven.

N.B. Je hoeft overigens niet verplicht de stappen van het vinden en highlighten van de dubbele waarden te doorlopen. Je kunt ook direct over gaan tot ontdubbeling.

Complexe ontdubbeling

Kijk eens naar het onderstaande adressenbestand. Er zijn vier kolommen:

• Voornaam
• Achternaam
• Geboortedatum
• E-mailadres

Hoe ontdubbel je een dergelijk bestand? Namen en geboortedata kunnen vaker voorkomen, zonder dat er sprake hoeft te zijn van een dubbele invoer. Je zou kunnen ontdubbelen op basis van uniek e-mailadres, maar wat als er meerdere mensen in een huishouden gebruik maken van hetzelfde e-mailadres?

Als we dezelfde stappen doorlopen als in het eerste voorbeeld vinden we de volgende dubbele waarden:

In dit kleine bestand zien we met het blote oog dat Julia Smits en Zeger Verbeek inderdaad dubbel zijn ingevoerd. Ook Sam de Wit is tweemaal ingevoerd, maar zijn achternaam is op verschillende manieren genoteerd. Sara en Lotte Prins delen slechts een achternaam en Finn Meijer heeft dezelfde geboortedatum als Tess Bakker.

Het probleem van de standaard ontdubbelingsfunctie van Excel

Als we nu op dezelfde manier ontdubbelen als in ons eerste voorbeeld, dan verdwijnen alleen de dubbele waarden die in alle vier de kolommen voorkomen:

Zoals je ziet komt Sam de Wit nog steeds twee keer voor in ons bestand.

We kunnen een tweede ronde ontdubbelen op basis van e-mailadres, maar in dat geval blijft Tess Bakker nog twee keer in ons bestand staan, omdat zij met twee verschillende e-mailadressen geregistreerd is:

Hoe ga je hiermee om in het geval van grote bestanden met veel meer data, of als er meerdere bronnen moeten worden gecombineerd?

Handmatige ontdubbeling

Handmatige ontdubbeling heeft verschillende voordelen. Zo kun je de originele lijst behouden (en aanvullen) op een eerste werkblad, terwijl je de ontdubbelde lijst genereert op een tweede werkblad. Zo kun je het resultaat altijd vergelijken met de brongegevens.

Zie onderstaand voorbeeld.

De bovenstaande tabel toont of een bepaald e-mailadres vaker dan één keer voorkomt en kent vervolgens een uniek volgnummer toe aan iedere unieke verschijning. Hier zijn natuurlijk diverse functies voor nodig.

Functies

In kolom E (Dubbel) kijken we met behulp van de functie COUNTIF of het e-mailadres in kolom D in de reeks erboven al eerder is voorgekomen. In cel E16 staat bijvoorbeeld: =COUNTIF(D$2:D15;D16). Het resultaat hiervan is 0.

In kolom F (Eerste verschijning) noteren we met behulp van de IF (ALS) functie of het de eerste verschijning van dit e-mailadres is. In cel F16 staat: =IF(E16=0;1;0). Het resultaat hiervan is 1.

Ten slotte geven we ieder uniek e-mailadres een volgnummer in kolom G (Ranking). Dit doen we met een combinatie van de IF (ALS) functie en de SUM (SOM) functie. In cel G16 staat: =IF(F16=1;SUM(F$2:F16);0). Het resultaat hiervan is 15.

Resultaat

In een tweede werkblad publiceren we vervolgens met de combinatie INDEX en MATCH de lijst met unieke e-mailadressen.

INDEX en MATCH

Hoe werkt dat in het geval van dit werkblad? In kolom B (Match) zoeken we met de MATCH functie het volgnummer in kolom A op in het werkblad met de originele data. In cel B16 staat bijvoorbeeld: =MATCH(A16;’Werkblad1′!G:G;0). Het resultaat van deze match is 16, omdat volgnummer 15 in het eerste werkblad overeenkomt met rij 16.

Vervolgens vullen we kolom C tot en met F met de gegevens die in het eerste werkblad op rij 16 te vinden zijn in de rijen voor Voornaam (kolom A), Achternaam (kolom B), Geboortedatum (kolom C) en E-mailadres (kolom D) met behulp van de INDEX functie. In de cellen C16, D16, E16 en F16 staat zodoende:

• C16: =INDEX(‘Werkblad1’!A:A;$B16)
• D16: =INDEX(‘Werkblad1’!B:B;$B16)
• E16: =INDEX(‘Werkblad1’!C:C;$B16)
• F16: =INDEX(‘Werkblad1’!D:D;$B16)

Voordeel

Het voordeel van deze methode is dat je het werkblad met originele data kunt behouden en kunt aanvullen met nieuwe data. Om de lijst met unieke adressen aan te vullen op het tweede werkblad hoef je vervolgens slechts het formulebereik te vergroten.

Ontdubbelen is maatwerk

De bovenstaande voorbeelden zijn vrij eenvoudig, maar in de praktijk zijn ontdubbelingsopdrachten vaak complex. Als je bijvoorbeeld meerdere databases of bestanden wilt samenvoegen, of als je wilt ontdubbelen op basis van méér dan een kolom. In dat geval kun je werken met een ‘sleutel’ die (elementen) van diverse kolommen samenvoegt.

Sleutel

In het werkblad hieronder hebben we in kolom E (Sleutel) een combinatie gemaakt van de eerste letter van de voornaam, de achternaam én het e-mailadres. Vervolgens zoeken we in kolom F naar sleutels die vaker dan één keer voorkomen.

Het bepalen van de sleutel en de voorwaarden voor ontdubbeling kunnen een ingewikkeld proces zijn en er zijn vele verschillende scenario’s te bedenken. Complexe ontdubbeling is dan ook maatwerk. Bij PerfectXL helpen we je graag bij ontdubbelingsvraagstukken. Neem contact met ons op voor advies, of om de ontdubbeling aan ons uit te besteden.