Excel – Einträge einer Tabelle ohne Doppelte auflisten

Ich möchte die Liste aller Einträge einer Spalte haben – das aber bitte ohne Doppelte.
Über Pivot oder den erweiterten Filter kann ich mir einen Schnappschuss holen, der aber ständig aktualisiert werden muss. Bei meinen Formelrecherchen bin ich dann auf viele Lösungen mit Matrixschreibweise gestoßen, muss dafür aber die {}-Klammern mit STRG + SHIFT + ENTER setzen.

Als Nutzer von Excel 2010/2013 steht mir aber auch die Funktion AGGREGAT zur Verfügung. Und mit dieser mächtigen Arrayfunktion (Typ 15), INDEX, ZEILE und WENNFEHLER kann ich mir zuverlässig die einzelnen Werte einer Intelligenten Tabelle oder eines normalen Bereichs ausgeben lassen.

Meine Daten befinden sich in den Spalten A:D und sind als Intelligente Tabelle mit dem Namen tab_Daten formatiert.
Ich möchte die Daten aus Spalte A – tab_Daten[Kunde] in Spalte F aus gewiesen bekommen.

352-1

In Spalte D mit dem Namen tab_Daten[Erstmals] befindet sich folgende Formel:
=ZÄHLENWENN($A$2:[@Kunde];[@Kunde])

Die Formel steht in F2 und wird nachher runter kopiert:
=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE(tab_Daten[Kunde])/(tab_Daten[Erstmals]=1);ZEILE()-1);1);"")

Die Formel aus F2 schrittweise aufgelöst:
1. ZEILE(tab_Daten[Kunde]) gibt die Zeilen der Einträge im Bereich tab_Daten[Kunde] zurück. Man hätte auch eine andere Spalte wählen können, da alle gleich lang sind.
=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;{2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18; 19;20;21;22;23}/(tab_Daten[Erstmals]=1);ZEILE()-1);1);"")

2. tab_Daten[Erstmals] gibt die Werte aus Spalte D zurück.
=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;{2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18; 19;20;21;22;23}/({1;1;1;2;1;1;1;1;2;3;2;3;1;4;4;5;3;2;2;5;2;1}=1);ZEILE()-1);1);"")

3. Der Vergleich der Wert aus Spalte D mit dem Wert 1. Bei Übereinstimmung WAHR, falls nicht FALSCH.
=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;{2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23}/{WAHR;WAHR;WAHR;FALSCH;WAHR;WAHR;WAHR;WAHR;FALSCH;FALSCH;FALSCH;FALSCH;WAHR;FALSCH; FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;WAHR};ZEILE()-1);1);"")

4. Die Zeilennummern der Tabelle werden durch WAHR (1) und FALSCH (0) geteilt. Bei einer Division durch Null kommt die Fehlermeldung #DIV/0. Der Parameter 6 bei AGGREGAT sorgt dafür, dass diese Werte ignoriert werden.
=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;{2;3;4;#DIV/0!;6;7;8;9;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;14;#DIV/0!;#DIV/0!; #DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;23};ZEILE()-1);1);"")

5. Anschließend wird ganz rechts in der Formel die aktuelle Zeile ausgelesen und um 1 reduziert (da F2 in Zeile 2 steht, muss hier bei der obersten Formel eine 1 herauskommen)
.=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;{2;3;4;#DIV/0!;6;7;8;9;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;14;#DIV/0!; #DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;23};{2}-1);1);"")

6. AGGREGAT liefert den kleinsten Wert des Arrays und ignoriert dabei die Fehler.
=WENNFEHLER(INDEX(A:A;{2};1);"")

7. Die Funktion Index nutzt den Zeilenwert, der über AGGREGAT ermittelt wurde, um die entsprechende Zeilenposition aus Spalte A:A auszulesen.
=WENNFEHLER("K-102";"")

8. Bei einem Fehler durch zu weiteres Herunterkopieren der Formel, würde WENNFEHLER die Formel einfach ausblenden. INDEX liefert das Ergebnis sauber ab. Der erste Eintrag steht.
="K-102"

–>
K-102

Datei zum Video:
Excel_03_Datenlisten_Teil_2

Link zu YouTube:

8 Kommentare

  1. Pingback: Excel – Liste der Einfach- und Mehrfacheinträge | Andreas Thehos
  2. Jack Plumpi

    Sehr toller Blog!

    Hallo, alles was hier noch fehlt ist, dass die Kunden auch sortiert angezeigt werden können. Wie macht man das, denn die Sortierfunktion fährt hier ins Leere! 😉

    • thehosblog

      Sortieren kann hier nicht funktionieren, da ja Formeln hinterlegt sind. Die Formeln müssten entsprechend angepasst werden.

  3. Jack Plumpi

    Diese Form der Tabelle kann auch nicht so einfach freigegeben werden… Kompliziertes Thema 😉

    • thehosblog

      Hallo,
      Freigaben sind ein ganz spezielles Thema. Vieles funktioniert dann nicht mehr ordentlich.

  4. fly4two

    Hallo, Super Beschreibung funktioniert tadellos !
    Die spannende Frage ist allerdings, wie muss die Formel zum Sortieren angepasst werden ??
    Habe darüber nicht gefunden, könnte das aber sehr gut gebrauchen.
    Ansonsten super gemacht, weiter so.

  5. Manuel

    Hi,

    finde diese Liste und Anleitung echt super, großen Daumen hoch!

    Aber habe bei mir ein Problem, bei mir gibt er immer Fehler wenn ich die Formel mit dem tab_ beginne, irgendwie, warum auch immer erkennt er das bei mir nicht 😦 Hoffe evtl auf eine kleine Hilfe 😉

  6. Frank Mueller

    Kann man das auch umsetzten ohne eine „intelligente“ Tabelle, wenn ja: wie müsste das angepasst werden?