Excel-kolomvergelijking: Vind overeenkomsten & verschillen!

Vergelijk twee kolommen rij voor rij

Hieronder zie je een gegevensset (het bereik B2:C8) waarbij ik moet controleren of de namen in kolom B hetzelfde zijn als die in kolom C binnen dezelfde rij.

In dit deel worden twee voorbeelden gegeven om uit te leggen hoe je twee kolommen rij voor rij kunt vergelijken.

• Voorbeeld1: Vergelijk cellen in dezelfde rij
• Voorbeeld2: Vergelijk cellen in dezelfde rij met hoofdlettergevoeligheid

Voorbeeld1: Vergelijk cellen in dezelfde rij

Als je doorgaans twee kolommen rij voor rij wilt vergelijken op exacte overeenkomsten, kun je onderstaande formule gebruiken:

=B2=C2

Druk op Enter en sleep de vulgreep naar cel D8. Als de formule Ja retourneert, zijn de waarden in dezelfde rij van beide kolommen volledig gelijk; als Nee wordt weergegeven, zijn de waarden verschillend.

Je kunt ook specifieke teksten weergeven om overeenkomsten of verschillen aan te geven door de IF-functie als volgt te gebruiken:

=IF(B2=C2,"Match","No match")

Het resultaat kan er als volgt uitzien:

Voorbeeld2: Vergelijk cellen in dezelfde rij met hoofdlettergevoeligheid

Als je twee kolommen rij voor rij wilt vergelijken met hoofdlettergevoeligheid, kun je een formule gebruiken die IF en EXACT combineert.

IF(EXACT(B2,C2), "Match", "Mismatch")

Druk op Enter om het eerste resultaat te krijgen en sleep vervolgens de vulgreep naar cel D8.

In bovenstaande formule kun je de teksten “Match” en “Mismatch” aanpassen naar je eigen omschrijving.

Vergelijk meerdere kolommen in dezelfde rij

Soms wil je meer dan twee kolommen in dezelfde rij vergelijken, zoals de gegevensset (het bereik B2:D7) in onderstaande schermafbeelding. In dit gedeelte worden verschillende methoden opgesomd om meerdere kolommen te vergelijken.

Hier wordt het opgesplitst in twee delen om gedetailleerde instructies te geven voor het vergelijken van meerdere kolommen in dezelfde rij.

• Voorbeeld1: Vergelijk meerdere kolommen en zoek overeenkomsten in alle cellen in dezelfde rij
• Voorbeeld2: Vergelijk meerdere kolommen en zoek overeenkomsten in willekeurige twee cellen in dezelfde rij

Voorbeeld1: Vergelijk meerdere kolommen en zoek overeenkomsten in alle cellen in dezelfde rij

Om volledige overeenkomsten over kolommen in dezelfde rij te vinden, kan onderstaande formule je helpen.

=IF(COUNTIF($B2:$D2, $B2)=3, "Full match", "Not")

Druk op Enter om het eerste vergelijkingsresultaat te krijgen en sleep vervolgens de vulgreep naar cel E7.

Voorbeeld2: Vergelijk meerdere kolommen en zoek overeenkomsten in willekeurige twee cellen in dezelfde rij

Soms wil je weten of er in dezelfde rij twee kolommen overeenkomen; hiervoor kun je onderstaande IF-formule gebruiken.

=IF(COUNTIF($B2:$D2,$B2)>=2,"Match","No match")

Druk op Enter en sleep de vulgreep naar cel E7.

Vergelijk twee of meerdere kolommen rij voor rij en markeer overeenkomsten of verschillen

Als je twee of meer kolommen wilt vergelijken en de overeenkomsten of verschillen wilt markeren, worden in dit gedeelte twee methoden geïntroduceerd om deze taken uit te voeren.

Er zijn twee voorbeelden voor het vergelijken en markeren van overeenkomsten en verschillen

• Voorbeeld1: Vergelijk twee kolommen en markeer volledige overeenkomsten in alle cellen in dezelfde rij of in willekeurige twee cellen in dezelfde rij
• Voorbeeld2: Vergelijk twee kolommen en markeer verschillen in dezelfde rij

Voorbeeld1: Vergelijk twee kolommen en markeer volledige overeenkomsten in alle cellen in dezelfde rij of in willekeurige twee cellen in dezelfde rij

Om overeenkomsten in alle cellen of in willekeurige twee cellen in dezelfde rij te markeren, kun je de functie Voorwaardelijke opmaak gebruiken.

1. Selecteer het bereik dat je wilt gebruiken en klik vervolgens op Start > Voorwaardelijke opmaak > Nieuwe regel.

2. In het dialoogvenster Nieuwe opmaakregel

1. Kies Gebruik een formule om te bepalen welke cellen moeten worden opgemaakt in het gedeelte Regeltype selecteren
2. Gebruik onderstaande formule in de Waarden opmaken waarvoor deze formule waar is tekstvak.
   

   =COUNTIF($B2:$D2, $B2)=3

3. Klik op Opmaak.

3. In het dialoogvenster Cellen opmaken kies je een vulkleur of andere celopmaak om de rijen te accentueren. Klik op OK > OK om de vensters te sluiten.

Nu worden alleen de rijen waarin alle cellen overeenkomen gemarkeerd.

Voorbeeld2: Vergelijk twee kolommen en markeer verschillen in dezelfde rij

Als je de verschillen in dezelfde rij wilt markeren, wat betekent dat je de kolomcellen één voor één vergelijkt en de verschillende cellen vindt op basis van de eerste kolom, kun je de ingebouwde Excel-functie 'Ga naar speciaal' gebruiken.

1. Selecteer het bereik waarin je de rijverschillen wilt markeren en klik op Start > Zoeken en selecteren > Ga naar speciaal.

2. In het pop-upvenster Ga naar speciaal kies je de optie Rijverschillen. Klik op OK.

Nu zijn de rijverschillen geselecteerd.

3. Laat de cellen geselecteerd, klik op Start > Vulkleur om een kleur te kiezen uit het keuzemenu.

Vergelijk twee kolommen in cellen op unieke en dubbele gegevens

In dit deel wordt de gegevensset (het bereik B2:C8) getoond zoals hieronder, en wil je alle waarden vinden die zowel in kolom B als in kolom C voorkomen, of alleen de waarden die in kolom B staan.

Dit gedeelte geeft4 verschillende methoden om twee kolommen in cellen te vergelijken; je kunt er een kiezen op basis van je behoeften.

• Voorbeeld1: Vergelijk twee kolommen in cellen en toon het vergelijkingsresultaat in een andere kolom
• Voorbeeld2: Vergelijk twee kolommen in cellen en selecteer of markeer dubbele of unieke gegevens met een handig hulpmiddel
• Voorbeeld3: Vergelijk twee kolommen in cellen en markeer dubbele of unieke gegevens
• Voorbeeld4: Vergelijk twee kolommen in cellen en lijst exacte duplicaten in een andere kolom

Voorbeeld1: Vergelijk twee kolommen in cellen en toon het vergelijkingsresultaat in een andere kolom

Hier kun je een formule gebruiken die IF en COUNTIF combineert om twee kolommen te vergelijken en de waarden te vinden die in kolom B staan maar niet in kolom C.

=IF(COUNTIF($C$2:$C$8, $B2)=0, "No in C", "Yes in C")

Druk op Enter en sleep de vulgreep naar cel D8.

Voorbeeld2: Vergelijk twee kolommen in cellen en selecteer of markeer dubbele of unieke gegevens met een handig hulpmiddel

Soms wil je na het vergelijken van twee kolommen verdere acties uitvoeren op de overeenkomsten of verschillen, zoals selecteren, verwijderen, kopiëren enzovoort. In dit geval kan het handige hulpmiddel – Selecteer dezelfde & verschillende cellen van Kutools voor Excel direct de overeenkomsten of verschillen selecteren voor verdere bewerking, en ook direct de waarden markeren.

Na het gratis installeren van Kutools voor Excel klik je op Kutools > Selecteren > Selecteer dezelfde & verschillende cellen. Stel in het dialoogvenster Selecteer dezelfde & verschillende cellen het volgende in:

1. Kies in de secties Waarden zoeken in en Volgens twee kolommen afzonderlijk die je wilt vergelijken.
2. Kies de optie Per rij.
3. Kies Zelfde waarde of Verschillende waarde naar wens.
4. Geef aan of je de geselecteerde waarden wilt kleuren en klik op OK.

Er verschijnt een dialoogvenster dat aangeeft hoeveel waarden zijn gevonden. Klik op OK om het venster te sluiten. Tegelijkertijd zijn de waarden geselecteerd; je kunt nu verwijderen, kopiëren of andere bewerkingen uitvoeren.

Als je de selectievakjes Achtergrondkleur invullen en Lettertypekleur invullen aanvinkt, ziet het resultaat er zo uit:

Voorbeeld3: Vergelijk twee kolommen in cellen en markeer dubbele of unieke gegevens

De functie Voorwaardelijke opmaak in Excel is krachtig; hiermee kun je twee kolommen in cellen vergelijken en vervolgens de verschillen of overeenkomsten markeren zoals je wilt.

1. Selecteer twee kolommen die je wilt vergelijken en klik op Start > Voorwaardelijke opmaak > Regels voor celopmaak markeren > Dubbele waarden.

2. In het pop-upvenster Dubbele waarden kies je een markeeropmaak uit de vervolgkeuzelijst.

3. Klik op OK. Nu zijn de duplicaten in beide kolommen gemarkeerd.

Voorbeeld4: Vergelijk twee kolommen in cellen en lijst exacte duplicaten in een andere kolom

Wil je de overeenkomende waarden in een andere kolom weergeven na het cel voor cel vergelijken van twee kolommen met hoofdlettergevoeligheid, dan kan onderstaande macro je helpen.

1. Activeer het werkblad waarin je twee kolommen wilt vergelijken en druk op Alt + F11 om het Microsoft Visual Basic for Applications-venster te openen.

2. Klik op Invoegen > Module in het Microsoft Visual Basic for Applications-venster.

3. Kopieer en plak onderstaande code in het nieuwe lege modulevenster.

VBA: Lijst duplicaten in aangrenzende kolom na het vergelijken van twee kolommen

Sub ExtendOffice_FindMatches()
'UpdatebyKutools
Dim xRg, xRgC1, xRgC2, xRgF1, xRgF2 As Range
Dim xIntSR, xIntER, xIntSC, xIntEC As Integer
On Error Resume Next
SRg:
Set xRgC1 = Application.InputBox("Select first column:", "Kutools for Excel", , , , , , 8)
If xRgC1 Is Nothing Then Exit Sub
If xRgC1.Columns.Count <> 1 Then
    MsgBox "Please select single column"
    GoTo SRg
End If
SsRg:
Set xRgC2 = Application.InputBox("Select the second column:", "Kutools for Excel", , , , , , 8)
If xRgC2 Is Nothing Then Exit Sub
If xRgC2.Columns.Count <> 1 Then
    MsgBox "Please select single column"
    GoTo SsRg
End If
Set xWs = xRg.Worksheet
For Each xRgF1 In xRgC1
    For Each xRgF2 In xRgC2
        If xRgF1.Value = xRgF2.Value Then xRgF2.Offset(0, 1) = xRgF1.Value
    Next xRgF2
Next xRgF1
End Sub

4. Druk op F5 om de code uit te voeren; er verschijnen twee dialoogvensters waarin je afzonderlijk twee kolommen selecteert. Klik vervolgens op OK > OK.

De overeenkomsten zijn automatisch in de rechterkolom van de twee kolommen weergegeven.

Vergelijk twee lijsten en haal overeenkomende gegevens op

Hier introduceren we twee verschillende scenario's voor het vergelijken van twee lijsten en het ophalen van gegevens.

• Voorbeeld1: Vergelijk twee kolommen en haal exact overeenkomende gegevens op
• Voorbeeld2: Vergelijk twee kolommen en haal gedeeltelijk overeenkomende gegevens op

Voorbeeld1: Vergelijk twee kolommen en haal exact overeenkomende gegevens op

Stel, je hebt twee tabellen en je wilt kolom B en kolom E vergelijken en vervolgens de bijbehorende prijzen uit kolom C ophalen en in kolom F plaatsen.

Hier worden enkele handige formules geïntroduceerd om deze taak op te lossen.

Gebruik in cel F2 (de cel waarin je de opgehaalde waarde wilt plaatsen) een van onderstaande formules:

=VLOOKUP(E2,$B$2:$C$8,2,0)

Of

=INDEX($B$2:$C$8,MATCH(E2,$B$2:$B$8,0),2)

Druk op Enter; de eerste waarde is gevonden. Sleep vervolgens de vulgreep naar cel F6 om alle waarden op te halen.

Voorbeeld2: Vergelijk twee kolommen en haal gedeeltelijk overeenkomende gegevens op

Als er kleine verschillen zijn tussen de twee te vergelijken kolommen zoals hieronder, werken bovenstaande methoden niet.

Gebruik in cel F2 (de cel waarin je de opgehaalde waarde wilt plaatsen) een van onderstaande formules:

=VLOOKUP("*"&E2&"*",$B$2:$C$8,2,0)

Of

=INDEX($B$2:$C$8,MATCH("*"&E2&"*",$B$2:$B$8,0),2)

Druk op Enter; de eerste waarde is gevonden. Sleep vervolgens de vulgreep naar cel F6 om alle waarden op te halen.

Vergelijk twee kolommen en vind ontbrekende gegevenspunten

Stel, je hebt twee kolommen: kolom B is langer en kolom C is korter, zoals hieronder. Hoe vind je de ontbrekende gegevens in kolom C ten opzichte van kolom B?

• Voorbeeld1: Vergelijk twee kolommen en vind de ontbrekende gegevenspunten
• Voorbeeld2: Vind de ontbrekende gegevenspunten en lijst ze in een andere kolom (met een handig hulpmiddel)
• Voorbeeld3: Vergelijk twee kolommen en lijst ontbrekende gegevens hieronder

Voorbeeld1: Vergelijk twee kolommen en vind de ontbrekende gegevenspunten

Als je alleen wilt vaststellen welke gegevens ontbreken na het vergelijken van twee kolommen, kun je een van onderstaande formules gebruiken:

=ISERROR(VLOOKUP(B2,$C$2:$C$10,1,0))

Of

=NOT(ISNUMBER(MATCH(B2,$C$2:$C$10,0)))

Druk op Enter en sleep de vulgreep naar cel D10. Als de gegevens in zowel kolom B als kolom C staan, retourneert de formule Nee; als de gegevens alleen in kolom B staan en ontbreken in kolom C, retourneert de formule Ja.

Voorbeeld2: Vind de ontbrekende gegevenspunten en lijst ze in een andere kolom (met een handig hulpmiddel)

Wil je na het vergelijken van twee kolommen verdere acties uitvoeren op de ontbrekende gegevens, zoals het opsommen van de ontbrekende gegevens in een andere kolom of het aanvullen van de ontbrekende gegevens onder de kortere kolom, probeer dan het handige hulpmiddel Selecteer dezelfde & verschillende cellen van Kutools voor Excel.

Na het installeren van Kutools voor Excel klik je op Kutools > Selecteren > Selecteer dezelfde & verschillende cellen. Stel in het dialoogvenster Selecteer dezelfde & verschillende cellen het volgende in:

1. Kies in de sectie Waarden zoeken de langere kolom die de volledige lijst bevat.
2. Kies in de sectie Volgens de kortere kolom waarin gegevens ontbreken.
3. Kies de optie Per rij.
4. Kies de optie Verschillende waarde. Klik op OK.

Er verschijnt een dialoogvenster dat aangeeft hoeveel ontbrekende gegevens zijn gevonden. Klik op OK om het te sluiten. De ontbrekende gegevens zijn nu geselecteerd.

Je kunt nu op Ctrl + C drukken om de geselecteerde ontbrekende gegevens te kopiëren en ze met Ctrl + V onder de kortere kolom of in een nieuwe kolom plakken, afhankelijk van je behoefte.

Voorbeeld3: Vergelijk twee kolommen en lijst ontbrekende gegevens hieronder

Wil je de ontbrekende gegevens onder de kortere kolom opsommen na het vergelijken van twee kolommen, dan kan de INDEX-arrayformule je helpen.

Typ in de cel onder de kortere kolom, bijvoorbeeld cel C7, onderstaande formule:

=INDEX($B$2:$B$10,MATCH(TRUE,ISNA(MATCH($B$2:$B$10,$C$2:C6,0)),0))

Druk op Shift + Ctrl + Enter om de eerste ontbrekende gegevens te krijgen en sleep vervolgens de vulgreep naar beneden tot de foutwaarde #N/B verschijnt.

Daarna kun je de foutwaarde verwijderen en zijn alle ontbrekende gegevens onder de kortere kolom weergegeven.

Vergelijk twee kolommen met wildcards

Stel, je hebt een lijst met gegevens in kolom B en je wilt het aantal cellen tellen dat “Apple” of “Candy” bevat in kolom D, zoals hieronder:

Om te tellen of een cel één of meer waarden bevat, kun je een formule met wildcards gebruiken om dit probleem op te lossen.

=SUM(COUNTIF(B2,"*" & $D$2:$D$3 & "*"))

Druk op Shift + Ctrl + Enter om de eerste controle uit te voeren en sleep vervolgens de vulgreep naar cel F8.

Wil je het totale aantal cellen tellen dat de waarden uit kolom D bevat, gebruik dan de formule onder cel F8:

Vergelijk twee kolommen (datums) op groter dan of kleiner dan

Als je twee kolommen met datums hebt zoals hieronder, wil je misschien weten welke datum later is in dezelfde rij.

• Voorbeeld1: Vergelijk twee kolommen op groter dan of kleiner dan
• Voorbeeld2: Vergelijk twee kolommen op groter dan of kleiner dan en formatteer

Voorbeeld1: Vergelijk twee kolommen op groter dan of kleiner dan

Je kunt een eenvoudige formule gebruiken om snel te bepalen of datum1 later is dan datum2 in elke rij.

=IF(B2>C2,"Yes","No")

Druk op Enter om het eerste vergelijkingsresultaat te krijgen en sleep vervolgens de vulgreep naar cel C6 om alle resultaten te krijgen.

Voorbeeld2: Vergelijk twee kolommen op groter dan of kleiner dan en formatteer

Wil je de cellen in kolom Datum1 markeren als deze groter zijn dan Datum2, dan kun je de functie Voorwaardelijke opmaak in Excel gebruiken.

1. Selecteer de datums in kolom B (Datum1) en klik op Start > Voorwaardelijke opmaak > Nieuwe regel.

2. Selecteer in het dialoogvenster Nieuwe opmaakregel de optie Gebruik een formule om te bepalen welke cellen moeten worden opgemaakt in het gedeelte Regeltype selecteren en typ de formule

 =$B2>$C2

in het tekstvak van Waarden opmaken waarvoor deze formule waar is.

=$B2<$C2.

3. Klik op de knop Opmaak om het dialoogvenster Cellen opmaken te openen en kies het gewenste opmaaktype. Klik op OK > OK.

Nu zijn de cellen in kolom Datum1 die groter zijn dan die in kolom Datum2 gemarkeerd.

Vergelijk twee kolommen en tel overeenkomsten of verschillen

Onderstaande gegevensset is een voorbeeld voor het vergelijken en tellen van overeenkomsten of verschillen.

Met de SOMPRODUCT-formule kun je snel het aantal overeenkomsten in twee kolommen tellen.

=SUMPRODUCT(--(ISNUMBER(MATCH(B2:B8,C2:C8,0))))

Druk op Enter om het resultaat te krijgen.

Voor meer methoden om overeenkomsten en verschillen te tellen, bezoek deze pagina: Alle overeenkomsten / duplicaten tellen tussen twee kolommen in Excel

Vergelijk twee bereiken

Nu weet je hoe je twee kolommen kunt vergelijken na het lezen van bovenstaande methoden. In sommige gevallen wil je echter twee bereiken (twee reeksen met meerdere kolommen) vergelijken. Je kunt bovenstaande methoden (de formules of voorwaardelijke opmaak) gebruiken om ze kolom voor kolom te vergelijken, maar hier wordt een handig hulpmiddel geïntroduceerd – Kutools voor Excel – waarmee je deze taak in verschillende gevallen snel en zonder formules kunt uitvoeren.

• Voorbeeld1: Vergelijk twee bereiken per cel
• Voorbeeld2: Vergelijk twee bereiken als de gegevens in dezelfde volgorde staan

Voorbeeld1: Vergelijk twee bereiken per cel

Hier zijn twee bereiken die per cel vergeleken moeten worden; je kunt hiervoor de functie Selecteer dezelfde & verschillende cellen van Kutools voor Excel gebruiken.

Na het gratis installeren van Kutools voor Excel klik je op Kutools > Selecteren > Selecteer dezelfde & verschillende cellen. Stel in het pop-upvenster Selecteer dezelfde & verschillende cellen het volgende in:

1. Kies in de sectie Waarden zoeken in het bereik waarin je overeenkomsten of verschillen wilt vinden na het vergelijken van twee bereiken.
2. Kies in de sectie Volgens het andere bereik waarmee je wilt vergelijken.
3. Kies in de sectie Methode de optie Per cel.
4. Kies in de sectie Zoeken het type cellen dat je wilt selecteren of markeren.
5. In de sectie Verwerking van geselecteerde resultaten kun je de cellen markeren door achtergrondkleur of lettertypekleur in te vullen. Wil je niet markeren, vink dan de selectievakjes niet aan. Klik op OK.

Er verschijnt een dialoogvenster dat aangeeft hoeveel cellen/rijen zijn geselecteerd. Klik op OK om het te sluiten.

• Unieke waarden selecteren en markeren
  
• Dubbele waarden selecteren en markeren
  

Voorbeeld2: Vergelijk twee bereiken als de gegevens in dezelfde volgorde staan

Stel, het bereik F2:H7 is een model en je wilt controleren of de gegevens in bereik B2:D7 in de juiste volgorde staan ten opzichte van F2:H7.

In dit geval kan de functie Cellen vergelijken van Kutools voor Excel je helpen.

Na het gratis installeren van Kutools voor Excel klik je op Kutools > Cellen vergelijken. Stel in het dialoogvenster Cellen vergelijken het volgende in:

1. Kies de twee bereiken afzonderlijk in de vakken Waarden zoeken in en Volgens.
2. Kies het celtype dat je wilt markeren in de sectie Zoeken.
3. Kies het type markering in de sectie Verwerking van geselecteerde resultaten. Klik op OK.

Er verschijnt een dialoogvenster dat aangeeft hoeveel cellen zijn geselecteerd. Klik op OK om het te sluiten. Nu zijn de cellen die verschillen van die in het andere bereik geselecteerd en gemarkeerd.

De bovenstaande informatie legt uit hoe je kolommen in Excel kunt vergelijken. Hopelijk vind je het waardevol en nuttig. Voor meer onmisbare Excel-tips en -trucs die je gegevensverwerking kunnen verbeteren, lees verder hier.