Een van mijn vrienden werkt bij een analysebedrijf voor de gezondheidszorg. Hij heeft vaak contact met mij over enkele van de echte problemen waarmee hij wordt geconfronteerd tijdens het werken met gegevens in Excel.
Vaak converteer ik zijn zoekopdrachten naar Excel-zelfstudies op deze site, omdat het ook nuttig kan zijn voor mijn andere lezers.
Dit is ook zo'n tutorial.
Mijn vriend belde me vorige week met het volgende probleem:
Er zijn adresgegevens in een kolom in Excel en ik wil cellen identificeren / filteren waar het adres dubbele tekstreeksen (woorden) bevat.
Hier is de vergelijkbare dataset waarin hij cellen wilde filteren die een dubbele tekststring bevatten (die met rode pijlen):
Wat dit nu moeilijk maakt, is dat er geen consistentie is in deze gegevens. Aangezien dit een compilatie is van een dataset die handmatig is gemaakt door verkopers, kunnen er variaties in de dataset zijn.
Overweeg dit:
- Elke tekenreeks kan in deze dataset worden herhaald. Het kan bijvoorbeeld de naam van het gebied zijn of de naam van de stad of beide.
- De woorden worden gescheiden door een spatie en er is geen consistentie in het feit of de plaatsnaam er al zou zijn na zes of acht woorden.
- Er zijn duizenden records zoals deze, en de noodzaak is om die records te filteren waar dubbele tekststrings voorkomen.
Na veel opties te hebben overwogen (zoals tekst naar kolommen en formules), besloot ik uiteindelijk VBA te gebruiken om dit voor elkaar te krijgen.
Dus ik heb een aangepaste VBA-functie gemaakt ('IdDuplicate') om deze cellen te analyseren en me TRUE te geven als er een duplicaat woord in de tekstreeks is, en FALSE voor het geval er geen herhalingen zijn (zoals hieronder weergegeven):
Deze aangepaste functie analyseert elk woord in de tekenreeks en controleert hoe vaak het in de tekst voorkomt. Als de telling meer dan 1 is, wordt WAAR geretourneerd; anders wordt FALSE geretourneerd.
Het is ook gemaakt om alleen woorden van meer dan drie tekens te tellen.
Zodra ik de TRUE/FALSE-gegevens heb, kan ik gemakkelijk alle records filteren die WAAR zijn.
Ik zal u nu laten zien hoe u dit in Excel kunt doen.
VBA-code voor de aangepaste functie
Dit wordt gedaan door een aangepaste functie in VBA te maken. Deze functie kan vervolgens worden gebruikt als elke andere werkbladfunctie in Excel.
Hier is de VBA-code ervoor:
Function IdDuplicates(rng As Range) As String Dim StringtoAnalyse As Variant Dim i As Integer Dim j As Integer Const minWordLen As Integer = 4 StringtoAnalyze = Split(UCase(rng.Value), " ") For i = UBound(StringtoAnalyze) To 0 Stap -1 If Len(StringtoAnalyze(i)) < minWordLen Then GoTo SkipA For j = 0 To i - 1 If StringtoAnalyze(j) = StringtoAnalyze(i) Then IdDuplicates = "TRUE" GoTo SkipB End If Next j SkipA: Next i IdDuplicates = "FALSE" SkipB: functie beëindigen
Bedankt Walter voor het voorstellen van een betere benadering van deze code!
Hoe deze VBA-code te gebruiken?
Nu u de VBA-code hebt, moet u deze in de backend van Excel plaatsen, zodat deze als een gewone werkbladfunctie kan werken.
Hieronder staan de stappen om de VBA-code op de backend te plaatsen:
- Ga naar het tabblad Ontwikkelaars.
- Klik op Visual Basic (je kunt ook de sneltoets ALT + F11 gebruiken)
- Klik in de VB Editor-backend die wordt geopend met de rechtermuisknop op een van de werkmapobjecten.
- Ga naar 'Invoegen' en klik op 'Module'. Hiermee wordt het moduleobject voor de werkmap ingevoegd.
- Kopieer en plak in het venster Modulecode de hierboven genoemde VBA-code.
Zodra u de VBA-code in de backend hebt, kunt u de functie - 'IdDuplicates' gebruiken zoals elke andere normale werkbladfunctie.
Deze functie heeft één enkel argument nodig, namelijk de celverwijzing van de cel waar u de tekst hebt.
Het resultaat van de functie is TRUE (als er dubbele woorden in staan) of FALSE (als er geen duplicaten zijn). Zodra u deze lijst met WAAR/ONWAAR hebt, kunt u die met WAAR filteren om alle cellen te krijgen die dubbele tekstreeksen bevatten.
Opmerking: ik heb de code alleen gemaakt om rekening te houden met die woorden die meer dan drie tekens lang zijn. Dit zorgt ervoor dat als er woorden van 1, 2 of 3 tekens lang zijn (zoals 12 A, K G M of L D A) in de tekenreeks, deze worden genegeerd bij het tellen van de duplicaten. Als je wilt, kun je dit eenvoudig wijzigen in de code.
Deze functie is alleen beschikbaar in de werkmap waar je de code in de module hebt gekopieerd. Als u wilt dat dit ook in andere werkmappen beschikbaar is, moet u deze code in die werkmappen kopiëren en plakken. Als alternatief kunt u ook een invoegtoepassing maken (waardoor deze functie beschikbaar zou zijn in alle werkmappen op uw systeem).
Vergeet ook niet om deze werkmap op te slaan in de extensie .xlsm (omdat deze een macrocode bevat).
