Automatisch koppelen land aan stad

[doehli]

Beste mensen,

Ik heb een vraag over het voorbeeldbestand:
Bekijk bijlage Voorbeeld.xlsx

De bedoeling is dat je zelf een stad invoert in cel C10 en dat er dan automatisch in cel C11 wordt weergegeven in welk land dit is (omdat dit zo staat in de tabel die erboven staat).
Ik heb al geprobeerd om dit met VLOOKUP en INDEX+MATCH formules voor elkaar te krijgen, maar tot zover is het me nog niet gelukt dat Duitsland wordt weergegeven als ik Berlijn invoer (of Nederland als ik Amsterdam invoer, etc.)

Iemand een idee welke formule ik hiervoor moet gebruiken?

Bedankt!

 

[Ralbers]

=INDEX(3:3;1;ALS.FOUT(VERGELIJKEN(I10;4:4;0);ALS.FOUT(VERGELIJKEN(I10;5:5;0);VERGELIJKEN(I10;6:6;0))))

Zoiets zou een optie kunnen zijn
maarja dat kan natuurlijk alleen als je geen honderd rijen hebt

Als dat wel het geval is en je hebt wel maar een paar kolomen kan het andersom

namelijk zoiets:

=INDEX(3:3;1;ALS.FOUT(ALS(VERGELIJKEN(I10;A:A;0)>1;1;0);ALS.FOUT(ALS(VERGELIJKEN(I10;B:B;0)>1;2;0);ALS.FOUT(ALS(VERGELIJKEN(I10;C:C;0)>1;3;0);ALS(VERGELIJKEN(I10;D:D;0)>1;4;0)))))

Maarja beide methodes lijken me niet echt een optie bij een groot aantal kolommen of rijen

 

[Ralbers]

Ik heb nog een manier bedacht

Bekijk bijlage Voorbeeld.xlsx

Met redelijk simpele formules
Het enige nadeel is dat er een extra hulprijtje nodig is

 

[doehli]

Bedankt, ik zal met beiden eens aan de slag gaan. Waarschijnlijk heb ik 10 kolommen nodig en een nog niet gespecificeerd rijen (waarschijnlijk loopt dit wel tegen de 50-100 aan). Dan kan ik denk ik het beste Methode 2 in je bestand gebruiken? Thanks!

 

[Ralbers]

Word dan wel een lange formule maar moet te doen zijn met 10

Succes

 

[namliam]

Nog beter zou het zijn als de landen niet in kolommen staan maar in rijen,
Stad, Land

Dan is het een simpele VLookup

 

[doehli]

Word dan wel een lange formule maar moet te doen zijn met 10

Succes

Ik ben aan 't proberen met 10 kolommen en meerdere rijen, maar hoe pas ik de formules van Methode 2 zo aan dat die dit ook meeneemt? Bedankt

 

[Ralbers]

=INDEX(4:4;1;ALS.FOUT(ALS(VERGELIJKEN(M13;A:A;0)>1;1;0);ALS.FOUT(ALS(VERGELIJKEN(M13;B:B;0)>1;2;0);ALS.FOUT(ALS(VERGELIJKEN(M13;C:C;0)>1;3;0);ALS.FOUT(ALS(VERGELIJKEN(M13;D:D;0)>1;4;0);ALS.FOUT(ALS(VERGELIJKEN(M13;E:E;0)>1;5;0);ALS.FOUT(ALS(VERGELIJKEN(M13;F:F;0)>1;6;0);ALS.FOUT(ALS(VERGELIJKEN(M13;G:G;0)>1;7;0);ALS.FOUT(ALS(VERGELIJKEN(M13;H:H;0)>1;8;0);ALS.FOUT(ALS(VERGELIJKEN(M13;I:I;0)>1;9;0);ALS(VERGELIJKEN(M13;J:J;0)>1;10;0)))))))))))

Hij begint met index()
daarbij moet je de titels selecteren.
Hij pakt dan de x titel

die x bepaald ie door te kijken of de plaats bestaat in de eerste kolom. Zo ja dan geeft ie 1 zo nee dan gaat ie fout
Als hij fout gaat zoekt hij in de 2e kolom als hij daar voorkomt geeft hij 2 zo niet dan gaat ie fout
enzo voort
dus iedere keer is het

=als.fout(als([testen of hij voorkomt in die kolom met aantal.als];[zo ja titelnr];[zo nee 0 (komt nooit voor)]);[als het vergelijken geen resultaat geeft dan geeft hij fout en moet hij zoeken naar de volgende])

misschien een iets makkelijker alternatief is trouwens met aantal.als ipv vergelijken

=INDEX(4:4;1;ALS(AANTAL.ALS(A:A;M13)<>0;1;ALS(AANTAL.ALS(B:B;M13)<>0;2;ALS(AANTAL.ALS(C:C;M13)<>0;3;ALS(AANTAL.ALS(D:D;M13)<>0;4;ALS(AANTAL.ALS(E:E;M13)<>0;5;ALS(AANTAL.ALS(F:F;M13)<>0;6;ALS(AANTAL.ALS(G:G;M13)<>0;7;ALS(AANTAL.ALS(H:H;M13)<>0;8;ALS(AANTAL.ALS(I:I;M13)<>0;9;ALS(AANTAL.ALS(J:J;M13)<>0;10;0)))))))))))

Maar het idee is hetzelfde hij kijkt eerst of hij bestaat zo ja dan geeft ie een waarde zodat de index de goede uitkomst kan vinden. zo niet dan zoekt ie in de volgende kolom

 

[doehli]

Is het mogelijk om de cel waarin je de stad invoert en de cel waarin het bijbehorende land wordt weergegeven in een ander tabblad te plaatsen? Dus dat je in een tabblad de plaats kan invoeren plus het bijbehorende land eruit krijgt, en dat je in een ander tabblad het overzichtsveld hebt waarop jezelf kan aanvullen welke stad bij welk land hoort. Tot nu toe krijg ik dan foute resultaten?

Sorry voor de vele vragen..

 

[Ralbers]

no problem

overal waar je vergelijkt naar 4:4 of A:A of B:B enz
even Blad1! voorzetten
en Blad1 is dan de titel van het werkblad natuurlijk

 

[popipipo]

Of bekijk dit eens
http://www.contextures.com/xlDataVal13.html

 

[snb]

- verwijder de eerste lege rijen
- zet in een macromodule de volgende User Defined Function (UDF)

Function F_vind(c00)
   F_vind = Cells(1, Cells.Find(c00).Column)
End Function

- zet in cel C9
=F_vind(C8)

 

[doehli]

- verwijder de eerste lege rijen
- zet in een macromodule de volgende User Defined Function (UDF)

Function F_vind(c00)
   F_vind = Cells(1, Cells.Find(c00).Column)
End Function

- zet in cel C9
=F_vind(C8)

Hoe kan ik deze macro gebruiken als het overzicht met landen en steden (wat je handmatig verder aanvult) in Sheet1 zit, en het invoerveld voor de stad en de cel waar het land weergegeven moet worden zich op Sheet2 bevind?

 

[doehli]

no problem

overal waar je vergelijkt naar 4:4 of A:A of B:B enz
even Blad1! voorzetten
en Blad1 is dan de titel van het werkblad natuurlijk

En als de cel waar de stad ingevoerd wordt ook in een andere tabblad staat?

 

[snb]

Wat zou daarvoor een logische aanpassing zijn ?

 

[doehli]

Wat zou daarvoor een logische aanpassing zijn ?

Ik ben helemaal nieuw in het gebruiken van Macro's en je macro werkt perfect als je alle informatie in een tabblad hebt staan. Maar als ik in een tabblad (Invoer) een overzicht heb met landen en steden (wat je handmatig verder aanvult), en het invoerveld voor de stad en de cel waar het land weergegeven moet worden zich op het tabblad 'Output' bevindt, dan werkt het nog niet. Ik heb de macro als volgt aangepast (Zie voorbeeld):

Function F_vind(Invoer!c00)
F_vind = Cells(1, Cells.Find(Invoer!c00).Column)
End Function

Bekijk bijlage VoorbeeldLanden.xlsx

 

[snb]

Als je geïnteresseerd bent in VBA (de macro-taal) kun je het best met het begin beginnen: VBA voor Dummies (J. Walkenbach).

 

[doehli]

Ik heb de macro geprobeerd aan te passen zodat die wel goed werkt in het laatste voorbeelddocument (Voorbeeldlanden.xlsx), maar ik heb tot nu toe nog geen resultaat. Kun je me op de goede weg zetten/heb je tips?

Bedankt alvast