Absoluter Bezug bei SVERWEIS mit mehreren

[gerrard87]

Hallo Leute,

komme an einem Punkt leider nicht weiter. Ist quasi ein SVERWEIS mit mehreren Suchergebnissen. Habe zwei Spalten mit Informationen: Spalte A enthält Daten, Spalte B enthält Produkte. Ich möchte für jedes Datum die Produkte angezeigt bekommen (sind mehrere und können doppelt vorkommen). Für ein einzelnes Datum funktioniert das ohne Probleme. Hätte das aber gerne gleich für alle Daten aufeinmal.

Bisher habe ich folgendes:
In "E3" habe ich die unten aufgeführte Formel eingetragen (Matrix-Formel) und dann jeweils einfach runtergezogen:

Code:

=WENNFEHLER(INDEX($B$2:$B$9999;KGRÖSSTE(($A$2:$A$9999=$E$1)*(ZEILE($A$2:$A$9999)-1);ZÄHLENWENN($A$2:$A$9999;$E$1)+1-ZEILE(A1)));"")

Für alle anderen Daten muss ich die Formel kopieren und von Hand ändern, was ein großer Aufwand ist!

Gibt es hier eine Möglichkeit dieses zu automatisieren bzw. steht hierfür eine andere Lösungsmöglichkeit zur Verfügung.

Vielen Dank!

   

  test.xlsx (Größe: 16,92 KB / Downloads: 7)

[sturmrm]

Hallo,

wenn ich Dich richtig verstanden habe, dann so:

=WENNFEHLER(INDEX($B$2:$B$9999;KGRÖSSTE(($A$2:$A$9999=E$1)*(ZEILE($A$2:$A$9999)-1);ZÄHLENWENN($A$2:$A$9999;E$1)+1-ZEILE($A1)));"")

Mit Strg+Shift+Enter abschließen. Dann nach rechts und unten ziehen.
Achte auf die Dollar-Zeichen in den fett dargestellten Bezügen.


Gruß
sturmrm

[sturmrm]

Hallo,

oder ohne Matrixformel.

=WENNFEHLER(INDEX($A$2:$B$9999;AGGREGAT(15;6;ZEILE($A$2:$A$9999)-1/($A$2:$A$9999=E$1);ZEILE()-2);2);"")

Vielleicht würde bei der Länge des Suchbereiches aber eine VBA-Lösung schneller arbeiten?


Gruß
sturmrm

[neopa]

Hallo,

wenn die Produkte wirklich Produktnummern sind, dann bedarf es keines zusätzlichen INDEX()

Dann reicht einfach:

=WENNFEHLER(AGGREGAT(15;6;$B$2:$B$9999/($A$2:$A9999=E$1);ZEILE(A1));"")


Allerdings wenn Du wirklich fast 10000 Datensätze für über 500 Datumswerte auszuwerten hast, dann dürfte Dein PC ganz schön ins Schnaufen kommen.

Ich würde für eine PIVOTauswertung plädieren. Das Datum in den Berichtsfilter, Produkt in die Zeilenbeschriftung und evtl. zusätzlich noch Produkt in Werte (als Anzahl auswerten) und die im Berichtslayout die Gesamtauswertung deaktivieren. Das ist wesentlich schneller und auch mit nur wenigen Mausklicks aufgestellt.

[sturmrm]

Hallo Werner,

ja klar geht´s auch ohne Index(). Sind ja schon Zahlen, die durch 1 (oder 0) dividiert werden.

Da war wohl der Kaffee zu viel (bei der Warterei bis Berechnungsende).


Gruß
sturmrm

[neopa]

Hallo sturmrm,

wenn es jedoch keine Zahlenwerte sein sollten und unbedingt eine Formel (wovon ich aber nach wie vor abrate), dann smit INDEX() so:

=WENNFEHLER(INDEX($B:$B;AGGREGAT(15;6;ZEILE(B$2:B$9999)/($A$2:$A$9999=E$1);ZEILE(A1)));"")

zumal auch noch ungeklärt ist, ob evtl. vorh. Dupletten (am gleichen Tag) auch doppelt gelöscht werden sollen.

Ich rate nach wie vor zu einer PIVOTauswertung.

[sturmrm]

Hallo Werner,

ja macht Sinn, dann geht´s auch ohne subtrahieren von -2 bzw. -1 in den Zeilen.

Denke auch, daß Pivot hier das Mittel der Wahl sein dürfte.

Schönen Rest-Sonntag noch!
sturmrm

[neopa]

Hallo sturmrm,

danke, das wünsch ich Dir auch.