Das grosse Archiv mit 628
VBA-freien Formellösungen und Tips
 
   
 
 ÜBER UNS   NEWS   FORMELN   TIPPS   KONTAKT   GäSTEBUCH   FORUM   LINKS 
   
Übersicht   Zauberbuch   Rätselbuch   Sparbuch   Bücher-Downloads / Errata  
 
Listen/Datensätze (10) ->

Formellösungen für Spezialfilter ohne Duplikate * (194)
 
 
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;MIN(WENN(A1:A99<>"";ZEILE(1:99))))}
Am einfachsten: in C1 steht nichts oder irgend ne Überschrift.

in C2 steht:
{=INDEX(A:A;VERGLEICH(1;(ZÄHLENWENN(C$1:C1;A$1:A$99)=0)*(A$1:A$99<>"");0))}
Wenn kein Eintrag mehr gefunden wird, kommt die Fehlermeldung #NV

ohne Fehlermeldung in C2 dann:
{=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)))}
bzw. WENNFEHLER(....;"") drumrum

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.



 
    zurück


 
powered by telltarget