Liste nur mit eindeutigen Werten erstellen 64

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

 

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

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

64 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