Formellösungen für Spezialfilter ohne Duplikate *

Gruppe: Listen/Datensätze

Beitrag: Formellösungen für Spezialfilter ohne Duplikate *

Aufgabe

Die in Spalte A mehrfach vorkommenden Einträge (Doppler) sollen einzeln aufgelistet werden.

Eine Formellösung statt des Spezialfilters hat den Vorteil, daß diese sich Änderungen anpasst. Der Spezialfilter ist dynamisch wie ein Sack Muscheln und müsste jedesmal neu ausgeführt werden.

Die Array-Formeln ohne Hilfsspalte sind für überschaubare Bereiche. Bei langen Listen werden diese zu langsam; - dann sollte man die Hilfsspaltenlösung verwenden.

Beispiel Hilfsspaltenlösung:

	A	B	C
1	10	1	10
2	JensF	2	JensF
3	8	3	8
4	10	3	WF
5	10	3	5
6	WF	4	1
7	WF	4	3
8	5	5	7
9	1	6
10	3	7
11	3	7
12	7	8
13	7	8
14	7	8
15	10	8

Lösung

Liste: Spalte A (A1:A99)

in C1 steht =A1 oder wenn A1 auch leer sein kann:

{=INDEX(A:A;VERGLEICH(WAHR;A1:A99<>"";0))}

Am einfachsten: in C1 steht nichts oder irgend ne Überschrift.

in C2 steht:

{=WENNFEHLER(INDEX(A:A;VERGLEICH(1;(ZÄHLENWENN(C$1:C1;A$1:A$99)=0)*(A$1:A$99<>"");0));"")}

runterkopieren

für alte Excel-Versionen ohne die Funktion WENNFEHLER:

{=WENN(SUMME(ZÄHLENWENN(A$1:A$99;C$1:C1))>=ANZAHL2(A$1:A$99);"";INDEX(A:A;VERGLEICH(1;(ZÄHLENWENN(C$1:C1;A$1:A$99)=0)*(A$1:A$99<>"");0)))}

Handelt es sich nur um Zahlen und spielt die Reihenfolge keine Rolle ist es sehr viel einfacher:
in C1 steht:

=MAX(A:A)

in C2 steht:

{=MAX((A$1:A$99 < C1)*A$1:A$99)}

C2 runterkopieren

Dasselbe (nur Zahlen) ohne Arrayformel - bei großem Datenbestand - (Josef Burch):
in C1 steht:

=MIN(A:A)

in C2 steht:

=WENN(MAX(A:A)=MAX(C$1:C1);"";KGRÖSSTE(A:A;ZÄHLENWENN(A:A;">"&C1)))

C2 runterkopieren

Hilfsspaltenlösung ohne Arrayformel für Zahlen und Texte mit richtiger Reihenfolge:
in B1 (Hilfsspalte) steht 1
in B2 steht:

=(ZÄHLENWENN($A$1:A2;A2)=1)+MAX(B$1:B1)

und runterkopieren
in C1 steht:

=WENN(ZEILE(A1)>MAX(B:B);"";INDEX(A:A;VERGLEICH(ZEILE(A1);B:B;0)))

und runterkopieren

Ergänzung vom 08.10.2007: Spezialfilter für 2 Spalten
Es gibt zwei Listen mit Zahlen und Texten, die in beiden und mehrfach vorkommen können.
Es sollen alle Einträge aus beiden Listen ohne doppelte aufgeführt werden.

	A	B	C	D	E
1	Liste 1	Liste 2	Extrakt		4
2	Bier		Bier	1
3	5	vino	5	2	5
4		vino	12
5	5	103	Brandy		6
6	Bier	103	vino
7	12	Brandy	103	3
8		Bier
9	Brandy			4

in D2 steht:

=WENN(ZÄHLENWENN(A$2:A2;A2)=1;MAX(D$1:D1)+1;"")

in E1 steht:

=MAX(D:D)

in E2 steht:

=WENN(UND(ZÄHLENWENN(B$2:B2;B2)=1;ZÄHLENWENN(A:A;B2)=0);MAX(E$1:E1)+1;"")

in C2 steht:

=WENN(ZEILE(A1)>MAX(E:E);"";WENN(ZEILE(A1)>MAX(D:D);INDEX(B:B;VERGLEICH(ZEILE(A1);E:E;0));INDEX(A:A;VERGLEICH(ZEILE(A1);D:D;0))))

C2, D2 und E2 runterkopieren und die Hilfsspalten D und E dann ausblenden.

Handelt es sich nur um Zahlen und spielt die Reihenfolge keine Rolle, so wie oben eben mit 2 oder auch mehr Spalten verfahren.

Das Portal zum Thema Excel-Formeln