Microsoft Excel

Das Portal zum Thema Excel-Formeln

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  10  
2 JensF  JensF  
3 8  
4 10  WF  
5 10  5  
6 WF  1  
7 WF  3  
8 7  
9  
10  
11  
12  
13  
14  
15 10   

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   
3 5  vino  5  5  
4   vino  12     
5 103  Brandy    6  
6 Bier  103  vino     
7 12  Brandy  103   
8   Bier       
9 Brandy       

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.