Liste nur mit eindeutigen Werten erstellen 91

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

(Artikel überarbeitet am 26.10.2021)

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 nur 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 Beispieldatei 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

Ich werde dazu zwei Lösungsvarianten vorstellen:
Eine „universelle“ Variante, die zugegebenermaßen etwas komplizierter ist, aber dafür mit jeder Excel-Version funktioniert. Und für diejenigen, die mit Microsoft 365/Office 365 arbeiten und die somit die neuen dynamischen Arrayfunktionen nutzen können, gibt es am Ende noch eine deutlich einfachere Lösung.

Beginnen wir aber mit der universellen Variante, die wir nun in mehreren Schritten erarbeiten.

Für Ungeduldige: die (fast) 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))}
Ich empfehle dir aber trotzdem, hier noch nicht mit dem Lesen aufzuhören, denn es gibt weiter unten noch ein paar interessante Ergänzungen.

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));"")}

Ü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

Sonderfall: Leere Zellen

Meine oben vorgestellte Lösung hat allerdings noch einen Schönheitsfehler. Wenn die Ausgangsliste nämlich leere Einträge enthält, wird dafür in der Zielliste eine Null angezeigt:

Leere Zellen ergeben einen Nullwert

Leere Zellen ergeben einen Nullwert

Der Grund dafür ist, dass auch ein leerer Eintrag erst einmal gezählt wird. Und wenn man mit einer Formel auf eine leere Zelle verweist, wird eben die Ziffer Null dargestellt. Um dieses Problem zu lösen, muss unsere Formel nochmal ein wenig angepasst und erweitert werden.

Ich prüfe jetzt zusätzlich, ob die jeweilige Zelle in der Ausgangsliste leer ist:
$B$5:$B$24<>""

Als Ergebnis erhält man ein WAHR für jeden Eintrag, der nicht leer ist und ein FALSCH für jede leere Zelle. Um aus WAHR eine 1 und aus FALSCH eine 0 zu machen, setze ich die Prüfung in Klammen und multipliziere das Ganze mit 1:
($B$5:$B$24<>"")*1

Das heißt, für einen Eintrag, der nur einmal in der Liste vorkommt, ergibt die Zählung jetzt den Wert 1, für einen Namen, der zweimal vorkommt, 2 und so weiter. Leere Zellen hingegen ergeben den Wert 0. Und damit müssen wir jetzt in der VERGLEICH-Funktion nicht mehr nach dem Wert 0 suchen, sondern nach 1.

Die gesamte neue Formel sieht für Zelle F5 also folgendermaßen aus:
=WENNFEHLER(INDEX($B$5:$B$24;VERGLEICH(1;ZÄHLENWENN($F$4:F4;$B$5:$B$24)+($B$5:$B$24<>„“)*1;0));““)

Angepasste Formel filtert Nullwerte aus

Angepasste Formel filtert Nullwerte aus

Bitte auch hier unbedingt darauf achten, dass die Formel mit der Tastenkombination Strg+Umschalt+Eingabe abgeschlossen wird!

Ich weiß, die Lösung ist nicht ganz trivial. Aber es funktioniert 😉

Deutlich bequemer mit Excel 365

Wenn du das Glück hast, Excel aus Microsoft 365/Office 365 zu nutzen, dann habe ich für dich eine wesentlich komfortablere Lösung, als die oben beschriebenen Matrixformeln.

Denn dann stehen dir auch die neuen dynamischen Array-Funktionen zur Verfügung, die vieles von dem auf den Kopf stellen, wie man in der Vergangenheit bestimmte Probleme in Excel lösen musste.

Für unsere Zwecke sind das die Funktionen EINDEUTIG und FILTER.

Wie der Name schon vermuten lässt, liefert EINDEUTIG eine eindeutige Liste ohne Duplikate. Man gibt dabei lediglich die Ausgangsliste an und erhält sofort das Ergebnis. Da es sich um eine dynamische Funktion handelt, muss die Formel nur in die erste Zelle eingetippt werden. Sie läuft dann automatisch in soviele Zeile über, wie eben gerade notwendig.
=EINDEUTIG(B5:B24)

Nur in Microsoft 365: Die EINDEUTIG-Funktion

Nur in Microsoft 365: Die EINDEUTIG-Funktion

Da in dieser einfachen Form leere Zellen ebenfalls als Nullwert angezeigt werden, brauchen wir noch eine weitere Zutat, nämlich die ebenfalls neue FILTER-Funktion. Für sich genommen filtert sie eine Liste nach den angegebenen Kriterien. Um beispielsweise alle leeren Zellen auszufiltern, sieht die Funktion so aus:
=FILTER(B5:B24;B5:B24<>"")

Nur in Microsoft 365: Die FILTER-Funktion

Nur in Microsoft 365: Die FILTER-Funktion

Und wenn man jetzt die beiden Funktionen kombiniert, erhält man eine extrem einfache und höchst komfortable Lösung:
=EINDEUTIG(FILTER(B5:B24;B5:B24<>""))

EINDEUTIG kombiniert mit FILTER

EINDEUTIG kombiniert mit FILTER

Wie gesagt, die Voraussetzung für diese Variante ist Microsoft 365/Office 365. Wenn du Excel 2019 oder älter einsetzt oder Dateien mit anderen Excel-Anwendern austauschen musst, die solche Versionen im Einsatz haben, bleibt dir leider nur die kompliziertere erste Formellösung.

 

Wenn dir der Artikel gefallen hat: Bitte weitersagen!

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 zu Thomas Antworten abbrechen

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

91 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

      • Jim

        Gibt es denn auch eine Möglichkeit, mit der man mit der zusätzliche Variable nicht die Werteliste vergrößert und einen Wert je Jahr erlaubt, sondern nur alle eindeutigen Werte aus dem Jahr (oder Kriterium) X erlaubt?

        Etwa eine nach dem Schema:

        Zählenwenns($F$4:F4 ; $B$5:$B$24 ; $A$5:$A$24 ; „Kriterium“) ?

        Hab da gerade alle denkbaren Kombinationen (inkl. Summenprodukte) durchprobiert, allerdings ist mir bisher keine brauchbare Lösung eingefallen.

        Schöne Grüße,
        Jim

        PS: Hut ab vor deinem Können und deiner Website! Sie hilft immer wieder hervorragend und ist großartig erklärt!

        • Jim

          Hallo Martin,

          meine Lösung für das Ganze:

          {=INDEX($B$5:$B$24;VERGLEICH(0;
          WENN($A$5:$A$24=$A$1;ZÄHLENWENN(F$4:$F4;$B$5:$B$24);1);
          0))}

          A1 ist das Filterkriterium, für das die Werte ausschließlich rausgesucht werden sollen, A4:A24 enthält entsprechende Kriterien. Sobald Spalte A also den Wert enthält, wird nach Duplikaten gesucht, ansonsten wird gleich 1 (oder beliebiger Wert ungleich 0) angenommen. Die Formel sollte somit für beliebig viele Filterkriterien anwendbar sein, einfach entsprechend die Wenn-Bedingung anpasen.

          • Martin Weiß Autor des Beitrags

            Hallo Jim,

            ich habe deine Lösung gerade nachgestellt und die sieht sehr gut aus. Vielen Dank, dass du sie hier mit uns teilst!

            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.

  • Fabian

    Hallo Martin!
    Erst einmal vielen Dank für deine ganzen hilfreichen Artikel, diese haben mir schon sehr oft weitegeholfen!

    Ich habe ein Problem mit dieser Formel. Und wahrscheinlich sitzt auch hier das Problem vor dem Computer 🙂

    Die Vergleichs-Funktion liefert ja einige „doppelte“ Werte (im Beispiel die 15 und 19). Ich verstehe nicht ganz, wie die Index-Funktion dann nur eindeutige Werte liefern kann. Wenn ich das Beispiel nachbaue, und die Index-Funktion kopiere, werden mir die Datensätze mit dem Index 15 und 19 mehrfach ausgegeben.

    Viele Grüße
    Fabian

    • Martin Weiß Autor des Beitrags

      Hallo Fabian,

      diese Formel ist tatsächlich nicht ganz einfach zu durchschauen. Die INDEX-Funktion liefert deshalb keine doppelten Werte, weil über die ZÄHLENWENN-Funktion ja nur die Zeilen zurückgegeben werden, die den Wert 0 liefern.
      Hast du beim Nachbauen auf die korrekten Bezüge geachtet (also die Position der Dollarzeichen)? Und auf das Abschließen der Formel mit Strg+Umschalt+Eingabe? Denn nur dann wird das Ganze zur Matrixformel und liefert das gewünschte Ergebnis.

      Schöne Grüße,
      Martin

      • Fabian

        Hallo Martin,

        vielen Dank für deine Rückmeldung.

        Nachdem ich die Iterative Berechnung in den Optionen aktiviert habe, funktioniert die Formel bei mir einwandfrei.

        VG
        Fabian

  • Thomas

    Hallo Martin,

    ich möchte einfach nur Danke sagen. Dein Beispiel hat mir geholfen endlich ein Problem zu lösen, an dem ich schon sehr lange sitze!

    Grüße, Thomas

    • Martin Weiß Autor des Beitrags

      Hi Thomas,

      vielen Dank für das nette Feedback. Freut mich immer sehr, wenn die Leser hier konkrete Lösungen finden.

      Schöne Grüße,
      Martin

  • Harald Lossner

    Hallo Martin,
    vor ein paar Tagen schrieb ich eine Frage über die Index-Funktion.
    Habe den Fehler soweit eingegrenzt, dass der Fehler nicht mehr kommt, wenn die Spalte beim Bezug nicht höher als „A“ ist.
    In einigen Tabellen geht es sogar bis C1 ….. , ab D1 bringt Excel den Bezug-Fehler. Auch nach einer Online-Installation war der „Fehler“ immer noch vorhanden. Ist es in Excel jetzt üblich eine Liste/ Bezug mit A1 zu beginnen?
    Über eine Antwort würde ich mich freuen.

    • Martin Weiß Autor des Beitrags

      Hi,

      freut mich, wenn es doch noch geklappt hat (im Zweifelsfall einfach die Beispieltabelle herunterladen).

      Schöne Grüße,
      Martin

  • Oli

    Hallo Martin,
    meine Liste enthält leere Zellen, da dort weitere Einträge hin zu kommen. Jetzt zeigt mir die Liste ohne Duplikate eine 0 an die nicht in meiner Liste steht. wie bekomme ich diese weg? Gibt es da eine Lösung?
    Freundliche Grüße Oli

    • Martin Weiß Autor des Beitrags

      Hallo Oli,

      das scheint in der Tat etwas tricky zu sein. Auf Anhieb habe ich da leider auch keine Lösung.

      Schöne Grüße,
      Martin

  • Siggi

    Hallo Martin,
    ich hatte heute genau diesen Anwendungsfall. Deine coole Lösung hat mir viel Zeit erspart. Dafür möchte ich dir herzlich danken.
    Ich wünsche ein gutes 2020, schöne Grüße
    Siggi

    • Martin Weiß Autor des Beitrags

      Hallo Siggi,

      freut mich, wenn die Lösung geholfen hat.

      Auch dir ein gutes und gesundes neues Jahr!
      Martin

  • Gerrit

    Hallo zusammen

    erstmal vielen Dank an alle für die Tipps. Ich bin meiner Lösung schon recht nahe gekommen.
    Ich würde mir gerne eine Liste mit eindeutigen Werte anzeigen lassen, wenn 2 Bedingungen erfüllt sind.

    Mein Versuch sieht wie folgt aus:

    =WENNFEHLER(INDEX(VW!$B$16:$B$10000;VERGLEICH(0;WENN(UND(VW!C:C=$B$2;VW!I:I=$A$73);ZÄHLENWENN($A$57:A57;VW!$B$16:$B$10000);1);0));““)

    Leider greift dann die Wennfehlerformel und mir wird „“ angezeigt.
    @Martin: Hast du eine Idee, woran es liegen könnte.

    Vielen Dank im Voraus

    • Martin Weiß Autor des Beitrags

      Hallo Gerrit,

      weiter oben in den Kommentaren hat Christian schon eine ähnliche Frage gestellt, dazu hatte ich eine Formel für einen Lösungsvorschlag geschrieben. Probiere doch den mal aus.

      Schöne Grüße,
      Martin

      • Gerrit

        Hallo Martin,

        dies hatte ich probiert, hatte aber leider nicht funktioniert.
        Habe es nun über ein Hilfstabelle gelöst. Ist gefühlt bei einer grossen Datenbasis auch schneller.
        Trotzdem besten Dank.

        Gruss
        Gerrit

  • Oliver Ulrich

    Hallo Martin,
    vielen Dank für deine hilfreichen Erklärungen und Tipps, die mich schon vielfach weitergebracht haben.
    Bei meinem jetzigen Problem bin ich genau auf diesen Tipp gestossen „Liste nur mit eindeutigen Werten erstellen“ nun habe ich aber das Problem, dass ich noch eine Bedingung einbauen möchte bei der Namensliste, wie z.B. die Region XY erfüllt sein muss, damit die Namen übernommen werden können. Wie würde dann die Formel aussehen?

    Danke für Deine Hilfe!

    Grüsse
    Oliver

    • Martin Weiß Autor des Beitrags

      Hallo Oliver,

      eine ähnliche Frage zu zwei Kriterien habe ich weiter oben schon beantwortet (Kommentar von Christian).

      Schöne Grüße,
      Martin

  • Rico

    Hallo Martin,
    vielen Dank für deine Excel-Lösung!
    Ich bräuchte eine Formel aus der ich aus einer ganzen Tabelle eine eindeutige Liste erstellen kann. Ich habe versucht diese Formel anzupassen auf eine ganze Tabelle:
    {=WENNFEHLER(INDEX($C$2:$E$999;VERGLEICH(0;ZÄHLENWENN(N$1:$N1;$C$2:$E$999);0));““)}
    Diese Formel funktioniert jedoch so gar nicht, mache ich etwas falsch oder ist das die falsche Formel für mein Problem?
    Vielen Dank für deine Unterstützung.
    Freundliche Grüsse
    Rico

    • Martin Weiß Autor des Beitrags

      Hallo Rico,

      mit einer „normalen“ Formel dürfte das sehr schwer werden. Alternativ sehe ich zwei Möglichkeiten:
      Entweder
      Spezialfilter im Menü „Daten | Erweitert“. Dort die Optionen „An eine andere Stelle kopieren“ und „Keine Duplikate“ auswählen
      oder
      In Office 365 die neue EINDEUTIG-Funktion verwenden. Die hatte ich in diesem Artikel schon mal beschrieben: Ein Blick in die Zukunft: Dynamische Arrayfunktionen

      Schöne Grüße,
      Martin

  • Herbert Pischl

    Hallo,
    finde das auch eine super Funktion.
    Warum funktioniert sie bei mir nicht mehr wenn ich die Namensliste erweitere?
    Dann sind bei mir alle Zellen, auch die schon vorhanden Namen des Beispiels, leer.
    Danke für euren Rat.
    gruß Herbert

    • Martin Weiß Autor des Beitrags

      Hallo Herbert,

      warum die Liste danach nicht mehr funktioniert, erschließt sich mir auch nicht. Ich gehe mal davon aus, dass du alle Zellenbezüge in den Formeln auf die neue Liste angepasst hast. Und du hinterher auch die Formel wieder mit Strg+Umschalt+Eingabe abgeschlossen hast, oder?

      Schöne Grüße,
      Martin

  • Agnes Richter

    Hallo,
    vielleicht kann mir jemand helfen. Wir haben drei verschieden Tabellen. Tabelle 1 (alte Provision), Tabelle 2 (neue Provision), Tabelle 3 (Laufzettel). DIe Tabelle 3 soll sich den Wert aus Tabelle 1 oder 2 ziehen. Wenn in Tabelle 1 etwas eungetragen ist, dannn soll der Wert aus Tabelle 1 gezogen werden, wenn der Vetrag aber 0,00 steht, dann soll der Wert aus Tabelle 2 gezogen werden. Wie könnte die Formel aussehen?
    Vielen Dank für die Hilfe!

    Schöne Größe
    A.R.

    • Martin Weiß Autor des Beitrags

      Hallo Agnes,

      ohne eine genaue Beschreibung der Tabellen und der Art und Weise der Suche kann ich nicht wirklich weiterhelfen. Die Lösung dürfte aber über eine WENN-Funktion laufen, oder vielleicht auch über eine WENNFEHLER-Funktion. Für einen genaueren Hinweis bräuchte ich schon eine detaillierter Beschreibung des Tabellenaufbaus und nach welchen Kriterien gesucht werden soll.

      Schöne Grüße,
      Martin

    • Martin Weiß Autor des Beitrags

      Hallo Heiko,

      absolut! Wer Office 365 (bzw. Microsoft 365) in der aktuellsten Version nutzt, hat damit eine sehr elegante und einfache Lösung.

      Schöne Grüße,
      Martin

  • Michael

    Hallo zusammen,
    gesucht und gefunden. Die Formel macht was sie soll. Bei mir jedoch braucht sie bei ca. 3400 Einträgen (Straßennamen einer Stadt) eine ganze Weile. Jede Änderung, wie Verschiebung der Tabelle oder Einfügung, zwingt das System in die Knie.
    Eine weitere Lösung, die mit einem Zwischenschritt zum Ergebnis führt ist
    {=WENNFEHLER(INDEX(B:B;AGGREGAT(15;6;ZEILE(Tab_65[[#Alle];[Straße]])/(Tab_65[[#Alle];[Anzahl]]=1);ZEILE()-1);1);““) }
    Zwischenschritt für Tab_65[Anzahl] : =ZÄHLENWENN($B$2:[@Straße];[@Straße]).
    In Spalte B (Tab_65 [Straße] stehen die ca. 3400 Straßennamen.
    Hier mit erheblichen Zeitvorteil, quasi sofort aber auch einem, für mich, Problem*.
    Leider kann ich die Quelldaten und aber auch die Berechnungsdaten nicht verschieben. Soll heißen, das Einfügen von Zellen (Zeilen) gleichmäßig über den Daten (Quelle und Berechnung) lässt keine Berechnung mehr zu. Auch kann ‚ich‘ nur die Berechnungszellen nicht verschieben, da mir offenbar die Funktion Zeile() ein Strich durch die Rechnung macht. Hier besteht für mich die Frage, wie eine Tabelle unveränderlich in die Funktion (Zeile() eingefügt werden kann, sodass eben nicht nur in der Zeile, mit gleichem Adressenanteil (Bx) gesucht wird?
    Das Nachvollziehen der Formel, trotz Erklärung funktioniert ebenso nicht, sodass die Daten (Straße) in die ursprüngliche Vorlage eingesetzt, die Formel angepasst und mit „{ }“ geschlossen wurden.

    Grüße und ein Danke von hier.
    *wo das Problem ist, ist ganz klar!

    • Martin Weiß Autor des Beitrags

      Hallo Michael,

      es ist richtig, dass insbesondere Matrix-Formeln bei großen Datenmengen an ihre Grenzen kommen, was die Performance angeht. In solchen Szenarien würde ich stattdessen das Problem mit Power Query lösen. Damit lassen sich auch große Datenmengen problemlos verarbeiten und es geht – einmal eingerichtet – fast automatisch.

      Schöne Grüße,
      Martin

  • Karsten

    Super Lösung, aber ich stehe noch vor einem Problem:
    Ich habe mehrere Matritzen, die ich dabei durchsuchen lassen will
    (und bestenfalls zusätzlich: Das ganze soll noch nach Werten in anderen Spalten aufsummiet und absteigend sortiert werden.)
    Gibt es dafür ne geeignete Lösung?

    Meine Idee, die Matritzen mit „&“ zu verbinden, scheint hier nicht zu funktionieren…

    Vielen Dank und schöne Grüße

    • Martin Weiß Autor des Beitrags

      Hallo Karsten,

      mit einer Formellösung wird’s da schlecht aussehen. Dafür würde ich eher Power Query bemühen.

      Schöne Grüße,
      Martin

  • Peter

    Echt Super, hat mir schon viel weitergeholfen.
    Kann man die Werte auch nach der größten Zahl in einem Rutsch mit sortieren lassen?
    Ich habe schon versucht mit MAX oder KGRÖSSTE anzusetzen, aber ohne Erfolg.

    Gruß und Danke im Voraus
    Peter

    • Martin Weiß Autor des Beitrags

      Hallo Peter,

      mit normalen Formeln dürfte das sehr komplex werden, spontan habe ich da keine Lösung für dich. Allerdings gibt es mit Microsoft 365/Office 365 neue Funktionen, mit denen das ganze Problem (also eindeutige Werte und sortieren) ganz einfach und gelöst werden können: EINDEUTIG und SORTIEREN

      Vielleicht hilft das ja weiter.

      Schöne Grüße,
      Martin

  • Michel

    Hallo Martin

    erst einmal vielen Dank für die zahlreichen Tipps auf deiner Seite.
    Mein aktuelles Problem ist, dass ich aus einer Liste mit Aufträgen, mir die Kunden herausfiltern möchte (also jeweils 1mal) und dann die Anzahl der Aufträge zuordnen. Deine Lösung scheint dafür auch wie gemacht.
    Ich wollte jedoch die Liste auf einem anderen Tabellenblatt erzeugen um etwas mehr Übersichtlichkeit zu bewahren aber die Ergebnisse der ZÄHLENWENN Funktion unterscheiden sich dann.
    Meine Funktion auf deine Vorlage angewendet sieht dann so aus: =ZÄHLENWENN(Eindeutige_Liste_2!$B$4:B4;Eindeutige_Liste_2!$B$5:$B$24)
    Gibt es da ein bekanntes Problem?

    LG Michel

    • Martin Weiß Autor des Beitrags

      Hallo Michel,

      nein, es spielt keine Rolle, ob die Ausgabeliste in einem anderen Arbeitsblatt liegt. Du hast vermutlich nur einen Fehler in der ZÄHLENWENN-Funktion. Der erste Parameter darf sich nicht auf das andere Blatt beziehen, sondern auf das aktuelle Blatt, in dem die Formel steht. Also etwa so:

      =ZÄHLENWENN($B$4:B4;Eindeutige_Liste_2!$B$5:$B$24)

      Schöne Grüße,
      Martin

  • Gabi Hilebrand

    Hallo
    Ich bin das Problem vor dem Computer.
    Ich habe eine Namensliste mit Name, dann 1 bis 4 Vornamen. Ich soll die Liste nun so bearbeiten, dass nur noch Name, Vorname im Feld stehen. Gibt es da eine Möglichkeit für den DAU??

    • Martin Weiß Autor des Beitrags

      Hallo Gabi,

      sind wir nicht alle manchmal das Problem vor dem Computer? 🙂
      Aus deiner Beschreibung geht noch nicht hervor, ob in der bisherigen Namensliste der Name und jeder Vorname in jeweils einer eigenen Spalte stehen oder ob jetzt schon alle Bestandteile in einer einzigen Spalte zusammengefasst sind. Falls alles schon in einer Spalte steht: Gibt es irgendein Trennzeichen zwischen den Vornamen, wie zum Beispiel ein Komma? Oder sind sie nur mit Leerzeichen getrennt?

      Und vielleicht verrätst du uns noch, mit welcher Excel-Version du arbeitest. Manchmal kann das wichtig sein bei der Lösungsfindung.

      Schöne Grüße,
      Martin

      • Gabi Hilebrand

        Hallo Martin
        Vielen Dank, dass du dich meinem Problem annimmst.
        Es steht alles in einer einzigen Spalte: „Name, Vorname Vorname Vorname“ – (es handelt sich um 1 bis 4 Vornamen) Nach Bereinigung soll nur noch der Name, das Komma und der erste Vorname nach dem Komma stehen bleiben – also Mustermann, Max Josef Theodor soll zu Mustermann, Max reduziert werden. Die Vornamen sind nur durch ein Leerzeichen getrennt. Ich habe Excel 2013 mit Windows10 auf meinem Rechner.

        • Martin Weiß Autor des Beitrags

          Hallo Gabi,

          danke, jetzt ist es klarer 🙂
          Im Grunde muss man also den Text links vom zweiten Leerzeichen nehmen. Und wenn es kein zweites Leerzeichen gibt, weil nur ein Vorname enthalten ist, dann wird der ganze Text übernommen.

          Die Formel dafür könnte so aussehen:
          =WENNFEHLER(LINKS(A1;FINDEN(“ „;A1;FINDEN(„,“;A1)+2));A1)

          Die innere FINDEN-Funktion sucht nach der Position des Kommas und addiert dazu den Wert zwei, um am ersten Leerzeichen vorbeizukommen.
          Die äußere FINDEN-Funktion sucht dann ab dieser Position nach dem nächsten (= zweiten) Leerzeichen. Damit weiß die LINKS-Funktion, wieviele Zeichen benötigt werden.
          Die umschließende WENNFEHLER-Funktion greift dann, wenn es kein zweites Leerzeichen gibt.

          Schöne Grüße,
          Martin

  • Oliver Harder

    Hallo Martin,
    zuerst einmal vielen Dank für die anschaulichen und nachvollziehbaren Anleitungen. Die Formel für Liste mit eindeutigen Werten funktioniert nur bedingt bei mir. Ich habe in einer Spalte Fallnummern, die alle mehrfach vorkommen und möchte mit der vorgestellten Formel eindeutige Werte bekommen, d.h. jede Fallnummer nur noch einmal. Leider werden trotzdem Fallnummern mehrfach gezählt. Gibt es eine Obergrenze für die Zeilen (ich habe 14413) oder ist mein Rechner zu schwach?

    Herzliche Grüsse,

    Oliver

    • Martin Weiß Autor des Beitrags

      Hallo Oliver,

      bei der vorgestellten Formel handelt es sich um eine Matrixformel, die grundsätzlich dazu neigen, sehr rechenintensiv zu sein. Bei einer dermaßen großen Anzahl, wie du sie beschreibst, wird vermutlich der Rechner an seine Grenzen kommen.
      Ich empfehle dir daher eine wesentlich einfachere Variante, völlig ohne Formeln: Eine Pivot-Tabelle. Schneller kann man keine eindeutige Liste erstellen. Einfach den Datenbereich markieren, dann Menü „Einfügen | PivotTable“, danach das Feld mit den Fallzahlen in den Zeilenbereich ziehen, fertig.

      Schöne Grüße,
      Martin

  • Theresa

    Hallo Martin,

    ich habe ein Problem mit der Zählenwenn Funktion. Ich hab damit eigentlich schon gearbeitet, aber diesmal bin ich am verzweifeln.
    Ich gebe die Funktion genauso wie du ein, aber es zählt falsch.

    Namensliste
    Heike Urner 0 =ZÄHLENWENN($B$4:B4;$B$5:$B$24)
    Doreen Ackermann 1 =ZÄHLENWENN($B$4:B5;$B$5:$B$24)
    Diana Freitag 1 =ZÄHLENWENN($B$4:B6;$B$5:$B$24)
    Maria Bergmann 1 =ZÄHLENWENN($B$4:B7;$B$5:$B$24)
    Dieter Hahn 1 =ZÄHLENWENN($B$4:B8;$B$5:$B$24)
    Daniel Traugott 1 =ZÄHLENWENN($B$4:B9;$B$5:$B$24)
    Markust Förster 1 =ZÄHLENWENN($B$4:B10;$B$5:$B$24)
    Sandra Goldschmidt 1 =ZÄHLENWENN($B$4:B11;$B$5:$B$24)
    Max Reiniger 1 =ZÄHLENWENN($B$4:B12;$B$5:$B$24)
    Jessika Friedmann 1 =ZÄHLENWENN($B$4:B13;$B$5:$B$24)
    Dieter Hahn 1 =ZÄHLENWENN($B$4:B14;$B$5:$B$24)
    Doreen Ackermann 1 =ZÄHLENWENN($B$4:B15;$B$5:$B$24)
    Max Reiniger 1 =ZÄHLENWENN($B$4:B16;$B$5:$B$24)
    Heike Urner 1 =ZÄHLENWENN($B$4:B17;$B$5:$B$24)
    Karl Obermeier 2 =ZÄHLENWENN($B$4:B18;$B$5:$B$24)
    Dieter Hahn 2 =ZÄHLENWENN($B$4:B19;$B$5:$B$24)
    Daniel Traugott 2 =ZÄHLENWENN($B$4:B20;$B$5:$B$24)
    Jessika Friedmann 2 =ZÄHLENWENN($B$4:B21;$B$5:$B$24)
    Max Mustermann 2 =ZÄHLENWENN($B$4:B22;$B$5:$B$24)
    Doreen Ackermann 2 =ZÄHLENWENN($B$4:B23;$B$5:$B$24)

    Kann es leider nicht besser reinkopieren.
    Bei mir wird schon ab dem 2. Namen nicht mehr die 0 angezeigt.

    Kannst du mir bitte weiterhelfen, was hier falsch ist?

    Viele Grüße und Danke
    Theresa

    • Martin Weiß Autor des Beitrags

      Hallo Theresa,

      ich empfehle dir, einfach die Beispieldatei herunterzuladen (Link ist oben im Artikel), dort kannst Du dann alles nochmal überprüfen.

      Schöne Grüße,
      Martin

  • Ingo

    Hallo Martin,

    danke für Formel und Erklärung. Eine Frage habe ich:

    Warum verwendest Du hier überhaupt eine Matrixformel? Wenn ich die Formel als „Nichtmatrixformel“ anwende, funktioniert das ebenfalls. Ich stehe mit Matrixformeln ohnehin noch etwas auf Kriegsfuß…

    Ich habe es übrigens mit großen Listen zu tun (>100K Einträge), da funktioniert die Formel aus Performancegründen nicht. Dank Deinem Kommentar hier habe ich die EINDEUTIG-Funktion in Office 365 gefunden, die kann erstaunlich gut auch mit diesen Datenmengen rechnen.

    Viele Grüße
    Ingo

    • Martin Weiß Autor des Beitrags

      Hallo Ingo,

      die Matrixformel ist für die älteren Excel-Versionen notwendig. Wenn du das Ganze unter Excel 2010, 2013 usw. ohne Matrix eingibst, wird es nicht funktionieren. Unter Office 365 hingegen geht es tatsächlich auch ohne Matrixformel. Und ja, für große Datenmengen gibt es hier definitiv bessere Lösungen.

      Schöne Grüße,
      Martin

  • Denis

    Hallo, vielen Dank für dein Tip. Wie kann ich diese Formel über mehrere Spalten oder sogar Tabellen anwenden? Ich hab es probiert in dem ich =INDEX($J$3:$K$22;VERGLEICH(0;ZÄHLENWENN(L$2:$L2;$J$3:$K$22);0)) angepasst habe, aber da erhalte ich keine Werte.
    Vielen Dank für deine Hilfe und Gruß
    Denis

    • Martin Weiß Autor des Beitrags

      Hi Denis,

      dafür habe ich leider auch keine Lösung parat. Vielleicht hat ja ein anderer Leser eine Idee.

      Schöne Grüße,
      Martin

      • Arne

        Hallo Martin,
        klasse Seite, habe in den letzten Wochen ganz viel Info gefunden, wie ich mein bisher halbherzig behandeltes Excel besser ausreizen kann. Vielen Dank dafür.
        Aber eines bekomme ich immernoch nicht hin: mit o.g. Lösungsformel oder alternativ mit AGGREGAT bekomme ich aus einer intelligenten Tabelle (Wertpapierorderbuch) eine neue Liste mit allen eindeutigen Werten jeweils 1x. Aber wie kann ich ein zusätzliches Kriterium einbauen, damit in dieser Liste nicht alle bisher gekauften und verkauften Papiere drinstehen, sondern nur die, die noch einen Bestand >0 haben. Ich raufe mir die Haare. Hast du einen Tip für mich?
        Die intel.Tab. ist aufgebaut:
        Name / Datum / Anzahl / Anzahl kummuliert / Zähler
        Wertpapier A / 1.1.20 / 1 / 1 / 1
        Wertpapier B / 1.1.20 / 10 / 10 / 1
        Wertpapier A / 3.1.20 / -1 / 0 / 2
        Wertpapier C / 4.1.20 / 20 / 20 / 1
        Wertpapier C / 5.1.20 / 5 / 25 / 2
        Wertpapier C / 6.1.20 / -25 / 0 / 3

        Die neue, eindeutige Liste soll jetzt nur Wertpapier B anzeigen…
        Schönen Rest 4.Advent
        Gruß
        Arne

        • Martin Weiß Autor des Beitrags

          Hallo Arne,

          freut mich sehr, wenn du hier fündig geworden bist. Was das beschriebene Problem angeht:
          Tatsächlich würde ich keine Formellösung wählen, sondern einfach eine Pivot-Tabelle erstellen:

          • Name in den Zeilenbereich
          • Anzahl in den Wertebereich
          • Dann im Namensfeld einen Wertefilter setzen auf Werte > 0

          Geht viel einfacher als komplizierte Formeln 😉

          Schöne Grüße,
          Martin

          • Arne

            Hi Martin,
            das stimmt schon, mit Pivot habe ich auch schon eine Übersicht realisiert, der wesentliche Nachteil ist aber, dass Pivot immer neu aktualisiert werden muss. Und außerdem gibt es da irgendwo einen Rechenfehler, ganz ominös. Bei einem Wertpapier werden Restwerte angezeigt, im Nachkomma-/Rundungsbereich, die in der Einzelauflistung über Formel nicht erscheinen…
            Also leider für mich nicht brauchbar 🙁
            Viele Grüsse
            Arne

  • Klaus Wlotzka

    Hallo Martin,
    ich möchte mich an dieser Stelle für Deine ausgezeichnete Webseite bedanken, ich habe schon einige Tipps verwenden können .Ich hatte dieses Problem der eindeutigen Liste, ohne deinen Artikel zu kennen, über die RANG-, INDEX-Funktion- und SUMMEWENN -Funktion in mehreren Teilschritten gelöst. Die RANG-Funktion brauchte ich, weil die fertige Liste am Ende auch noch eine bestimmte Reihenfolge berücksichtigen musste.

    Nun meine Frage: Bei deinem Artikel „Liste nur mit eindeutigen Werten erstellen“ funktioniert bei mir die Formel aus der Kombination der Funktionen „INDEX“, „VERGLEICH“ und „ZÄHLENWENN“ auch ohne Matrixformel. Ich verwende die 64-bit.Version von Excel 365. Wie ist das möglich?

    • Martin Weiß Autor des Beitrags

      Hallo Klaus,

      vielen Dank für das Feedback, freut mich, wenn du hier hin und wieder fündig wirst.
      Um deine Frage zum Artikel beantworten: Excel aus Microsoft 365 ist mittlerweile so „intelligent“, dass es mit Matrixformeln auch ohne den Affengriff Strg+Umschalt+Enter umgehen kann. Wie dir vermutlich bekannt ist, wurden hier einige spezielle dynamische Array-Funktionen eingeführt (FILTER, SORTIEREN etc.) und dabei hat man offensichtlich auch gleich den Umgang mit den bisherigen Matrixformeln an der einen oder anderen Stelle angepasst. Daher kann man in Excel 365 in diesem Fall explizit auf die spezielle Tastenkombination verzichten.

      Schöne Grüße,
      Martin

  • Roland Jörg

    Hallöchen Martin,
    Ich bin ein ziemlicher Excel-D.A.U. (P.P.S…. Stimmt, ich sitze vor dem Computer 😉 )

    Aber mit dieser (und vielen anderen) Hilfe(n) auf dieser Seite (diesen Seiten) konnte ich schon eine Vielfalt von
    Fragen klären und Probleme bei den unzähligen Excel-Möglichkeiten und deren Anwendungen lösen.
    Was diese Seite(n) besonders einmalig macht ist, dass man hier nicht nur `ne Formel um die Ohren geschlagen
    bekommt, sondern diese auch mit dem „Warum“ und „Wie“ (und zusätzlich noch mit Bildern) erklärt werden.
    ABSOLUTE SPITZENKLASSE

    VIELEN DANK für Ihre Arbeit !!!!!!

    Gruß
    Roland

    • Martin Weiß Autor des Beitrags

      Hallo Roland,

      vielen Dank für so viel Lob, da wird man ja gleich rot 😊

      Schöne Grüße und weiterhin viel Spaß hier!
      Martin

  • Uwe

    Hallo Martin,
    wirklich prima erklärt …excellente Darstellung …
    könntest du diese „aussortierte“ bzw. „selektierte“ Namensliste in Spalte D bzw. F auch in einer DropDown-Liste wiedergeben ?
    Danke für Tipps im Namen aller Leser vorab.

    mit besten Grüßen
    Uwe

    • Martin Weiß Autor des Beitrags

      Hallo Uwe,

      da gibt es verschiedene Möglichkeiten. Man könnte beispielsweise einen Namen definieren, der anstelle eines festen Bezugs über die BEREICH.VERSCHIEBEN-Funktion auf die gefilterte Liste verweist.
      Bezogen auf das Beispiel oben:
      =BEREICH.VERSCHIEBEN(F4;1;0;ZÄHLENWENN(F5:F24;“> „))

      Schöne Grüße,
      Martin

  • Kafen

    Hi,

    vielen Dank für die super Erklärung!

    Ich habe allerdings ein Problem: Und zwar möchte ich aus allen Bestellungen eine eindeutige Kundenliste extrahieren, um damit weiterarbeiten zu können (z.B. Umsatz pro Kunde). Der Listenbereich muss also dynamisch oder ziemlich groß sein, damit ich immer weitere Bestellungen hinzufügen kann. Wenn ich die Liste aber mit einer Länge von z.B. 1000 Zeilen definiere, dauert es sehr lange (über 10 Sekunden), bis die Berechnungen durchgeführt werden und ich weitere Bestellungen hinzufügen kann – obwohl ich einen Leistungsstarken PC habe.

    Gibt es einen besseren Weg um mein Ziel zu erreichen?

    Vielen Dank und Liebe Grüße
    Kafen

    • Martin Weiß Autor des Beitrags

      Hallo Kafen,

      ja, es gibt definitiv einen besseren Weg, und der heißt Pivot-Tabelle. Vor allem, wenn du beispielsweise den Umsatz pro Kunde oder ähnliches sehen möchtest, ist Pivot wirklich ideal. Pivot ist völlig zu Unrecht als kompliziert verschrien. Falls du bisher noch nicht damit gearbeitet hast, es gibt schon ein paar Artikel dazu. Einfach mal ganz oben über das Suchfeld suchen.

      Schöne Grüße,
      Martin

  • Holger Schmidt

    hallo Martin
    habe mir gerade deine Lösung sowie die verlinkte mit der AGGREGAT-Funktion angesehen.
    Ich finde, bei einer Version mit Hilfsspalte ist es deutlich einfacher wenn man dir Formel in der Hilfspalte ein kein wenig erweitert:

    Ich markiere schon in der Hilfsspalte jeden relevanten Datensatz mit einer EINDEUTIGEN Zahl.
    Nach dieser Zahl wird das Ergebnis später sortiert ausgegeben. Wenn es keine abweichend Sortierung geben soll, verwende ich halt Zeile(). Einzige Bedingung für den Wert in der Hilfsspalte ist, das es keine Duplikate gibt.
    Sinngemäß: =wenn(zählenwenn(….)=1;zeile();““)

    Bei dieser Version kann ich jede relevante Position mit Vergleich und dem Suchkriterium aus Kkleinste() abfragen.
    zeile()*-1 in der Hilfsspalte kehrt die Sortierung um.
    Ich kann aber auch ganz simpel eine neue Sortierung erzeugen in dem ich den Wert in der Hilfsspalte anpasse.
    Vielleicht eine sortierte Fußball Tabelle :
    Punkte*1000+Tore-Gegentore+zeile()/100000
    oder nach alter sortiert mit dem Geburtsdatum
    Geburtsdatum+zeile()/100000
    oder nach Vor- und Nachnamen sortieren geht alles, einfach den Wert dementsprechend anpassen
    immer mit dabei [ZEILE()/10000 um Duplikate zu vermeiden.

    Gruß Holger

    • Martin Weiß Autor des Beitrags

      Hallo Holger,

      vielen Dank für deine Variante. Und man sieht wieder einmal: Viele Wege führen nach Rom 🙂

      Schöne Grüße,
      Martin

  • Silvia (Excel-Anfängerin)

    Hallo Martin,
    vielen Dank für deine hilfreichen Erklärungen und Tipps, die mich schon vielfach weitergebracht haben.

    Danke für Deine Hilfe!

    Grüsse
    Silvia

  • Timo

    Hallo Martin,

    danke für den super Tipp, das hat mir sehr geholfen!

    Beim Erweitern deiner Formel bin ich jedoch leider an meine Grenzen gestoßen und würde mich freuen, wenn du mir helfen kannst.
    Ich würde die Formel gerne um eine zusätzliche Spalte erweitern, sodass sowohl bei der Ausgangsliste als auch bei der endgültigen Liste zwei Spalten verwendet werden können.
    Da ich hier leider keinen Screenshot einfügen kann, beschreibe ich das Problem mal:
    Ich habe die zwei Spalten A und B. In Spalte A befinden sich z. B. Projektnamen und in Spalte B die Jobbezeichnung der am Projekt beteiligten Personen:
    Spalte A / Spalte B
    Projekttitel 1 / Projektleiter
    Projekttitel 1 / Teilprojektleiter Untertage
    Projekttitel 1 / Teilprojektleiter Obertagetechnik
    Projekttitel 1 / Vorarbeiter
    Projekttitel 1 / Vorarbeiter
    Projekttitel 1 / Fachkraft
    Projekttitel 1 / Fachkraft
    Projekttitel 1 / Fachkraft
    Projekttitel 1 / Fachkraft
    Projekttitel 2 / Projektleiter
    Projekttitel 2 / Vorarbeiter
    Projekttitel 2 / Fachkraft
    Projekttitel 2 / Fachkraft

    Innerhalb beider Spalten wiederholen sich also durchaus einige Bezeichnungen. In der angestrebten endgültigen Liste möchte ich aber nicht die einzelnen Dopplungen vermeiden, sondern nur die Dopplung der Kombination aus Spalte A und B vermeiden, sodass die Liste wie folgt aussehen würde:
    Projekttitel 1 / Projektleiter
    Projekttitel 1 / Teilprojektleiter Untertage
    Projekttitel 1 / Teilprojektleiter Obertagetechnik
    Projekttitel 1 / Vorarbeiter
    Projekttitel 1 / Fachkraft
    Projekttitel 2 / Projektleiter
    Projekttitel 2 / Vorarbeiter
    Projekttitel 2 / Fachkraft

    Ich hoffe ich habe mein Problem verständlich erklärt.
    Ist die von mir angestrebte Lösung überhaupt mit Excel zu realisieren?
    Ich habe mir daran bislang leider die Zähne ausgebissen und würde mich sehr über Hilfe freuen.

    Freundliche Grüße
    Timo

    • Martin Weiß Autor des Beitrags

      Hallo Timo,

      mir fallen spontan zwei Möglichkeiten ein:
      Entweder du fasst die beiden Spalten in einer zusätzlichen Hilfsspalte zusammen und führst dann deine Berechnungen auf diese Hilfsspalte aus.
      Oder du verwendest einfach eine Pivot-Tabelle und kannst damit ganz auf Formeln verzichten.

      Schöne Grüße,
      Martin

  • Sebastian

    Hallo Martin,

    Danke für die Formel, hat mir sehr geholfen. Allerdings gibt es bei mir noch ein Problem, in meiner „Namensliste“ können leere Felder ohne Information vorkommen. Dies hat zur Folge, dass es dann bei der „Eindeutigen Liste“ nach der Ersten Spalte eine 0 gibt und erst eine Spalte darunter wieder weitergeht.

    Also Zum Beispiel: A = Namensliste / B = Eindeutige Liste

    A4 = Burkhalter / B4 = Burkhalter
    A5 = *leer* / B5 = 0
    A6 = Hunziker / B6 = Hunziker
    A7 = Hunziker / B7 = Isolag
    A8 = *leer* / B8 = *leer*
    A9 = Isolag / B9 = *leer*

    Gibt es einen Weg, diese „0“ Spalte zu eliminieren?

    Herzlichen Dank im Voraus,
    Sebastian

    • Martin Weiß Autor des Beitrags

      Hallo Sebastian,

      die Frage hatte weiter oben schon mal ein anderer Leser gestellt, leider hatte ich damals keine Lösung. Jetzt hätte ich folgende Formel anzubieten (Zellreferenzen bezogen auf mein Beispiel aus dem Artikel):

      {=WENNFEHLER(INDEX($B$5:$B$24;VERGLEICH(1;ZÄHLENWENN($F$4:F4;$B$5:$B$24)+($B$5:$B$24<>„“)*1;0));““)}

      Wichtig: Auch diese Formel muss als Array-Formel eingegeben werden (Strg+Umschalt+Eingabe).

      Mit Microsoft 365/Office 365 gibt es auch eine elegantere Lösung, und das auch noch ohne Array-Eingabe:
      =EINDEUTIG(FILTER(B5:B24;B5:B24<>„“))

      Schöne Grüße,
      Martin

  • Dieter

    Hallo Martin,
    danke für deine sehr hilfreichen Beiträge.
    Ich habe versucht deine Formel für Office 365 auf eine 2-spaltige Liste anzuwenden.
    Mit Unique klappt das zuverlässig.
    =UNIQUE(B5:C24)
    Aber leider scheitere ich an der Filterformel:
    =FILTER(B5:C24;B5:C24″“)
    Ich halte dann den #Value! Fehler.

    Hast du dafür eine Lösung?
    Gruß
    Dieter

    • Martin Weiß Autor des Beitrags

      Hallo Dieter,

      falls du eine Tabelle mit mehreren Spalten filtern möchtest, dann darf der zweite Parameter (also die Filterspalte) sich nur auf eine Spalte beziehen:

      =FILTER(B5:C24;B5:B24<>““)

      Und da du offensichtlich eine englische Version verwendest, werden die Parameter nicht mit einem Semikolon getrennt, sondern mit einem Komma.

      Schöne Grüße,
      Martin