Excel: erweiterter sverweis - Ausgabe mehrerer Ergebnisse

Question

Hallo Experten,

ich bin eine Ewigkeit an einem Excel-Tool am basteln und benötige eure Hilfe bei einem erweiterten sverweis.

Es sollen nach Eingabe einer PLZ eine oder mehrere Ortschaften, die diese PLZ haben, in Spalte B ab Zeile 11 untereinander ausgegeben werden. Z.B. Beispiel Postleitzahl 66871 hat 14 Ortschaften zugeordnet. Hier ist meine Datei. www.file-upload.net/download-4199644/Beispiel-1.xls.html

Ich bin schon auf diesen thread www.supportnet.de/t/2397444 gestoßen, bekomme es aber irgendwie nicht hin.

Könnt ihr mir helfen?


Viele Grüße,
Betriebsdirektor

Answer 1

Hallo,

Versuch es mal mit der Lösung

www.file-upload.net/download-4199749/Beispiel.xls.html

Die beiden rot geschriebenen Werte werden für die Verarbeitung gebraucht

Gruß

Helmut

Answer 2

Hallo Betriebsdirektor,

Alternative ohne Hilfszellen:

Trage in B11 folgende Formel ein, bestätige die Eingabe mit Strg+Umschalt+Enter und ziehe sie nach Bedarf nach unten.

=WENN(ZEILE(A1)<=ZÄHLENWENN(D:D;$A$3);INDEX(E:E;KKLEINSTE(WENN(D$1:D$14759=$A$3;ZEILE($1:$14759));ZEILE(A1)));"")

Gruß
Rainer

Answer 3

Hallo Helmut,

viiieelen Dank für die schnelle Lösung! Eine Kleinigkeit scheint noch nicht zu funktionieren:
Bei manchen Postleitzahlen nimmt er noch den Wert aus der nächst höheren PLZ mit rein.
Zum Beispiel bei Eingabe von 66887 nimmt er noch Bruchmühlbach-Miesau (PLZ 66892) mit rein.

Komischerweise nicht bei allen Postleitzahlen. Womit kann das denn zusammenhängen?

Gruß, Betriebsdirektor

Answer 4

Hallo,

da liegt im letzten Teil noch die von mir ursprünglich genutzte Formel.
Einfach die Formel mal neu nach unten ziehen ab der Zeile ab B12


Gruß

Helmut

Answer 5

@Helmut: ja, okay jetzt funktioniert es!

@Rainer: auch dir vielen Dank für deine Mühe. Funktioniert auch einwandfrei! Hut ab.

Ich möchte ja nicht unverschämt sein, aber das Sahnehäubchen wäre jetzt noch, wenn die Ergebnisse (Orte) alphabetisch sortiert würden. Geht das ohne VBA mit einem Makro oder einer Formel?

Gruß, Betriebsdirektor

Answer 6

Hallo Betriebsdirektor,

...das geht dann nur mit Makro.

Habe mal ein Makro aufgezeichnnet und etwas modifiziert.

Wenn ich alle richtig gemacht habe, sollten nach Eingabe der entsprechenden PLZ die zugehörigen Orte in Spalte A aufgelistet werden.

Download

Gruß
Rainer

Answer 7

Hallo Reiner,

leider bringt Excel mir eine Fehlermeldung "Fehler beim Kompilieren: Variable nicht definiert".
Ich verwende MS Excel 2003 SP3.

Gruß, Betriebsdirektor

Answer 8

Hallo Betriebsdirektor,

... das kann ich leider nicht nach vollziehen, unter Excel2010 läuft der Code und Excel2003 habe ich nicht mehr auf dem Rechner.

Gruß
Rainer

Answer 9

Hallo,

warum sortierst du nicht deine Liste nach Alphabet?

Spalte E bis L markieren , dann "Sortierenl" nach Spalte E und F

Gruß

Helmut

Answer 10

warum sortierst du nicht deine Liste nach Alphabet?

...so einfach kann es manchmal sein!

Funktioniert prächtig.

Tausend Dank!

Viele Grüße,
Betriebsdirektor