Twee simpele tabellen die op elkaar lijken. Maar er zitten vijf verschillen in.
Hoe vind je met één formule de verschillen?
Erik Guzik en Charles Devlin hebben mij twee oplossingen gestuurd.
Erik Guzik en Charles Devlin hebben mij twee oplossingen gestuurd.
Hoewel het in de afbeelding twee tabellen lijken, zijn het in werkelijkheid twee bereiken. Maar dat maakt voor deze vraag geen verschil.
In kolom B staat alleen tekst en in kolom C zijn de getallen als getallen ingevoerd.
De tabel aan de rechterkant is een kopie van links maar ik het een aantal zaken gewijzigd:
In cel E3 staat een formule die verwijst naar cel B3
In cel E3 staat een formule die verwijst naar cel B3
In cel E6 staat een formule die twee tekstgedeeltes samenvoegd
In cel E7 staat er een spatie achter de naam die niet in cel B7 staat
In cel F4 staat er een apostrof voor het cijfer 2 en dus is dit tekst geworden die rechts uitgelijnd is.
In cel F6 staat geen getal maar een formule.
Voorwaardelijke opmaak
Met voorwaardelijke opmaak kun je eenvoudig vergelijken wat gelijk is aan elkaar of wat juist verschilt.
Selecteer de gegevens in de cellen B3:C7, houdt de Ctrl-toets ingedruk en selecteer de cellen E3:F7.
Selecteer de gegevens in de cellen B3:C7, houdt de Ctrl-toets ingedruk en selecteer de cellen E3:F7.
Kies dan uit het Start-lint Voorwaardelijke opmaak > Markeringsregels voor cellen > Dubbele waarden
Je kunt zoeken naar dubbele waarden maar ook naar unieke waarden waarmee je dus snel ziet wat niet gelijk aan elkaar is.
Helaas, Het vind alleen de spatie in E7 als verschil met B7, de andere verschillen worden niet opgemerkt.
Selecteren-speciaal: verschil in rijen
Doordat iemand op LinkedIn een sneltoets vermeldde die niet in de nederlandse versie van Excel werkt, kwam ik er, dankzij de tip van Jan-Karel Pieterse, achter dat er een andere mogelijkheid is om gegevens te vergelijken.
Selecteer de beide Naam-kolommen en kies uit het Startlint Zoeken > Speciaal zoeken... > Verschillen in rij.
Selecteer de beide Naam-kolommen en kies uit het Startlint Zoeken > Speciaal zoeken... > Verschillen in rij.
In de Naam-kolom worden de verschillen gevonden, in de Bedrag-kolom wordt wel cel F6 gevonden maar niet de tekst in cel F4.
Waar/Onwaar
De door mij veel gebruikte methode om verschillen te vinden is met behulp van de =-formule. (Dit noem ik de Als-formule is zijn meest basale vorm).
In cel H3 plaats ik de formule =B3=E3 waarna Excel mij verteld of ze gelijk aan elkaar zijn of niet.
En zoals je hierboven ziet, het vind niet het verschil in E3, E6 en F6.
Oplossing 1: Voorwaardelijke opmaak
Via de Facebook-groep Excel for Freelancers kwam Erik Guzik met een oplossing die een oplossing gaf voor deze tabel. Je kunt het in een voorwaardelijke opmaak zetten of als een formule in de tabel ernaast: Zijn formule is: =(NIET(B3:C7=E3:F7))+ISFORMULE(F3:G7).
Dat werkt in dit voorbeeld wel maar niet als er bijvoorbeeld in de linkertabel een formule staat en in de rechtertabel niet.
Na wat verder expirimenteren met de formule van Erik kwam ik tot de slotsom dat onderstaande formule werkt:
=(NIET(B3:C7=E3:F7)+(ISFORMULE(B3:C7)<>ISFORMULE(E3:F7))>0)
Dat werkt in dit voorbeeld wel maar niet als er bijvoorbeeld in de linkertabel een formule staat en in de rechtertabel niet.
Na wat verder expirimenteren met de formule van Erik kwam ik tot de slotsom dat onderstaande formule werkt:
=(NIET(B3:C7=E3:F7)+(ISFORMULE(B3:C7)<>ISFORMULE(E3:F7))>0)
En de NIET()-functie kan gewijzigd worden in de ongelijk-tekens waardoor de formule gewijzigd kan worden in:
=(B3:C7<>E3:F7)+(ISFORMULE(B3:C7)<>ISFORMULE(E3:F7))>0
Deze formule kan er met een LET-functie zo uit zien:
=LET(Mijntbl1;DubbelklikTabel1;Mijntbl2;DubbelklikTabel2;ALS(((ISFORMULE(Mijntbl1)<>ISFORMULE(Mijntbl2))*1)+((Mijntbl1<>Mijntbl2)*1)>0;"FOUT";""))
De voordelen van een LET-functie is dat je maar één keer elke tabel hoeft te selecteren. Dubbelklik op de tekst "DubbelklikTabel1" en selecteer dan de eerste tabel. Idem voor de tweede tabel.
=LET(Mijntbl1;DubbelklikTabel1;Mijntbl2;DubbelklikTabel2;ALS(((ISFORMULE(Mijntbl1)<>ISFORMULE(Mijntbl2))*1)+((Mijntbl1<>Mijntbl2)*1)>0;"FOUT";""))
De voordelen van een LET-functie is dat je maar één keer elke tabel hoeft te selecteren. Dubbelklik op de tekst "DubbelklikTabel1" en selecteer dan de eerste tabel. Idem voor de tweede tabel.
Oplossing 2: Zelf gedefinieerde functie (UDF) in VBA
Charles Devlin heeft in dezelfde Facebook-groep een andere oplossing aangedragen. In Excel is het mogelijk om zelf formules samen te stellen via VBA. Met zijn functie =CompareCells(ce11;cel2) laat hij zien wat de verschillen zijn.
De functie die hij hiervoor in VBA schreef is de volgende:
Function CompareCells(Cell_1 As Variant, Cell_2 As Variant) As String
If (Cell_1.Formula & Cell_1.PrefixCharacter) = (Cell_2.Formula & Cell_2.PrefixCharacter) Then
Else
CompareCells = Cell_1.PrefixCharacter & IIf(Right(Cell_1.Formula, 1) = Chr(32), Cell_1.Formula & "[space]", Cell_1.Formula) & " " & ChrW(8800) & " " & Cell_2.PrefixCharacter & IIf(Right(Cell_2.Formula, 1) = Chr(32), Cell_2.Formula & "[space]", Cell_2.Formula)
End If
End Function
Mocht je een verschil in twee tabellen (of bereiken) hebben die niet met deze formule wordt opgelost, laat het dan weten via het contactformulier.
