Vergelijken celinhoud met tabel

[MarinusPJ]

Hoi allen,

Ik heb een Excel bestand dat gegevens uit 1 informatiesysteem krijgt waarmee het andere systeem weer gevuld kan worden. Beiden systemen sluiten natuurlijk niet goed op elkaar aan.

Op tabblad 'INVULBLAD AANMELDEN' staat in kolom A de herkomst van klanten. De inhoud van deze kolom wordt overgenomen uit een informatiesysteem dat door anderen niet altijd even netjes gevuld wordt, we zien bijvoobeeld:
Tunesië Tunis (land met stad)
(lege cel)
Waalwijk (geen land, wel een stad)

De volgorde is gelukkig wel altijd hetzelfde, het land staat altijd voor de stad. Bij klanten uit Nederland staat alleen een stad.

Op tabblad 'TABELLEN' staat een tabel met alle landen van de wereld.

Ik wil op tabblad 'ITBC EXPORT' een kolom die het resultaat toont van het vergelijk tussen kolom A op tabblad 'INVULBLAD AANMELDEN' en de inhoud van de tabel op tabblad 'TABELLEN'. Het resultaat zou dan moeten zijn:
Tunesië
(lege cel) mag ook foutmelding zijn
Nederland

Wie maakt mij en onze secretaresses heel gelukkig?

Groeten,
MarinusPJ

 

[Haije]

Begin eens met het posten van een voor zelf excelbestand

 

[VenA]

Ik zou het informatiesysteem aan laten passen. S h i t in is s h i t out. een land en een plaats zijn 2 verschillende entiteiten in een database.

 

[MarinusPJ]

@VenA: helemaal gelijk, maar niemand wil bewegen om aanpassingen te doen. "We nemen het mee in de lijst". Kortom: hou je adem maar niet in...
Het voorbeeld is toegevoegd, ik hoop dat dit wat verduidelijkt. Een aantal labels heb ik geanonimiseerd.

Groeten,
MarinusPJ

 

[VenA]

Met een UDF kom ik een heel eind

Function Geboorteland(r1 As Range, r2 As Range)
  ar = r2
  For j = 1 To UBound(ar)
    If InStr(1, r1, ar(j, 1), vbTextCompare) Then
      If LCase(Split(ar(j, 1))(0)) = LCase(Split(r1)(0)) Then
        c00 = ar(j, 1)
        Exit For
      End If
    End If
  Next j
  Geboorteland = IIf(c00 <> "", c00, "Nederland")
End Function

Met in Z2 en verder doorslepen naar beneden.

=IF(Y2="";"";Geboorteland(Y2;Tabellen!$D$3:$D$389))

Zorg er wel voor dat alle landen in de landentabel voorkomen anders krijg je als standaardresultaat Nederland. (zie o.a. rij 34)

 

[MarinusPJ]

Super, ik ga er gelijk naar kijken als ik weer kan inloggen op het werk, als ie 't goed doet zet ik de vraag daarna op opgelost.

Alvast bedankt.

Groeten,
MarinusPJ

 

[MarinusPJ]

Ik was te nieuwsgierig om tot zaterdag te wachten en ben thuis maar ingelogd.

Ik krijg 'm helaas niet aan de praat. De UDF heb ik in eerste instantie via de VBA editor ingevoerd op Blad 4 en de formule in cel N2 op tabblad iTBC_Export. De formule geeft dan de foutmelding #NAAM (de formule bevat tekst die niet wordt herkend). Toen zag ik dat de formule verwees naar Y2 in hetzelfde werkblad en heb ik de formule aangepast in

=if('Invulblad aanmelden'!Y2="";"";Geboorteland('Invulblad aanmelden'!Y2;Tabellen!$D$3:$D$389)) en heb ik
=als('Invulblad aanmelden'!Y2="";"";Geboorteland('Invulblad aanmelden'!Y2;Tabellen!$D$3:$D$389)) geprobeerd.

Ook daar bleef de #NAAM fout verschijnen.
Toen heb ik de UDF nog in verschillende Bladen geprobeerd, maar helaas.

Wat doe ik niet goed?

 

[VenA]

De UDF moet in een aparte module komen te staan dus niet in een bladmodule.

 

[MarinusPJ]

Fijn dat je het zo helder uitlegt! Het tabblad met de formule wordt wekelijks overschreven, met een aanpassing naar het brontabblad heb ik 'm verwerkt in het tabblad iTBC_Export. Ik heb ook het celbereik waarin gezocht moet worden als een tabel vormgegeven en dit in de formule verwerkt, wie weet worden er nieuwe landen geboren. Het werkt als een zonnetje.:thumb:

Nog een vraag voor de finetuning. In de UDF staat Onbekend. Daarmee wordt volgens mij bedoeld dat wanneer de formule geen land herkent in de landentabel dat er dan Onbekend moet komen te staan. Nu zie ik echter Nederland verschijnen als er een foutje is gemaakt in het aangeleverde bestand zoals GEORGiIË Khulo i.p.v. GEORGIË Khulo. Het zou mooi zijn als ik Onbekend zou zien, dan kan ik dit extra accentueren met voorwaardelijke opmaak.

Groeten,
MarinusPJ

 

[VenA]

Het meest eenvoudig is om een extra tabel te gebruiken met alle plaatsnamen in Nederland.

Eerst daarop vergelijken en bij geen match naar de landen tabel.

Function Geboorteland(r1 As Range, r2 As Range, r3 As Range)
  If IsNumeric(Application.Match(r1.Value, r3, 0)) Then
    Geboorteland = "Nederland"
    Exit Function
  End If
  ar = r2
  For j = 1 To UBound(ar)
    If InStr(1, r1, ar(j, 1), vbTextCompare) Then
      If LCase(Split(ar(j, 1))(0)) = LCase(Split(r1)(0)) Then
        c00 = ar(j, 1)
        Exit For
      End If
    End If
  Next j
  Geboorteland = IIf(c00 <> "", c00, "onbekend")
End Function

In het voorbeeld alleen de relevante tabbladen al werk jij met een andere. (is allemaal verwarrend en maakt van het bestand geen voorbeeldbestand)

 

[HSV]

Of:

Function Geboorteland(r1 As Range, r2 As Range, r3 As Range)  ar = r2
  sq = Split(r1.Value)
     If UBound(sq) > 0 Then
       If IsNumeric(Application.Match(sq(0) & " " & sq(1), r2, 0)) Then
         c00 = sq(0) & " " & sq(1)
        ElseIf IsNumeric(Application.Match(sq(0), r2, 0)) Then
         c00 = sq(0)
        End If
      Else
       If IsNumeric(Application.Match(r1.Value, r3, 0)) Then c00 = "Nederland"
      End If
  Geboorteland = IIf(c00 <> "", c00, "onbekend")
End Function

 

[VenA]

@HSV,
Is opzich een leuke en snellere oplossing maar werkt maar ten dele. Het gaat fout bij plaatsnamen met een spatie erin en fout met landen waarin meerdere spaties staan.

 

[MarinusPJ]

Ja, dat lijkt 'm helemaal te zijn. Hartstikke bedankt VenA! Nu nog kijken of ik maandag getrakteerd wordt door secretaresses.

Ik denk dat ik je nog wel wat toelichting schuldig ben. Het lijkt allemaal fors omslachtig wat we doen.
Mijn afdeling is een klein onderdeel van een heel groot bedrijf. Dat grote bedrijf heeft een administratiesysteem (A) dat 4/dag informatie dumpt naar het specialistische en beperkt toegankelijke administratiesysteem (B) waar mijn afdeling mee werkt.
In systeem A staan geboortestad en -land keurig in aparte velden, bij de dump naar systeem B worden deze variabelen echter in één veld weggeschreven in systeem B.
Een van mijn ketenpartners heeft delen van systeem B nodig om samen met andere data te verwerken in zijn administratiesysteem (C).
De resultaten van de query van systeem B geven een aantal variabelen die deels overeenkomen met de wensen van systeem C. De gepresenteerde volgorde van kolommen is in beide systemen anders. Om nou te voorkomen dat er per abuis data worden overschreven, hebben we gekozen voor een invoerblad en een volledig beveiligd exportblad. Het invoerblad wordt wekelijks ververst.

Voor mij klinkt 't inmiddels logisch, maar ik kan me heel goed voorstellen dat een buitenstaander hier vreemd van staat te kijken.

Nogmaals bedankt,
MarinusPJ

 

[VenA]

Nu nog kijken of ik maandag getrakteerd wordt door secretaresses.

Volgens mij zijn jullie compleet de weg kwijt bij het Ministerie van Veiligheid en Justitie.
De enigen die getrakteerd moet worden op secretaresses zijn Haije, HSV en de schrijver van dit bericht.

 

[HSV]

@VenA, die code liet inderdaad niet het gewenst resultaat zien.

Hierbij een code met een lusje die gebaseerd is op ubound(split(r1)).
De snelheid heb ik niet gemeten.

Function Geboorteland(r1 As Range, r2 As Range, r3 As Range)
    ar = r2
    s0 = r1
    sq = Split(s0)
  If IsNumeric(Application.Match(r1.Value, r3, 0)) Then
    Geboorteland = "Nederland"
   Exit Function
  End If
    For i = UBound(sq) To 0 Step -1
        If InStr(1, Join(Application.Transpose(ar), "|"), s0, 1) Then
           x = Application.Match(s0, ar, 0)
            If IsNumeric(x) Then c00 = ar(Application.Match(s0, ar, 0), 1)
        End If
      If Len(s0) > Len(sq(i)) Then s0 = Mid(s0, 1, Len(s0) - Len(sq(i)) - 1)
   Next i
 Geboorteland = IIf(c00 <> "", c00, "onbekend")
End Function

Ps. waarom heb je de UDF afgesloten met Ctrl+Shift+Enter?
Komt dat zoals je hier heb aangekondigd?
https://www.helpmij.nl/forum/showthread.php/951727-gegevens-in-cel-splitsen

 

[HSV]

@cow18 (Bart),

Keurig! :thumb:

Ik heb de Filter methode ook nog even geprobeerd, maar was er weer vanaf geweken.
Wat me opvalt is de optionele 'Include'.

 sn = Filter(Application.Transpose(r2), sq(0), 1, vbTextCompare)

Daar gebruik je de True of False in (-1 of 0), maar jij gebruikt de 1 die ook werkt.
Zelfs met een 2 werkt het nog
Ieder ander getal dan 0 of zelfs weggelaten is zo te merken altijd 'True'.

 sn = Filter(Application.Transpose(r2), sq(0), , vbTextCompare)

Ik kan er niets over vinden in de Help, maar het viel me op dat je de 1 gebruikte en dat wekte mijn nieuwsgierigheid.

 

[VenA]

@HSV,

Ik heb de UDF niet afgesloten met <Ctrl> + <Shift> + <Enter> lijkt mij ook niet nodig. Doet deze versie van Excel blijkbaar vanzelf. Zoals in het andere draadje al laten zien staan er ook geen {} omheen bij mij.