Liste nur mit eindeutigen Werten erstellen 17

Mit Hilfe einer Matrixformel können aus einer Liste nur die eindeutigen Werte extrahiert werden (ohne Duplikate)
 

Vor einiger Zeit hatte ich in einem Artikel beschrieben, wie man in einer Liste mit Duplikaten die Anzahl der eindeutigen Werte bestimmt (Wie zählt man eindeutige Werte in Excel?).

Der heutige Artikel geht einen Schritt weiter:
Ich möchte nicht nur wissen, wieviele eindeutigen Werte es gibt, sondern ich möchte diese gleich in einer eigenen Liste ausgeben. Also dublettenfrei. Und zwar mit Hilfe von Excel-Formeln.

Und so geht’s:

Ausgangslage und gewünschtes Ergebnis

Für mein Beispiel verwende ich eine einfache Namensliste, welche manche Einträge mehrfach enthält (die Datei kannst du dir hier herunterladen). Als Ergebnis soll die eindeutige Liste herauskommen, die im Bild in Spalte D gezeigt wird. Jeder Name also genau einmal.

Ausgangslage und gewünschtes Ergebnis

Ausgangslage und gewünschtes Ergebnis

Und das ganze mit Hilfe einer Formel-Lösung.

Für Ungeduldige: die fertige Lösungsformel

Um dieses Ergebnis zu erreichen, verwende ich eine Matrix-Formel, die aus drei Funktionen besteht:

  • ZÄHLENWENN
  • VERGLEICH
  • INDEX

{=INDEX($B$5:$B$24;VERGLEICH(0;ZÄHLENWENN(D$4:$D4;$B$5:$B$24);0))}

Wie bei jeder Matrix-Formel gilt auch hier:
Die geschweiften Klammern dürfen nicht von Hand eingegeben werden. Stattdessen muss die Formel zwingend mit der Tastenkombination Strg+Umschalt+Eingabe abgeschlossen werden!

Die fertige Matrix-Formel

Die fertige Matrix-Formel

Aus kosmetischen Gründen wird die finale Formel am Ende noch mit der WENNFEHLER-Funktion umschlossen. Zur besseren Übersicht lasse ich das in den nachfolgenden Erläuterungen aber weg.

Schritt 1: ZÄHLENWENN

Matrix-Formeln sind immer etwas schwer durchschaubar. Daher empfiehlt es sich, die Formel zu zerlegen und mit Hilfsspalten zu arbeiten. Da man verschachtelte Formeln am besten von innen nach außen bearbeitet, beginnen wir mit der ZÄHLENWENN-Funktion.

Erste Hilfsspalte mit der ZÄHLENWENN-Funktion

Erste Hilfsspalte mit der ZÄHLENWENN-Funktion

Damit wird für jede einzelne Zeile geprüft, ob der enthaltene Name oberhalb der jeweiligen Zeile schon einmal vorgekommen ist. Wenn nein, liefert die Funktion den Wert 0 (Null), ansonsten eben 1, 2 oder wie oft auch immer der Name weitere oben schon aufgetaucht ist.

Wichtig: Das erste Argument der ZÄHLENWENN-Funktion, also der zu durchsuchende Bereich, beginnt bereits in der Überschriftenzeile.
Ebenfalls wichtig sind die gemischten Bezüge, also die korrekte Setzung der Dollarzeichen:
$D$4:D4

Wenn die Formel dann nach unten kopiert wird, beginnt damit der Bezug immer absolut in Zelle D4 und verlängert sich dann mit jeder Zeile nach unten.

Wie man im Screenshot oben sieht, interessieren uns am Ende nur die Zeilen mit einem Null-Wert.

Schritt 2: VERGLEICH

Die nächste Schicht in der verschachtelten Formel ist die VERGLEICH-Funktion, welche die ZÄHLENWENN-Funktion umschließt:
{=VERGLEICH(0;ZÄHLENWENN($B$4:B4;$B$5:$B$24);0)}

Diese Formel wird in die erste Zeile als Matrix-Formel eingegeben und dann nach unten kopiert.

Zweite Hilfsspalte mit der VERGLEICH-Funktion

Zweite Hilfsspalte mit der VERGLEICH-Funktion

Wir suchen damit nach dem Wert 0 (= 1. Argument) innerhalb der Ergebnisse der ZÄHLENWENN-Funktion (= 2. Argument). Und dabei soll ein exakter Vergleich verwendet werden, daher im dritten Argument wieder der Wert 0.

Als Ergebnis erhalten wir die relative Position aller 0-Werte in der Liste.

Schritt 3: INDEX

Die alles umfassende INDEX-Funktion nimmt am Ende die Positionen, die VERGLEICH ermittelt hat und liefert die entsprechenden Namen zurück:

Das fertige Liste (inklusive WENNFEHLER)

Das fertige Liste (inklusive WENNFEHLER)

Die in Zelle D5 eingegebene Matrix-Formel wird dann bis ans Ende der Tabelle nach unten kopiert. Da die eindeutige Liste erwartungsgemäß weniger Einträge umfasst, gibt es einige #NV-Fehler. Diese lassen sich aber mit einer zusätzlichen WENNFEHLER-Funktion elegant unterdrücken:
{=WENNFEHLER(INDEX($B$5:$B$24;VERGLEICH(0;ZÄHLENWENN($F$4:F4;$B$5:$B$24);0));"")}

Und das war wieder einmal ein Beispiel dafür, wie vielseitig Matrix-Formeln sein können.

Übrigens:
Mein geschätzter Excel-Kollege Andreas Thehos, den ich auf den ExcellentDays 2018 kennenlernen durfte, stellt mit der AGGREGAT-Funktion eine andere interessante Lösung für dieses Problem vor:
Guckst du hier: Excel – Einträge einer Tabelle ohne Doppelte auflisten

 

Bitte weitersagen, wenn dir der Artikel gefallen hat!

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

17 Gedanken zu “Liste nur mit eindeutigen Werten erstellen

  • Jörg Böhmichen

    Hallo Martin,
    danke für Deinen neuen Excel-Tipp! Ich finde die Aufgabe realistisch, wobei es häufig Listen gibt, die zwar in der ersten Spalte denselben Namen gibt, in den Folgespalten aber verschiedene Daten stehen, z.B. Arbeitsstunden — diese müssten dann in der eindeutigen Liste mit einer passenden Aggregatfunktion zusammengefasst werden (SUM, MIN, MAX, AVG, …).
    Mir fällt auf, dass es nur die WENNFEHLER-Funktion erst ab Vers. 2007 und höher gibt, INDEX, VERGLEICH und ZÄHLENWENN habe ich in Excel 2002 auch schon. Kannst Du die entsprechenden Dateien in beiden Versionen zum Download anbieten? Also „??? bis 2003“ und „2007 und höher“? Vielen Dank!

    Freundliche Grüße
    Jörg

    • Martin Weiß Autor des Beitrags

      Hallo Jörg,

      das stimmt natürlich, dass manche Funktionen vor Excel 2007 noch nicht verfügbar sind. Allerdings ziehe ich hier auch die Grenze. Auch auf die Gefahr hin, dass ich jetzt vielleicht manche Leser verprelle, aber die ganz alten Versionen kann und möchte ich in meinen Artikeln nicht mehr berücksichtigen (und selbst Excel 2007 hat ja auch schon 11 Jahre auf dem Buckel…).

      Schöne Grüße,
      Martin

  • Sven Uhlig

    Hallo Martin,

    Deine Lösung hat mich daran erinnert, dass ich ja noch auf der Suche nach einer Lösung für folgendes Problem bin:

    Ich habe eine Tabelle mit Artikeln. In Spalte A steht eine Anzahl, in Spalte B die Artikelnummer, in Spalte C die Artikelbezeichnung, in Spalte D der Einzelpreis, in Spalte E der Gesamtpreis pro Artikel und in Spalte F muss eine Materialnummer händisch eingetragen werden. Die Materialnummer muss aus einer externen nicht per Excel abfragbaren Datenbank herausgesucht werden. Als Suchkriterium kann man die Artikelnummer oder die Artikelbezeichnung verwenden, da nicht jeder Artikel eine Artikelnummer hat. In der Tabelle kommen, manche Artikel mehrfach (doppelt, dreifach, usw.) vor.
    Damit ich weiß, welche Artikel mehrfach vorkommen habe ich mit „ZÄHLENWENN“ eine Bedingte Formatierung erstellt, wie Du es im Artikel „Das doppelte Lottchen …“ (https://www.tabellenexperte.de/dublettensuche-mit-excel/) beschrieben hast. Das hilft mir zwar schon mal weiter ist aber noch nicht perfekt und effektiv genug. Weil ja Artikel mit der gleichen Anzahl auch die gleiche Farbe haben. Das macht es z.B. bei 10 Artikeln, welche es jeweils 3x gibt, trotzdem noch nicht wesentlich übersichtlicher und einfacher bei der Erfassung der Materialnummern.

    Wenn ich jetzt für einen mehrfach vorkommenden Artikel die Materialnummer aus der Datenbank rausgesucht habe und diese in meiner Artikelliste eintrage möchte, dann wäre es doch sehr hilfreich, wenn hinter dem 1. Mehrfach-Artikel in meiner Liste, sagen wir mal in Spalte G, ein Hyperlink auf die Zelle F… des 2. gleichen Mehrfach-Artikels, stehen würde. Damit dieser 2. gleiche Mehrfach-Artikel einfach und schnell mit einem Mausklick angesteuert werden kann. Wenn es noch einen 3. gleichen Mehrfach-Artikel geben sollte, dann soll natürlich hinter dem 2. gleichen Mehrfach-Artikel ebenfalls ein Hyperlink zum 3. gleichen Mehrfach-Artikel stehen usw.

    Es müsste also in der Spalte G eine Formel stehen, die zum einen prüft ob es sich um einen Mehrfach-Artikel handelt (vermutlich mit „ZÄHLENWENN“, 1. Prüfung auf die Artikelnummer, wenn nicht vorhanden, dann auf die Artikelbezeichnung) und wenn ja, in welcher „ZEILE“ sich der nächste gleiche Mehrfach-Artikel befindet und dessen „ADDRESSE“ in einen „HYPERLINK“ schreiben. Gibt es keinen weiteren gleichen Mehrfach-Artikel, wird kein weiterer Hyperlink angezeigt/ausgegeben. Für jeden Mehrfach-Artikel muss natürlich der richtige Hyperlink zu seines gleichen hinterlegt werden.

    Wäre super wenn Du mir hier helfen könntest.

    Viele Grüße
    Sven

    • Sven Uhlig

      Hallo Martin,

      ich habe mittlerweile eine Lösung für mein Problem gefunden und beantworte meine Frage mal selbst, für den Fall dass noch jemand so ein Thema hat.
      Die benötigte Formel um einen Hyperlink zu den weiteren Mehrfach-Datensätzen mit Prüfung auf die Spalte B (Artikelnummer) zu erzeugen, lautet wie folgt (bei mir steht diese in Zelle G2):
      =WENN(ZÄHLENWENN($B3:$B$100;$B2);HYPERLINK(„#F“&VERGLEICH($B2;$B3:$B$100;0)+ZEILE();“▼NÄCHSTER MEHRFACH-DATENSATZ▼“); „“)
      Ich habe die Formel auch noch in der Spalte H2 zur Prüfung auf die Spalte C (Artikelbezeichnung) eingetragen. Danach habe ich diese runter kopiert. Ich erhalte also je einen Hyperlink auf den nächsten Mehrfach-Datensatz, welcher in Spalte F (Materialnummer) landet.

      Eine Frage habe ich jedoch noch:
      Wenn ich eine Zeile ausschneide und an einer anderen Stelle wieder einfüge, dann wird bei ZÄHLENWENN und auch bei VERGLEICH die benannte Zelle in dem der definierte Bereich beginnt nicht automatisch angepasst. Um beim obigen Beispiel zu bleiben, ich schneide Zeile 2 aus
      =WENN(ZÄHLENWENN($B3:$B$100;$B2);HYPERLINK(„#F“&VERGLEICH($B2;$B3:$B$100;0)+ZEILE();“▼NÄCHSTER MEHRFACH-DATENSATZ▼“); „“)
      und füge diese als Zeile 8 wieder ein, dann sieht die Formel so aus:
      =WENN(ZÄHLENWENN($B3:$B$100;$B8);HYPERLINK(„#F“&VERGLEICH($B8;$B3:$B$100;0)+ZEILE();“▼NÄCHSTER MEHRFACH-DATENSATZ▼“); „“)

      Wieso wird B3 nicht automatisch von Excel auf B9 angepasst?

      Viele Grüße
      Sven

      • Martin Weiß Autor des Beitrags

        Hallo Sven,

        ich muss schon sagen: Das ist eine sehr coole Lösung, die du da gefunden hast. Hut ab!
        Das Problem beim Ausschneiden ist ein generelles Excel-Problem bzw. eine Eigenheit von Excel. Das kannst du nur umgehen, indem du stattdessen die Zeile kopierst, einfügst und die Ursprungszeile anschließend wieder löscht. Beim Kopiervorgang werden die Bezüge angepasst, beim Ausschneiden leider nicht.

        Schöne Grüße,
        Martin

  • Christian

    Hallo Martin,
    extrem hilfreiche Seite, sie hat mir schon sehr oft weitergeholfen!
    Ich habe versucht, deine Formel zu erweitern und noch einen Jahresbezug einzubauen. Ich bin kläglich gescheitert!!
    Ich würde deine Formel gerne soweit erweitern, dass auch das Jahr (fix in einer Zelle) als Kriterium zählt. Sprich ich habe 2 Spalten Jahr & Name und das Ergebnis ist eine Liste nur eindeutigen Werten auch in Abhängigkeit vom Jahr. Hoffe ich habe mich verständlich ausgedrückt. 🙂
    Wäre das möglich?

    Schon mal vielen Dank für deine Antwort!
    Beste Grüße
    Christian

    • Martin Weiß Autor des Beitrags

      Hallo Christian,

      ja, das geht grundsätzlich auch. Nehmen wir an, in meinem Beispiel steht vor jedem Namen in Spalte A noch die Jahreszahl. Dann würde die Formel so aussehen:
      {=WENNFEHLER(INDEX($B$5:$B$24&$A$5:$A$24;VERGLEICH(0;ZÄHLENWENN($F$4:F4;$B$5:$B$24&$A$5:$A$24);0));““)}

      Schöne Grüße,
      Martin

  • Sascha Broß

    Hallo Martin,

    ersteinmal: Super Seite, hat mir schon bei einigen kniffligen Sachen geholfen. Nach einer einfachen Lösung, aus einer Tabelle, mit mehrfachen gleichen Einträgen in einer Spalte eine Liste ohne Duplikate zu erstellen, und dann auch noch mit so einer super Erklärung, habe ich lange suchen müssen.
    Aber nun verzweifel ich, weil ich einen Schritt weitergehen möchte aber nicht weiter weiss:
    Die Tabelle hat bei mir noch einige Spalte mehr, nämlich z.B. verschiedenene Lieferanten, bei denen die Leute aus der Namensliste bestellen (Und noch weitere, aber für mein Problem nicht wichtige). Nicht jeder bestellt bei allen Lieferanten, aber es kommt vor, dass einige mehrmals bei einem Lieferanten bestellen. Und so habe ich in der zweiten Spalte Einträge, die doppelt und häufiger vorkommen. Ich nutze jetzt die erste eindeutige Namensliste als Quelle in einem Kombinationsfeld und möchte nun, für einen ausgewählten Namen eine neue Liste erhalten, in der nur die Lieferanten stehen, bei denen die entsprechende Person bestellt hat, aber die Lieferanten sollen nur einmal drin stehen, auch wenn sie in der Liste mehrmals vorkommen. Ich möchte nämlich die Umsätze einer Person zusammenfassen, ich brauche nicht alle Lieferungen einzeln aufgelistet. Und es soll so übersichtlich sein wie nur irgendwie möglich. Also ein Dropdown für die Personen, ein weiteres Dropdown, in dem automatisch nach ausgewählter Person, nur die zugehörigen Lieferanten stehen. Da drunter kommt eine Zusammenfassung, die ich auch ohne weitere Probleme aus der Datentabelle erhalte.

    Hast du einen Tipp für mich?

    Lg
    Sascha

    • Martin Weiß Autor des Beitrags

      Hallo Sascha,

      freut mich, wenn du hier ein paar Lösungen gefunden hast.
      Was dein Problem angeht: Ohne mich jetzt näher in die Thematik reinfuchsen zu können, stellt sich mir die Frage, ob du es nicht lieber mit einer Pivot-Tabelle versuchen möchtest? Dort kannst du ja beliebig filtern und verdichten. Vielleicht wäre das ja auch eine Möglichkeit.

      Schöne Grüße,
      Martin

      • Sascha Broß

        Hallo Martin,

        an Pivot-Tabellen habe ich dabei gar nicht gedacht. Muss ich mal probieren, ob das von mir angestrebte Ziel damit zu erreichen ist. Danke 🙂

        Lg
        Sascha

  • Helge

    Hallo,

    der Fehlerteufel war da:
    {=INDEX($B$5:$B$24;VERGLEICH(0;ZÄHLENWENN(D$4:$F4;$B$5:$B$24);0))}
    ist die erste gezeigte Formel. Die muss aber:
    {=INDEX($B$5:$B$24;VERGLEICH(0;ZÄHLENWENN(D$4:$D4;$B$5:$B$24);0))}
    lauten (Im Zählenwenn-Teil ein „D“ statt eines „F“. Siehe auch den Screenshot, der das ganze erläutert – da ist es nämlich korrekt.

    Viele Grüße und danke für den tollen Artikel!

    • Martin Weiß Autor des Beitrags

      Hallo Helge,

      vielen Dank für den Hinweis, der Fehler im Text ist jetzt korrigiert.

      Schöne Grüße,
      Martin

  • Stephan

    Hallo Martin,

    vielen Dank für die wirklich gute Seite.

    Ich habe allerdings ein Problem, das ich mir gerade nicht erklären kann.

    Ich habe die Formel von einer Spalte auf eine Zeile Umgebaut:

    =INDEX($BM3:$DR3;VERGLEICH(0;ZÄHLENWENN($BL3:BT3;$BM3:$DR3);0))

    Problem ist nun, den ersten Wert im Ergebnis ist eindeutig, die weiteren doppeln sich.

    Beispiel:
    104556 104557 104557 104557 104557 104557 104557 104557 104557 104558 104558 104559 104559 104559

    Kann die Formel nur auf Spalten angewandt werden oder stehe ich hier irgendwo auf dem Schlauch?

    • Stephan

      Ich habe meinen Fehler gefunden.

      Habe es jetzt auch verstanden. Ich hatte das Zählenwenn auf die Ausgangsdaten gelegt nicht auf den Zielbereich.

      =INDEX(Rohdaten!$BM2:$DR2;VERGLEICH(0;ZÄHLENWENN($A2:A2;Rohdaten!$BM2:$DR2);0))

      Funktioniert jetzt einwandfrei.