ZoekDir-macro

 

Dubbele gegevens in een bereik markeren

 

Verschillen in twee kolommen met tekst

 

Welke teksten zijn aan elkaar gelijk en welke niet. En als je regels invoegt, klopt dan de formule nog. In dit blog leg ik een mogelijkheid uit waarbij het verschuiven van tekst geen invloed heeft op de controle-formule.

Je hebt twee tekstbestanden, bijvoorbeeld twee Word-bestanden die op elkaar lijken maar niet precies gelijk zijn. Of twee macro's die op elkaar lijken maar toch verschillen. Hoe zie je dan snel te verschillen.

De oplossing:

=CEL("inhoud";INDIRECT(ADRES(RIJ();2)))=CEL("inhoud";INDIRECT(ADRES(RIJ();4)))

De uitleg:

De eenvoudige oplossing is door beide teksten elk in een kolom te plaatsen en er tussen een formule te zetten die controleert of de tekst in de ene kolom gelijk is aan de andere kolom op dezelfde rij. In bovenstaand voorbeeld zou je dan in cel C2 de formule =B2=D2 kunnen plakken. Dit is de meest eenvoudige ALS-formule die je kunt bedenken. Want als de tekst in cel B2 gelijk is aan de tekst in cel D2 dan geeft deze formule als antwoord WAAR. Zijn ze niet gelijk, dan geeft de formule ONWAAR.
Simpel en effectief zou je zeggen. en dat is juist. Zo heb ik lange tijd teksten vergeleken.

Maar als je naar bovenstaand voorbeeld kijkt, dan zie je dat de tekst in kolom D extra regels heeft en dat de tekst in cel B7 gelijk is aan de tekst in cel D11. Dus in kolom D zijn extra regels toegevoegd.

Als je de cellen B7:B10 selecteert en je voegt hier regels in (Cntrl-+ > Cellen naar beneden verplaatsen) Dan komt wat in de afbeelding nog p cel B7 staat op cel B11 te staan en dan is B11 weer gelijk aan D11. Maar de formule zegt ONWAAR. Dat komt omdat de formule meegaat met de verplaatsing. Dus in cel C7 waar eerst =B7=D7 stond, staat nu =B11=D7. En dat wil je niet.
Een oplossing is om cel C6 te selecteren en die formule omlaag te kopiëren. Dan zijn de formules weer correct. Maar bij veel verschillen moet je steeds opnieuw de formules aanpassen en dat is tijdrovend.

Er moet dus een formule gemaakt worden die niet mee "schuift" met het invoegen van rijen. Dus de directe celverwijzing (zoals =B7) moet vervangen worden door een indirecte celverwijzing die altijd blijft kijken naar de inhoud in kolom B op de betreffende rij.

En dat kan met de functie RIJ(). =RIJ() geeft de rij weer van de cel waarin deze functie wordt gebruikt. En deze RIJ-functie kun je gebruiken in de functie =ADRES(rij;kolom). Deze functie geeft de celverwijzing weer van het rij- en het kolomnummer. =ADRES(2;3) geeft als antwoord $C$2. Dus als ik in cel C7 de formule =ADRES(RIJ();2) invoer, dan krijg ik als antwoord $B$7.
Nu wil ik niet de celadressen met elkaar vergelijken maar de inhoud van de betreffende cellen. En daarvoor heeft Excel de functie =CEL(). Met =CEL() kun je eerst aangeven wat je wilt weten over een cel en daarna kun je de betreffende cel opgeven. Zo kun je de inhoud opvragen van een cel. Dus =CEL("inhoud";A1) geeft weer wat er in cel A1 staat vermeld. En dat zouden we kunnen gebruiken door de inhoud van twee cellen te vergelijken. Maar als we in cel C2 de formule = CEL("inhoud";B2)=CEL("inhoud";D2) zouden plaatsen dan verschuift de formule met het invoegen van rijen net zoals =B2=D2 verschuift bij het invoegen.
De formule =CEL("inhoud";ADRES(RIJ();2))=CEL("inhoud";ADRES(RIJ();4)) werkt helaas niet. Maar als we eerst het ADRES-gedeelte omzetten naar een uitkomst, dan werkt het wel. En dat kan gelukkig met de INDIRECT()-functie.
Daarmee kunnen we de volgende formule bouwen:

=CEL("inhoud";INDIRECT(ADRES(RIJ();2)))==CEL("inhoud";INDIRECT(ADRES(RIJ();4))). Door deze formule omlaag te kopiëren kun je zowel links in kolom B als rechts in kolom D zoveel rijen invoegen of verwijderen als je wilt, de formule zal blijven kijken naar dezelfde gegevens op dezelfde rij.

Zoeken op meerdere kolommen met één formule

 Met de dynamische functie Filter kunnen gegevens gefilterd worden op ingevoerde zoekgegevens. Maar er kan ook mee eenvoudig gezocht worden in meerdere kolommen.

Zoeken in meerdere kolommen met Voorwaardelijke opmaak

Naar aanleiding van een video van MVP Deb Ashby vroeg ik me af of je met het invoeren van een paar letters Excel laten zoeken in meerdere kolommen van een tabel en de achtergrond van de hele rij laten wijzigen? Na wat testen bleek dat het mogelijk is met een wat uitgebreidere formule in de voorwaardelijke opmaak.

In bovenstaand voorbeeld kan in cel H3 een tekst ingevoerd worden en als die tekst aanwezig is in de kolom Voornaam, Achternaam of Plaats, dan zal de hele rij een gele achtergrond krijgen.

Keuze-/Dropdownlijst met alleen de nog niet gebruikte gegevens

Soms kom je een oplossing tegen die zo mooi en zo simpel is om te doen, dat je die graag wilt bewaren voor het geval je het eens nodig denkt te hebben. En daarvoor heb ik deze blog opgezet. Daarom hoort deze hier zeker vermeld te worden.

Hoe vind je alle verschillen in twee tabellen?

 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.