Geregeld krijg ik tijdens trainingen vragen over dubbele gegevens. Sommigen sorteren dan de gegevens in een lijst en zoeken zelf de dubbele data eruit. Anderen bedenken andere oplossingen. In deze tip zie je enkele opties voor het vinden/en of het verwijderen van dubbele gegevens. En natuurlijk is het uitgangspunt dat Excel het werk moet doen (en niet ik).
Als voorbeeld gebruik ik onderstaande lijst: factuurnummers en bedragen
Mogelijkheid 1: functie UNIEK (Eng: UNIQUE)
Met deze relatief nieuwe functie (beschikbaar in Excel voor Microsoft 365 (Windows en Mac), Webversie van Excel, Excel 2021 (voor Windows en Mac) en Excel voor iPad/ iPhone/Android-tablets/Android-telefoons) kun je uit een gebied de unieke gegevens halen en in een nieuwe lijst weergeven.
Met =UNIEK(<bereik met de factuurnrs>) krijg je dus een nieuwe lijst met unieke nummers. In kolom E zie je de formule die staat in de cel ernaast.
Het bijzondere aan deze functie is dat het een functie is die gebruik maakt van een dynamisch bereik. Dat zie je omdat er een blauwe rand omheen staat. De functie staat alleen cel D2 (cel linksboven van het blauwe omkaderde gebied) en loopt over in de cellen eronder in dat gebied. De andere cellen in dat gebied zijn leeg: je ziet alleen met een lichtgrijze kleur de gebruikte functie in de formulebalk om aan te geven welke berekening in dat gebied overloopt.
Selecteer je bijvoorbeeld D4 en wis je de inhoud, dan gebeurt er niets (er staat immers niets).
Zolang de rest van blauwe omrande gebied leeg is, gaat dit goed. Staat er wel iets, dan levert dit een foutmelding op. Haal de inhoud van die cel weg of gebruik hiervoor het pictogram ervoor en het probleem is opgelost.
Mogelijkheid 2: Dubbele waarden weergeven
Met een voorwaardelijke opmaak kun je de dubbele gegevens een opmaak geven, bijvoorbeeld een opvulkleur. Dan zie je wat de dubbele nummers zijn.
Selecteer het gebied met de factuurnummers. Kies Start > Voorwaardelijke opmaak > Markeringsregels voor cellen > Dubbele waarden.
De dubbele factuurnummers krijgen een kleur. Door er met de rechtermuisknop op te klikken > Filteren > Kleur van geselecteerde cel gebruiken voor filter kun je direct alleen de dubbele gegevens zien.
Mogelijkheid 3: Functie AANTAL.ALS (Eng: COUNTIF)
Met deze functie kun je tellen hoe vaak iets voorkomt. In dit geval: hoe vaak komt een factuurnummer voor? Is die waarde groter dan 1 dan komt zo’n nummer dus vaker voor.
Als eerste argument van deze functie geef je op waar Excel moet gaan zoeken (in de lijst met factuurnummers) en als tweede argument wat er gezocht moet worden (het factuurnummer dat op deze rij staat: dus A2 in de bovenste formule). Daarna kopieer je dit naar de andere cellen.
Vervolgens zou je kunnen filteren op “groter dan 2” om de dubbele factuurnummers eenvoudig te vinden. Of je geeft de cellen met een getal groter dan 1 een voorwaardelijke opmaak om ze herkenbaar te maken.
Mogelijkheid 4: Dubbele waarden verwijderen
De opties hierboven geven alleen aan wat er dubbel is. Soms wil je dubbele gegevens verwijderen. Dat kan ook, maar dan moet je in dit geval oppassen! Wil je alleen de dubbele factuurnummers verwijderen of moet de combinatie factuurnummer en bedrag hetzelfde zijn en alleen dan moet het verwijderd worden?
Selecteer alleen de factuurnummers ① en kies Gegevens > Dubbele waarden verwijderen (in oudere versies heet dit Duplicaten verwijderen). Dan krijg je eerst een vraag ②. Ga je door met de huidige selectie dan krijg je na ③ de melding dat er 2 items zijn verwijderd ④.
Je ziet dan ook dat het helemaal verkeerd gaat! Omdat er uit de A-kolom cellen verwijderd zijn, corresponderen in de B-kolom de bedragen niet meer na het eerste verwijderde factuurnummer! Kijk maar bij de 2e factuur 23/15.
Daarom moet je direct de factuurnummers én de bedragen selecteren of de selectie uitbreiden ②) en dan de dubbele waarden te verwijderen. Gaat het dan alleen om de dubbele factuurnummers dan moet je het dialoogvenster zo invullen.
Nu wordt alleen gekeken naar de factuurnummers. Als een nummer nog een keer voorkomt, dan wordt de 2e (of 3e of volgende) verwijderd inclusief het bijbehorende bedrag.
Als je de dubbele gegevens wilt verwijderen waarbij de combinatie factuurnummer en bedrag hetzelfde zijn, dan schakel je beide selectievakjes in. Dat zal in dit voorbeeld niets verwijderen.
Het vervelende van dubbele waarden verwijderen is dat je wel ziet hoeveel er verwijderd worden, maar je ziet niet welke! Heb je er spijt van dan kun je natuurlijk altijd de “oeps-knop” gebruiken: Ongedaan maken!
Saskia Jacobsen is directeur-eigenaar van Toels-PC en helpt bedrijven en vooral hun gebruikers om slimmer en beter met de apps op hun pc te werken. In de expert-training Leer nieuwe geheimen van Excel komt onder andere het werken met nieuwe dynamische functies als UNIEK aan de orde.
Geef een reactie