Formel gesucht!

[cysu11]

Hallo liebe Excelgemeinde,


bin auch der Sucher nach einer Formel und komme einfach nicht drauf :(

Tabelle1
Spalte A = Artikelnummer
Spalte B = Charge
Tabelle2
Hier möchte ich eine Formel haben, die in Tabelle1 nachguckt welche Artikel die Chargenummer XX und yy hat.
Nur wenn beide Chargen vorhanden! Wenn nur eine Charge dann nicht!
Ich hoffe das ist verständlich! :)

Danke im Voraus
Alexandra

[lupo1]

C2: =(SUMME(INDEX(N(ZÄHLENWENNS(A:A;A2;B:B;E$2:INDEX(E:E;ANZAHL2(E:E)))>0);))=ANZAHL2(E:E)-1)*(A2<>A1)

(in der angehängten Datei)
  170829 Dateisaetze mit UND-Auftreten mehrerer Auspraegungen einer Eigenschaft.xlsx (Größe: 9,99 KB / Downloads: 3)

Überschreitest Du eine gewisse Menge an Datensätzen, ist folgende genauere und längere Formel schneller (ist in der Datei nicht verwendet):

C2: =(SUMME(INDEX(N(ZÄHLENWENNS(
A:A;A2;
B:B;
E$2:INDEX(E:E;ANZAHL2(E:E)))>0);))=ANZAHL2(E:E)-1)*(A2<>A1)

wird dann zu (blau: verlängerte Formelteile!)

C2: =(SUMME(INDEX(N(ZÄHLENWENNS(
INDEX(A:A;VERGLEICH(A2;A:A;)):INDEX(A:A;VERGLEICH(A2;A:A));A2;
INDEX(B:B;VERGLEICH(A2;A:A;)):INDEX(B:B;VERGLEICH(A2;A:A));
E$2:INDEX(E:E;ANZAHL2(E:E)))>0);))=ANZAHL2(E:E)-1)*(A2<>A1)

Eine weitere Optimierung ist möglich, wenn ein Dummydatensatz mit Artikelnr. 0 vorangestellt wird und die Art-Nr positiv-ganzzahlig sind. Dann kann auch der vordere VERGLEICH mit dem Parameter ;1 statt ;0 laufen:

C2: =(SUMME(INDEX(N(ZÄHLENWENNS(
INDEX(A:A;VERGLEICH(A2-1%;A:A)):INDEX(A:A;VERGLEICH(A2;A:A));A2;
INDEX(B:B;VERGLEICH(A2-1%;A:A)):INDEX(B:B;VERGLEICH(A2;A:A));
E$2:INDEX(E:E;ANZAHL2(E:E)))>0);))=ANZAHL2(E:E)-1)*(A2<>A1)

[Hydronaut]

Alexandra,

jenseits von Formeln:
Filter
Advanced Filter
Pivot-Tabelle
Power Query

[neopa]

Hallo Alexandra,

wie eben sehe, hat Lupo schon ein Vorschlag eingestellt. Da ich mich aber nun damit beschäftigt habe, hier noch mein Formelvorschlag (der einfacheren Übersicht halber im gleichen Tabellenblatt) .

Formel E2 (setzt mindestens die Excel 2010er Version voraus) einfach nach unten kopieren.

Für eine Massendatenauswertung würde ich es wohl mit Hilfe einer Pivotdatenauswertung vornehmen. Mit PowerQuery (hab ich nicht) sollte es aber vielleicht noch einfacher gehen.

	A	B	C	D	E
1	Artikel	Charge			Ergebnis
2	A_01	aa			A_02
3	A_02	xx			A_03
4	A_03	xy
5	A_01	xx
6	A_02	bb
7
8	A_03	yy
9	A_02	yy
10
11	A_04	xx
12	A_03	xx
13

Formeln der Tabelle

Zelle Formel E2 {=WENNFEHLER(INDEX(Tabelle1!A:A;AGGREGAT(15;6;ZEILE(A$2:A$99)/((ZÄHLENWENNS(A$2:A$99;A$2:A$99;B$2:B$99;"xx")>0)+(ZÄHLENWENNS(A$2:A$99;A$2:A$99;B$2:B$99;"yy")>0)=2)/(ZÄHLENWENN(E$1:E1;A$2:A$99)=0);1));"")}

Enthält Matrixformel: Umrandende { } nicht miteingeben, sondern Formel mit STRG+SHIFT+RETURN abschließen!

Matrix verstehen

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

[lupo1]

Eine weitere Optimierung ist möglich, wenn ein Dummydatensatz mit Artikelnr. 0 vorangestellt wird und die Art-Nr positiv-ganzzahlig sind. Dann kann auch der vordere VERGLEICH mit dem Parameter ;1 statt ;0 laufen:

C2: =(SUMME(INDEX(N(ZÄHLENWENNS(
INDEX(A:A;VERGLEICH(A2-1%;A:A)):INDEX(A:A;VERGLEICH(A2;A:A));A2;
INDEX(B:B;VERGLEICH(A2-1%;A:A)):INDEX(B:B;VERGLEICH(A2;A:A));
E$2:INDEX(E:E;ANZAHL2(E:E)))>0);))=ANZAHL2(E:E)-1)*(A2<>A1)

Das war nicht korrekt. Richtig muss die Formel, jetzt mit Dummyzeile 2 (mit A2: 0), so heißen:

C3: =(SUMME(INDEX(N(ZÄHLENWENNS(
INDEX(A:A;VERGLEICH(A3-1%;A:A)+1):INDEX(A:A;VERGLEICH(A3;A:A));A3;
INDEX(B:B;VERGLEICH(A3-1%;A:A)+1):INDEX(B:B;VERGLEICH(A3;A:A));
E$2:INDEX(E:E;ANZAHL2(E:E)))>0);))=ANZAHL2(E:E)-1)*(A3<>A2)