Microsoft Excel

Herbers Excel/VBA-Archiv

Indexsuche über zwei Matrizen

Betrifft: Indexsuche über zwei Matrizen von: toolmaker
Geschrieben am: 25.03.2020 12:15:13

Hallo Leute,


ich bin auf der Suche nach einer Möglichkeit die Index-Suche über zwei Matrizen zu formulieren.

Im Anhang ist eine Beispieldatei. Konkret sieht mein Beispielproblem wie folgt aus:

In Blatt 1 stehen in Spalte A Vor- und Nachnamen, in Spalte B steht entsprechend ob es sich um einen Vor- oder Nachname handelt.

In Blatt 2 möchte ich nun eine automatische Auflistung aller Vor- oder Nachnamen, entsprechend welches Suchwort ("Vorname", oder "Nachname") angegeben ist. Angenommen das Suchwort steht in E1, und in E2 und folgende sollen die Namen aufgelistet werden. Hierzu sieht die Formel wie folgt aus:


{=WENNFEHLER(INDEX(Tabelle1!A:A;KKLEINSTE(WENN(Tabelle1!B1:B10=$E$1;ZEILE(1:10));ZEILE(A1)));"") }


Das ganze funktionier auch schon für ein Tabellenblatt genauso wie ich es mir vorstelle.

Mein Problem ist nun, das ich noch ein drittes Blatt habe, welches identisch zu Blatt 1 ist. Wie kann ich die Formel nun verändern das mir sowohl die Namen aus Blatt 1, als auch Blatt 3 angezeigt werden?


Danke und bleibt alle gesund :)


https://www.herber.de/bbs/user/136092.xlsx

Betrifft: AW: ist realisierbar ...
von: neopa C
Geschrieben am: 25.03.2020 12:45:06

Hallo,

... doch in Deinem Tabellenblatt fehlen Angaben im Tabellenblatt 3
Und wenn Du noch angibst welche Excel-Version Du genau hast gibt es auch noch eine andere Methode als von Dir angedacht.

Ich bin jetzt allerdings erst einmal eine mind. 1h offline.


Gruß Werner
.. , - ...

Betrifft: AW: ist realisierbar ...
von: toolmaker
Geschrieben am: 25.03.2020 13:24:34

Hallo Werner,

genau, meine Beispielexcel bezog sich jetzt nur auf die schon funktionierende Formel, damit man versteht was genau gemeint ist. Meine Excelversion ist 2013. Ich würde es gerne mit Formel, ohne Makro realisieren.

Betrifft: AW: sollen auch vorh. Duplik. gelistet werden? owT
von: neopa C
Geschrieben am: 25.03.2020 15:10:45

Gruß Werner
.. , - ...

Betrifft: AW: sollen auch vorh. Duplik. gelistet werden? owT
von: toolmaker
Geschrieben am: 25.03.2020 15:15:26

Nein Duplikate müssen nicht gelistet werden

Betrifft: AW: dann geschachteltes INDEX() und AGGREGAT() ...
von: neopa C
Geschrieben am: 25.03.2020 15:30:32

Hallo,

... für Daten in Tabelle1 und 3 mit gleicher Datenstruktur und Auswertung in Tabelle2 ab Zeile2 (in E1 steht die "Rubrik") mit folgender Matrixfunktion(alität)sformel die keines spez. Formelabschluss wie eine klassische Matrixformel benötigt.

=WENNFEHLER(WENNFEHLER(INDEX(Tabelle1!A:A;AGGREGAT(15;6;ZEILE(A$2:A$99)/(Tabelle1!B$2:B$99=E$1)/(ZÄHLENWENN(E$1:E1;Tabelle1!A$2:A$99)=0);1));INDEX(Tabelle3!A:A;AGGREGAT(15;6;ZEILE(A$2:A$99)/(Tabelle3!B$2:B$99=E$1)/(ZÄHLENWENN(E$1:E1;Tabelle3!A$2:A$99)=0);1)));"")

Gruß Werner
.. , - ...

Betrifft: AW: dann geschachteltes INDEX() und AGGREGAT() ...
von: toolmaker
Geschrieben am: 25.03.2020 19:29:14

Hallo Werner,

danke für den ersten Lösungsvorschlag. Leider hat es noch nicht so geklappt wie ich es mir vorgestellt habe. Möglicherweise habe ich aber auch einen Fehler gemacht... Anbei nochmal eine aktualisierte Version meiner Tabelle. Tabelle1 und Tabelle2 sind identisch und in Tabelle3 werden alle Nachnamen untereinander aufgelistet. In meinem Beispiel funktioniert das wie du sehen wirst erst bisher nur mit Tabelle1.
Danke und Viele Grüße


https://www.herber.de/bbs/user/136103.xlsx

Betrifft: AW: "nicht geklappt" ist nicht nachvollziehbar ...
von: neopa C
Geschrieben am: 25.03.2020 19:42:03

Hallo,

... denn Du hast meinen Vorschlag ja gar nicht eingesetzt. Dafür hast Du jetzt im Gegensatz zur Eingangsfragestellung die Ergebnisliste in Tabelle 3 verlegt. Dafür müsstest Du doch nun lediglich in meiner Formel "Tabelle3" in "Tabelle2" ändern und Du hast die gesuchte Lösung. Ich denke jetzt sollte es auch bei Dir "klappen".

Gruß Werner
.. , - ...

Betrifft: AW: "nicht geklappt" ist nicht nachvollziehbar ...
von: toolmaker
Geschrieben am: 26.03.2020 08:41:55

Hallo Werner,
danke für deine Unterstützung, ich habe es jetzt in meiner Beispielexcel so zum Laufen gebracht. Leider habe ich das Beispiel aber wohl ein bisschen zu extrakt ausgewählt weil ich dacht das ich es mit einer kleinen Hilfestellung dann selbst hinbekomme. Leider bekomme ich es aber nicht auf meine eigentliche Exceltabelle übertragen. Darf ich dir nochmal eine Kopie meiner eigentlichen Tabelle schicken?
Schon mal danke für die Hilfe!

Betrifft: AW: warum schicken? Lade sie doch hier hoch owT
von: neopa C
Geschrieben am: 26.03.2020 08:46:15

Gruß Werner
.. , - ...

Betrifft: AW: warum schicken? Lade sie doch hier hoch owT
von: toolmaker
Geschrieben am: 26.03.2020 09:20:08

Meinte ich ja ;)
Anbei die Excel wie sie im Original sein soll. Im Blatt "Miete" sollen dann das Datum, Verwendungszweck,Umsatz und Kategorie aller Zeilen aus Konto1 und Konto2 erscheinen welche zur Kategorie "Miete" gehören.
https://www.herber.de/bbs/user/136110.xlsx

Betrifft: AW: dann analog, nur dass hierbei ...
von: neopa C
Geschrieben am: 26.03.2020 10:04:59

Hallo.

... Datenduplikate mE nicht gelöscht werden dürfen.

Deshalb in A3 folgende Formel:

=WENNFEHLER(INDEX(Konto1!A:A;AGGREGAT(15;6;ZEILE(A$2:A$99)/(Konto1!$D$2:$D$99=$B$1);ZEILE(A1)));WENNFEHLER(INDEX(Konto2!A:A;AGGREGAT(15;6;ZEILE(A$2:A$99)/(Konto2!$D$2:$D$99=$B$1);ZEILE(A1)-ZÄHLENWENN(Konto1!$D:$D;$B$1)));""))

und diese nach unten und rechts ziehend kopieren. Anschließend noch der Spalte A das Datumsformat und Spalte C das Währungsformat zuweisen.

Gruß Werner
.. , - ...

Betrifft: AW: dann analog, nur dass hierbei ...
von: toolmaker
Geschrieben am: 27.03.2020 10:19:21

Hat geklappt, ich bin hellauf begeistert!
Vielen Dank!

Betrifft: AW: gerne owT
von: neopa C
Geschrieben am: 27.03.2020 16:25:42

Gruß Werner
.. , - ...