(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
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
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
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
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)
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
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
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
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
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
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.
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.
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
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
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
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
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
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
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
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!
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.
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
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
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
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
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!
Hallo Helge,
vielen Dank für den Hinweis, der Fehler im Text ist jetzt korrigiert.
Schöne Grüße,
Martin
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?
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.
Freut mich zu hören 🙂
Schöne Grüße,
Martin
Hallo,
Der Bericht hat mir sehr geholfen!
Vielen Dank!
Markus
Freut mich, gern geschehen!
Schöne Grüße,
Martin
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
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
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
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
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
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.
Hallo Harald,
zu deiner Frage zum INDEX-Fehler habe ich auch geantwortet:
https://www.tabellenexperte.de/excel-dream-team-index-verweis/
Schöne Grüße,
Martin
Ich werde den Verdacht nicht los, dass dieser Trick nur funktioniert, wenn nicht zwei gleiche Namen untereinander stehen.
Jetzt bin ich meinen Verdacht losgeworden. Meine Güte, was für eine verrückte Formel… Herzlichen Dank dafür!
Hi,
freut mich, wenn es doch noch geklappt hat (im Zweifelsfall einfach die Beispieltabelle herunterladen).
Schöne Grüße,
Martin
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
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
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
Hallo Siggi,
freut mich, wenn die Lösung geholfen hat.
Auch dir ein gutes und gesundes neues Jahr!
Martin
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
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
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
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
Hallo Oliver,
eine ähnliche Frage zu zwei Kriterien habe ich weiter oben schon beantwortet (Kommentar von Christian).
Schöne Grüße,
Martin
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
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
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
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
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.
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
In Excel365 gibt es nun auch die Funktion EINDEUTIG, welche genau das gleiche Ergebnis liefert – nur etwas schlanker.
https://support.office.com/de-de/article/eindeutig-funktion-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e
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
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!
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
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
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
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
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
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
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
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??
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
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.
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
Das probier ich gleich aus.
Vielen lieben Dank
Grüssle Gabi
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
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
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
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
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
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
Verstehe. Danke für Deine schnelle Antwort!
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
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
Unbrauchbare Formel solange die Iterative Berechnung in Excel nicht aktiviert ist.
Vielleicht könnte man das noch erwähnen?
Hallo Hans,
da muss ich widersprechen: Die Formel benötigt keine iterative Berechnung.
Schöne Grüße,
Martin
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
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:
Geht viel einfacher als komplizierte Formeln 😉
Schöne Grüße,
Martin
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
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?
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
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
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
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
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
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
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
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
Hallo Holger,
vielen Dank für deine Variante. Und man sieht wieder einmal: Viele Wege führen nach Rom 🙂
Schöne Grüße,
Martin
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
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
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
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
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
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
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
Hallo Martin
erstmal vielen Dank für die Lösungen, die du hier zeigst. Ich arbeite mit Office 365 und konnte somit die Formel mit „=EINDEUTIG“ verwenden. Nun konnte ich mir auf diese Weise eine Liste mit den gewünschten Einträgen einer Spalte erstellen. Ich suche nun noch an einer Lösung für folgendes Problem:
Ausgangslage: Ich habe eine Produktetabelle mit ca. 38’000 Produkten (Zeilen). In dieser Tabelle habe ich 4 Spalten welche die Produktekategorien (Hauptkategorie , Unterkategorie1, Unterkategorie2, Unterkategorie3) beinhalten.
Ziel:
Ich möchte eine Tabelle erstellen, die alle 4 Spalten beinhaltet, die aber in der Spalte Unterkategorie3 nur die Zeilen beinhaltet die eindeutig sind. Da ich in der Spalte Unterkategorie3 ca. 300 Eindeutige Einträge habe wäre dann meine Kategorientabelle auch nur 300 Zeilen lang und hätte natürlich in den Spalten Hauptkategorie, Unterkategorie1, Unterkategorie2, jeweils Einträge die mehrfach vorkommen.
Vielen Dank für Deine Hilfe
Gruss Urs
Hallo Urs,
irgendwie stehe ich noch auf der Leitung. Wie soll denn das gewünschte Ergebnis für die anderen Spalten (Hauptkategorie, Unterkategorie1 und Unterkategorie2) aussehen, wenn du von Unterkategorie3 eindeutige Werte haben möchtest?
Ich gehe mal davon aus, das es bei den anderen 3 Spalten deutlich mehr verschiedene Kombinationen geben wird, als die genannten 300 in Unterkategorie3. Ich habe mal ein kleines Beispiel erstellt:
Welche Zeilen davon sollen also angezeigt werden, wenn von Unterkategorie3 nur ein einziges B übrigbleibt?
Oder habe ich irgendetwas falsch verstanden?
Schöne Grüße,
Martin
Hallo Martin,
vorab ein herzliches Dankeschön für die vielen tollen Artikel, die mir schon oft weitergeholfen haben!
Eine kleine Verständnisfrage zur Funktionsweise Deiner Lösungs-Formel habe ich allerdings:
Der ZÄHLENWENN-Teil Deiner Formel gibt bei mir (Excel 365) auch explizit als Array-Formel nur dann die erforderliche „1“ bei eindeutigen Einträgen aus, wenn – wie in Deiner Beispieldatei – vor dem Suchkriterien-Bezugsbereich das „@“-Zeichen steht. In Deinem Beitrag steht dazu nichts und mich würde brennend interessieren, was das „@“ bewirkt und warum es bei mir nur damit funktioniert.
Besten Gruß und danke schon mal.
Werner
Moin Werner,
das @-Zeichen Verweist in dem Beispiel auf die aktuelle Zeile in der die Formel steht.
Steht die Formel z.B. in D12 dann würde @$B$5:$B$24 auf die Zelle B12 verweisen.
Alternativ könntest du auch
=ZÄHLENWENN($B$4:B14;INDIREKT(„B“ & ZEILE()))+(B15″“)*1
schreiben.
Gruß Marcel
Danke, Marcel, für die schnelle und erhellende Antwort. Wieder was gelernt. 🙂
Hallo Martin,
das war genau das, was ich gesucht habe. Die Lösung für Excel365 funktioniert super. Leider kommt dann einer unsortierte Liste raus. Meine Versuche die alphabetisch zu sortieren sind leider alle gescheitert. Hast Du noch einen Tipp, wie das Ergebnis sortiert ausgegeben werden kann? Vielen Dank schonmal!
Beste Grüße,
Alf
Ich glaube ich habs gerade selber rausgefunden 😉 Einfach die Formel nochmal mit =SORTIEREN(*) einrahmen.
Hallo Martin,
folgende Aufgabenstellung: Ich habe eine Liste mit Einnahmen, Vornamen, Nachname, Adressen und noch vielen anderen Spalten.
Es soll davon automatisch eine Liste erstellt werden die nach eindeutigen Bezügen Vorname und Nachname gefiltert wird aber auch alle anderen Informationen der Zeile enthält.
Habe mit Office 465 mit EINDEUTIG versucht und die Zellen Vorname und Nachname verbunden – aber dann gibt er mir diese in einer Zelle zurück – das ist so leider nicht brauchbar.
wäre für Hilfe sehr dankbar – hatte bis jetzt leider nicht viel mit Excel zu tun und muss das jetzt relativ rasch hinbekommen.
Ja ja, das Problem sitzt eben immer vor dem PC ;-))
Aber tolle Seite die ich sicher bei mehr Zeit durcharbeiten werde.
Vielleicht gibt es ja aber eine schnelle und einfache Lösung vielen Dank und liebe Grüße aus Wien
Hallo Isabelle,
erst mal Danke für dein schönes Feedback zu meiner Website.
Und ich sehe, du hast schon das super-neue, noch streng geheime Office 465 im Einsatz 😉
Aber Spaß beiseite. Wenn ich es richtig interpretiert habe, dann soll zwar jede Adresse nur einmal vorkommen, aber in diesem Datensatz dann beispielsweise die Summe aller Einnahmen zu dieser Adresse, richtig?
In diesem Fall hilft die EINDEUTIG-Funktion nicht so richtig weiter, denn damit wird nichts summiert. Ich würde daher auf eine Pivot-Tabelle zurückgreifen. Wenn du noch nicht mit Pivot gearbeitet hast, keine Angst, das ist wirklich einfach. Hier findest du eine kleine Einführung:
Der beste Freund des Excel-Analysten: Pivot-Tabellen und
Der Horror geht weiter: Pivot-Tabellen Teil 2
Du würdest in deinem Fall alle Namens- und Adressenfelder in den Zeilenbereich schieben und die Einnahmen in den Wertebereich. Und damit sparst du dir auch alle möglichen komplizierten Formeln.
Schöne Grüße
Martin
(Darf ich an dieser Stelle noch einmal ausdrücklich Werbung für Martins Buch „Excel Pivot-Tabellen für Dummies“ machen? Und, nein, ich bekomme kein Geld dafür, und auch sonst nichts.
Ich arbeite nur sehr gerne mit diesem Buch, gerade in den letzten Tagen wieder. Gründlich, gut lesbar, äußerst hilfreich.)
Lieber Michael,
vielen Dank für das Lob und die Empfehlung meines Buchs 🙂
Schöne Grüße,
Martin
Hallo
in ihren Erläuterungen stimmen ein paar Dinge nicht: Im screenshot zu Schritt 3 zeigen Sie bei ZÄHLENWENN einen anderen Bezug, als in der dann nachfolgenden Formel:
WENNFEHLER(INDEX($B$5:$B$24;VERGLEICH(1;ZÄHLENWENN($F$4:F4;$B$5:$B$24)+($B$5:$B$24„“)*1;0));““)
Also einmal von D:F, dann wieder nur F:F.
Jedenfalls plage ich mich anhand Ihrer Angaben nun schon etliche Minuten.
Hallo Christina,
da haben Sie recht, die Formel im Screenshot sollte sich eigentlich nur auf eine Spalte beziehen.
Statt D$4:$F4 müsste es heißen: D$4:$D4
Tut mir leid, wenn ich damit Verwirrung gestiftet habe.
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für diesen Beitrag. Hat mir schon sehr geholfen. Allerdings habe ich jetzt einen Anwendungsfall, wo die Formel so nicht funktioniert. Und zwar besteht meine Liste mit Dubletten aus Zahlen, die von -15 bis +150 gehen können. Zahlen und Minus-Werte sind kein Problem. Leider können aber auch Nullen drinnen vorkommen. Und das geht nicht. Dabei wird am Ende der Liste (z.B. +150 immer noch eine „0“ drangehängt. Hast du dafür vielleicht auch eine Lösung?
Leider wird die Datei auf Rechnern eingesetzt werden, die *noch* kein Microsoft 365 haben. „Eindeutig“ und „Filter“ geht also nicht.
Vielen Dank für deine Hilfe.
Hallo Bernd,
hast du dir die Beispieldatei heruntergeladen? Dort gibt es ein Blatt „Leere_Zellen“, in dem dieses Problem meines Erachtens gelöst sein sollte.
Schöne Grüße,
Martin
Hallo Martin,
wenn die Datei das Problem lösen soll, verstehe ich nicht, wie. Die Ausgangstabelle hat leere Zellen (was funktioniert), aber auch Nullen (0°C). Diese Nullen dürfen nicht rausgefiltert werden, sie werden benötigt. Die gefilterte Liste sieht dann z.B. so aus:
-15
-10
-5
0
5
10
15
0
Die letzte 0 muss weg.
Danke für Deine Hilfe.
Gruß,
Bernd
Hallo Bernd,
du hast leider recht, die Lösung in der Datei funktioniert hier leider doch nicht. Es klappt, wenn nur 0-Werte enthalten sind oder nur leere Einträge, aber nicht wenn es beides gibt. Dafür habe ich jetzt auch noch keine passende Lösung gefunden.
Schöne Grüße,
Martin
Hey Martin,
ich möchte nun doch fragen… weil ich es mit den Screenshots und dem Text nicht reproduzieren kann.
Im Einsatz ist Excel365, lokal installiert + geöffnet. Deine Lösung wollte ich für einen automatischen (Freelancer)-Stundenzettel nutzen. Je nachdem wie viele verschiedene Kunden im Monat auftauchen, brauche ich diese eindeutigen Werte für die Rechnungsheader. Sonst würde ich VBA nutzen, aber ich wollte deine Lösung lernen (daher möchte ich auch nicht die Beispieldatei nutzen).
Spalte D sind die Kunden (bei dir B), D4 ist Head, wie bei dir B4.
Hilfsspalte J soll nun ZählenWenn enthalten und beginnt in Zeile 5, gemeinsam mit den Datenzeilen. Matrixformeln mit Strg+Alt+Enter sind bekannt.
D5 enthält „Kunde1“ und J5 hat {=ZÄHLENWENN($D$4:D4;$D$5:$D$35)} ergibt 0
D6 enthält „Kunde2“ und J6 hat {=ZÄHLENWENN($D$4:D5;$D$5:$D$35)} ergibt 1
D7 enthält „Kunde1“ und J7 hat {=ZÄHLENWENN($D$4:D6;$D$5:$D$35)} ergibt 1
Mal 35 Zeilen zählt er mir tatsächlich nur pro Zeile auf, wie oft Kunde1 bisher kam. Bei einem seltenen Kunden in Zeile 27, wo er 0 zeigen müssten, zeigt er ebenfalls 7. Kannst du mir bitte bei meinem Fehler helfen? Ich sehe es auch nach 7 Mal vergleichen nicht 😀
Hallo Kevin,
du hast Excel 365, warum plagst du dich mit unnötigen Array-Funktionen herum? Ich empfehle dir stattdessen, die EINDEUTIG-Funktion zu nutzen, so wie es oben auch im Artikel beschrieben ist.
Schöne Grüße,
Martin
Vielen Dank für diese Funktion mit Mehrwert! Wie würde die Funktion mit einer Bedingung aussehen? Wenn ein bestimmter Wert gefunden wurde sollen einige Werte nicht angezeigt werden…
Hoffe ich konnte es einigermaßen klar formulieren 🙂
Danke vorab
S. C.
Hallo Selcuk,
das hört sich für mich also nach einem Filter an. Wenn du Microsoft 365 im Einsatz hast, wäre vielleicht die FILTER-Funktion hilfreich? Aber so ganz klar ist die Anforderung für mich leider noch nicht…
Schöne Grüße,
Martin
Hallo, vielen Dank für die interessanten Tricks. Ich suche nach Möglichkeiten, nur sichtbare eindeutige Werte zu zählen. Dabei ist es mir nicht wichtig, ob A jetzt 3 Einträge in der Liste hat und B 4 Einträge, sondern das Ergebnis A + B = 2. Gibt es hierfür einen Weg?
Jeden Tag ändert sich die Liste und ich setze einen anderen Tagesfilter. Die Duplikate sollten nicht für den gesamten Datensatz berechnet werden, sondern nur im Tagesbereich und dort nur einmal gezählt werden.
Innerhalb des Tages sollten Kategorien angeklickt werden und sich das Zählergebnis dynamisch ändern.
Hallo Janina,
die zusätzlichen Anforderungen sind mir jetzt nicht ganz klar. Aber wenn es darum geht, nur sichtbare Werte zu zählen und die über einen Filter ausgeblendeten zu ignorieren, dann helfen die beiden Funktionen TEILERGEBNIS oder AGGREGAT.
Schöne Grüße,
Martin
Guten Tag
Ich habe eine Auswahl von 7 Gegenständen, die ich mit. ca. 8 bis 15 verschiedenen Werkzeugen. bearbeite
Bei einigen Gegenständen benötige ich teilweise die gleichen Werkzeuge.
Ich möchte eine Packliste erstellen um zum Beispiel die Gegenständen 1; 3 und 7 zu erstellen.
Im Weiteren sind noch Werkzeuge, die ich immer mitnehmen muss.
Auf dem Blatt Arbeiten habe ich jeden Gegenstand aufgeführt mit den zugehörigen Werkzeugen. (Spalte A= Gegenstand Nummer, Spalte B=Name der Werkzeuge; Spalte C=Grösse und Holzart der Gegenstände.)
Mit Daten Filter kann ich die Gegenstände 1; 3 und 7 Auswählen, aber einige sind jetzt doppelt vorhanden. Wenn ich Daten Duplikate entfernen drücke, ist die Datenbank zerstört. Wie kann ich das Korrigieren?
Hallo Herr Schmocker,
wenn ich es richtig verstanden habe, gibt es für jeden Gegenstand immer so viele Zeilen, wie Werkzeuge benötigt werden. Das heißt, jedes einzelne Werkzeug in Spalte B steht in einer eigenen Zeile, richtig?
Automatisch korrigieren lässt sich das meiner Ansicht nach nicht. Sie könnten aber beispielsweise die Gegenstände filtern und dann die gefilterte Werkzeug-Spalte in einen separaten Tabellenbereich unterhalb der vorhandenen Tabelle kopieren (oder auf ein neues Blatt) und dort über die Funktion „Daten | Duplikate“ entfernen die Liste bereinigen.
Ein bessere Lösung fällt mir dazu spontan leider nicht ein.
Schöne Grüße,
Martin
Hallo,
mein Problem wird damit theoretisch tatsächlich lösbar, aber Excel schafft das leider absolut nicht performant zu bearbeiten. Ich habe eine Werteliste mit ca.8000 Datenzeilen und will dort die Werte dynamisch auf doppelte Einträge prüfen. Die vielen Datenzeilen stammen aus einer Datenbank und müssten für ein Migration überarbeitet und anschließend eingedampft werden. Das ist aber ein Arbeit auf „try and error“ Basis. Die gleiche Liste müsste also mehrfach angefasst und abgeändert werden. Daher wollte ich auch eine Funktion und nicht „Duplikate entfernen“ verwenden. Wenn man sehr viel Zeit mitbringt, kann man das so machen. Aber Spaß macht das nicht.
Viele Grüße,
Göran
Hallo Göran,
für große Datenmengen, die auch noch regelmäßig aktualisiert werden, würde ich Power Query nutzen. Dort gibt es auch eine Funktion zum Entfernen von Duplikaten, die könnte dir hier vielleicht weiterhelfen. Der große Vorteil von Power Query ist, dass man die Bereinigung jederzeit durch einen Mausklick aktualisieren kann, wenn sich der Datenbestand geändert hat.
Schöne Grüße,
Martin