Mittels sverweis tabelle ausgeben

[Bajukoelsch]

Hallo zusammen,
bin ganz neu im Forum und verzweifel an einer sverweis-Funktion.

Folgender Hergang:

Ich habe in den Zellen F23-35 eine Liste mit verschiedenen Namen.
Diese steuere ich per sverweis und einer Suchfunktion (I1) an und möchte alle
identischen Namensbezeichnungen untereinander ausgeben.
Bis jetzt bekomme ich lediglich einen Namen ausgegeben (I2) und habe
nach etlichen Forendurchsuchungen und Youtubevideoerklärungen es immer noch nicht hinbekommen
eine geeignete Funktion zu finden.


Hier die Bezeichnungen F23-F35, die jeewils in einer Zelle stehen:
Helge Schneider - Comeback Tour
Werner Koczwara - Als Opa Sondermüll wurde
Diether Thomas Kuhn
Kattrin Kupke - Geh wohin dat Herz dir trägt
Sissi Perlinger
Es fährt ein Zug nach Nirgendwo
Honey Pie - Anything Goes
Christoph Sonntag - Beschtoph
Piet Klocke
Sissi Perlinger - Drupp
Piet Man
Der schöne Piet
Piet Klocke - Zähne ziehen

Meine Funktion bisher:
=SVERWEIS("*"&GLÄTTEN(I1)&"*";F23:F35;1;0)

Wenn ich nun in I1 nach Piet suche, wird das Ergebnis Piet Klocke brav in I2 dargestellt.
Nun würde ich aber gerne die weiteren Ergebnisse (Piet Man, Der schöne Piet und Piet Klocke - Zähne ziehen)
untereinander (also ab I3) dargestellt haben.

Sicherlich wird jetzt der Ein oder Andere ein schmunzelndes Gesicht aufsetzen,
da dieses Forum sicherlich eine Menge Excel-Profis hat.
Da ich leider nur Grundkenntnisse besitze, und wie gesagt schon zig Beiträge ohne Efolg studiert habe,
wäre es super wenn mich jemand unterstützen könnte.

Danke euch schon mal herzlich im voraus.
Und sollte ich mich im falschen Themenbereich befindet so möge man es mir verzeichen,
da ich ein Newbie in diesem Forum bin.

Herzliche Grüße
Martin

[steve1da]

Hola,

ungetestet:

Code:

=WENNFEHLER(INDEX($F$23:$F$35;AGGREGAT(15;6;ZEILE($A$23:$A$35)-22/(ISTZAHL(SUCHEN($I$1;$F$23:$F$35)));ZEILE(A1)));"")

Gruß,
steve1da

[Bajukoelsch]

Hallo steve1da,


unglaublich, und so fix, du bist meine Rettung.
Es funktioniert wie am Schnürchen.

Ganz herzlichen Dank und viele Grüße.
Martin

[Bajukoelsch]

Hallo nochmal,

bin jetzt schon sehr weit mit meiner Datenbank gekommen,
auch mit der tatkräftigen Unterstützung von steve1da.
Nun hätte ich da noch eine Frage zum Thema Geschwindigkeit verbessern.

Habe die Positionen der Daten folgendermaßen umgestellt:

In Zellen O1-O350 habe ich folgende Lagernummern:

A-SG-M1-F01-E1
A-SG-M1-F01-E2
A-SG-M1-F01-E3
A-SG-M1-F02-E1
A-SG-M1-F02-E2
usw.

In den Zellen P1-P350 sind die Namensdaten:


Honey Pie - Anything Goes
Christoph Sonntag - Beschtoph
Piet Klocke
Sissi Perlinger - Drupp
Piet Man
Der schöne Piet
Piet Klocke - Zähne ziehen

Die Suchabfrage kann ich in I1 eingeben, und dann erscheinen
in den Zellen B1-B100 die gewünschten Ergebnisse.

Hier der Code von B1 den mir freundlicherweise steve1da übermittelt hat:
=WENNFEHLER(INDEX($P$1:$P$350;AGGREGAT(15;6;ZEILE($P$1:$P$350)/(ISTZAHL(SUCHEN($I$1;$P$1:$P$350)));ZEILE(B1)));"")


Nun möchte ich, daß in den Zellen A1-A100 die Lagernummern neben den gefilterten Veranstaltungsnamen erscheinen.
Hierfür hatte ich steve1da's Code ein wenig abgeändert und es funktioniert auch so weit.
Hier der Code in Zelle A1:
=WENNFEHLER(INDEX(O:O;AGGREGAT(15;6;ZEILE($P$1:$P$3500)/(ISTZAHL(SUCHEN($I$1;O:P)));ZEILE(A1)));"")

(Ähem: krieg irgendwie den Smiley nicht raus, ist aber ein Doppelpunkt gefolgt von einem großen P)

Problem hierbei ist, sobald ich eine Suchanfrage aktiviere dauert es ewig bis ein Ergebnis vorliegt.
Hatte versucht es irgendwie mit einem sverweis zu lösen, hat aber bis jetzt nicht wirklich funktioniert.

Es wäre toll wenn ich nochmal auf eure Unterstützung zurückgreifen könnte.
Schon mal herzlichen Dank im voraus.
Martin

[WillWissen]

Hi Martin,

stell bitte eine Beispieltabelle mit den bisherigen Ergebnissen vor. Es reichen ca. 10 Datensätze mit anonymisierten Werten und ein paar händisch eingetragenen Wunschergebnissen. So geht's:  http://www.clever-excel-forum.de/Thread-...ng-stellen

[Jockel]

Hallo Martin, du kannst vor dem Abschicken deiner Nachricht, die grafischen Smilies deaktivieren. Zu deinem "Problem". Du lässt ganze Spalten beackern... ... das haut rein... Alternativ zur Formellösung gibt's ja noch z.B. Power Query...

[Bajukoelsch]

Guten Morgen Jörg und Günter,

erstmal vielen Dank das ihr euch meiner so rasch annehmt.
@Jörg, Smilie-Abschalt Funktion gefunden... ;O)

@Günter (auch Jörg), anbei nun das gewünschte Attachement, hoffe, ich habe alles richtig gemacht.
Habe so gut als möglich versucht meine Schwierigkeit zu umschreiben.
Da ich wirklich kein Excel-Crack bin wäre sehr dankbar für eure kommenden Vorschläge.

Viele Grüße
Martin


  TestArchiv08.06.18.xlsx (Größe: 135,42 KB / Downloads: 3)

[Ego]

Hallo Martin,

A) Formeln:
1.Schreibe in einer Hilfsspalte die Zeilennummer des gefundenen Eintrags (nur die Aggregatfunktion) und nutze diese für die Indexfunktion in den übrigen Spalten.
Dann wird die Spalte P pro Zeile nur einmal durchsucht. Die Indexfunktion ist wahnsinnig schnell.
2. Nutze für die auszuwertende Liste eine strukturierte (intelligente) Tabelle (Strg+T) und in den Formeln strukturierte Verweise. Dann brauchst du nicht immer einen zu grossen Bereich zu durchsuchen.

B) Spezialfilter:
Wenn du alle Spalten der Liste angezeigt bekommen möchtest, kannst du auch die "erweiterte" Filterfunktion nutzen.

[Bajukoelsch]

Hallo Helmut,


ich bin wirklich kein großer Excel-Kenner, habe versucht die Aggregat- und Indexfunktion umzusetzen,
jedoch ohne wirklichen Erfolg. Der Spezialfilter ist für meine Zwecke auch keine große Hilfe, da die
Datenbank später Leuten zur Verfügung gestellt werden, und sie sich einfach nur einer Maske bedienen sollen.

Hab nochmal ein bißchen umgestellt und die Test-Datei als Attachement angehängt.
Vielleicht kann mir jemand sagen was ich falsch mache?

Herzliche Grüße
und vielen Dank für eure Geduld.
Martin


  TestArchiv08.06.18_2.xlsx (Größe: 141,54 KB / Downloads: 2)

[Jockel]

Hallo, als Formellösung würde ich eine Hilsspaltenvariante bevorzugen. Ich glaube, das wurde schon angesprochen..:

Arbeitsblatt mit dem Namen 'TestArchiv'
	A	B	I	J
1	LagerNr.	Veranstaltung	Sucheingabe	HS
2	A-SG-M1-F02-E3	Tristan & Isolde (russisches Plakat)	tristan	74
3	A-SG-M1-F02-E4	Tristan (1 von3)		89
4	A-SG-M1-F02-E4	Tristan Isolde (2 von 3)		90

Zelle	Formel
A2	=WENN($J2="";"";INDEX(O$1:O$350;$J2))
B2	=WENN($J2="";"";INDEX(P$1:P$350;$J2))
J2	=WENNFEHLER(AGGREGAT(15;6;ZEILE($A$1:$A$350)/ISTZAHL(SUCHEN($I$2;$P$1:$P$350));ZEILE(A1));"")

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

oder Beispiel 2..:

Arbeitsblatt mit dem Namen 'TestArchiv'
	A	B	I	J
1	LagerNr.	Veranstaltung	Sucheingabe	HS
2	A-SG-M1-F02-E3	Tristan & Isolde (russisches Plakat)	isolde	74
3	A-SG-M1-F02-E4	Isolde - Marcia Haydèe (3v.3)		87
4	A-SG-M1-F02-E4	Tristan Isolde (2 von 3)		90
5

Zelle	Formel
A2	=WENN($J2="";"";INDEX(O$1:O$350;$J2))
B2	=WENN($J2="";"";INDEX(P$1:P$350;$J2))
J2	=WENNFEHLER(AGGREGAT(15;6;ZEILE($A$1:$A$350)/ISTZAHL(SUCHEN($I$2;$P$1:$P$350));ZEILE(A1));"")

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg