Doppelte Daten oder Unikate in umfangreichen Listen zu finden, zu markieren oder gezielt zu bearbeiten ist eine alltägliche Herausforderung in Excel. Dies wird insbesondere dann interessant, wenn die Listen viele Hundert oder sogar Tausend Einträge umfassen und nach mehreren Kriterien gesucht bzw. selektiert werden soll.
In diesem zweiteiligen Tutorial zeigen wir ihnen zahlreiche verschiedene Möglichkeiten, wie Sie in Excel schnell und einfach doppelte Einträge finden und markieren können, Listen auf Übereinstimmung prüfen oder die Eingabe doppelter Daten verhindern können.
Hintergrund & Aufgabenstellung
Welcher Excel-Anwender kennt die folgenden Aufgabenstellungen nicht?
Sie müssen eine umfangreiche Liste mit Rechnungs-, Kunden- oder Artikelnummern zusammenführen oder importieren und dann weiterverarbeiten, Sie bekommen eine Vielzahl von Adressdaten und sollen ein Mailing vorbereiten oder Sie werden gebeten sicherzustellen, dass zwei Listen identische Inhalte haben, leider sind diese aber unterschiedlich sortiert.
In allen Fällen geht es darum, mit möglichst wenig Aufwand zu ermitteln, ob Duplikate bzw. nur Unikate in den Listen enthalten sind. Bei umfangreichen Listen (d.h. bereits bei mehr als 20 Einträgen) ist eine manuelle Prüfung aus Zeit- und Fehlergründen nicht möglich bzw. sinnvoll.
Für die häufigsten Anwendungsfälle wollen wir ihnen im Folgenden verschiedene Lösungsansätze aufzeigen. Dieser zweite Teil des Tutorials beschreibt Lösungen für die Aufgabenstellungen:
- Universelle Formel zur Auflistung von Duplikaten aus einer beliebig langen Liste
- Zwei Listen mit unterschiedlicher Sortierung auf gleiche Inhalte prüfen
- Doppelte Werte (Duplikate) bereits bei der Eingabe verhindern
Den ersten Teil dieser Reihe finden Sie hier.
Auflistung von Duplikaten aus einer beliebig langen Liste
Ziel: Entwicklung einer universellen Formel
Für viele Auflistungen, nicht nur im geschäftlichen Umfeld, ist es wichtig, dass diese Listen nur Unikate, also keine Mehrfacheinträge, enthalten.
Prinzipiell gibt es in Excel mehrere verschiedene Möglichkeiten, doppelte Einträge aus Listen zu entfernen. Kennengelernt haben wir bereits die direkte Prüfung mit ZÄHLENWENN bzw. ZÄHLENWENNS in einer Hilfsspalte (siehe Teil 1 des Tutorials).
Allerdings sind diese Vorgehensweisen auch mit Nachteilen bzw. Einschränkungen verbunden. Sofern die doppelten Einträge aus den Listen einfach entfernt werden, stehen diese nicht mehr für weitergehende Prüfungen zur Verfügung. Dies kann, muss aber nicht unbedingt ein Problem sein.
Mit ZÄHLENWENN bleiben die Einträge zwar erhalten, es ist jedoch erforderlich die Funktion in einer Hilfsspalte unterzubringen und dann diese Hilfsspalte zu sortieren oder zu filtern um das Ergebnis zu sehen. Ein weiterer Nachteil, der für beide Methoden gilt, ist, dass bei regelmäßiger Erweiterung der Liste (z.B. weil jede Woche oder jeden Monat zusätzliche Daten angeliefert werden) entweder die Bereinigung erneut durchgeführt werden muss, oder die ZÄHLENWENN-Funktion erweitert und erneut sortiert bzw. gefiltert werden muss.
Im folgenden Beispiel zeigen wir ihnen, dass dies auch eleganter geht, und zwar mit universellen Formeln die sich dynamisch an eine sich ändernde Datenmenge anpassen. Ziel soll dabei sein, alle in einer Liste in Spalte A mehrfach vorkommenden Einträge der Reihe nach einzeln aufzulisten.
Um die Vorgehens- bzw. Funktionsweise zu erläutern, betrachten wir zunächst eine übersichtliche Liste mit nur 10 Einträgen (siehe Screenshot). Sie sehen sofort, dass der Eintrag „Berlin“ doppelt vorkommt (Zeilen 11 und 16).
Zu Beginn weisen wir der zu prüfenden Liste den Namen „Liste“ zu und dynamisieren diesen mittels der Funktion BEREICH.VERSCHIEBEN. Es wird dem Namen im Namensmanager bspw. folgende Formel zugewiesen:
=BEREICH.VERSCHIEBEN($A$8;1;0;ANZAHL2($A$9:$A$5000);1)
Damit umfasst die Liste in unserem Beispiel den Bereich A9:A18, kann aber beliebig (hier bis maximal in Zeile 5.000 => erweiterbar) verlängert werden.
Die finale Formel ist relativ umfangreich und wird im Folgenden schrittweise von innen nach außen entwickelt.
Schritt 1:
Wir starten mit der Formel: =VERGLEICH(Liste;Liste;0)
Diese wird als Matrixformel mit „Shift+Strg+Return“ abgeschlossen und liefert bei Auflösung über F9 das folgende Ergebnis: {1;2;3;4;5;6;7;3;9;10}
Es wird deutlich, dass an Position 8 die Stelle des ersten Auftretens des doppelten Zellinhaltes „Berlin“ angegeben wird, nicht die tatsächliche Stelle des Wertes (wie bei allen anderen). D.h. bei Unikaten stimmen Stelle und Position überein, bei Duplikaten nicht, dort steht die Position des ersten Auftretens (hier Position 3 der Liste mit insgesamt 10 Positionen). Die Nr. 8 fehlt folglich vollständig in diesem Zwischenergebnis.
Schritt 2:
Wenn wir also nach den Zahlen 1 bis 10 in diesem Zwischenergebnis suchen, erhalten wir für die 8 den Fehlerwert #NV, da nicht vorhanden. Dazu können wir die Formel ZEILE($1:$10) nutzen. Zusammen ergibt dann die Matrixfunktion:
={VERGLEICH(ZEILE($1:$10);VERGLEICH(Liste;Liste;0);0)}
(Eingabe wieder mit „Shift+Strg+Return“ abschließen) über F9 das Ergebnis:
{1;2;3;4;5;6;7;#NV;9;10}
Schritt 3:
Da uns ausschließlich die Mehrfacheinträge interessieren ersetzen wir nun sämtliche Zahlen durch Leereinträge („“) bzw. die Fehlerwerte #NV durch deren Position. Dies lässt sich über eine WENN-Funktion in Kombination mit ISTNV erreichen, ebenfalls wieder als Matrixfunktion:
={WENN(ISTNV(VERGLEICH(ZEILE($1:$10);VERGLEICH(Liste;Liste;0);0));ZEILE($1:$10);““)}
Durch Markieren der Formel lässt sich über F9 dann dieses Ergebnis abrufen:
{„“;““;““;““;““;““;““;8;““;““}
Schritt 4:
Da wir am Ende die Einträge (der Duplikate) untereinander auflisten wollen, helfen uns Zahlen innerhalb der Matrix nicht weiter. Mit Hilfe der KKLEINSTE-Funktion kann aber aus einer Liste der Kleinste, Zweitkleinste, Drittkleinste usw. Wert extrahiert werden. Der Rang des Elementes der Matrix wird über das zweite Argument k der Funktion bestimmt, für den kleinsten Wert wäre k = 1 etc.
Synthax: KKLEINSTE(Matrix;k)
Wir können nun den Rang jeweils als absolute Zahl direkt in die Formel schreiben, eleganter ist es aber, den Rang in einer separaten Spalte einzutragen, dann kann die Formel anschließend bequem nach unten kopiert werden und wir erhalten auf diese Weise eine Liste mit dem ersten doppelten Eintrag, dann der Zweite, dann der Dritte etc.
Schreiben wir also bspw. die Zahlen 1, 2 und 3 in die Zellen E9, E10 und E11 dann lautet die Matrixformel für die erste ermittelte Duplette:
{=KKLEINSTE(WENN(ISTNV(VERGLEICH(Nr;VERGLEICH(Liste;Liste;0);0));Nr;““);E9)}
Schritt 5:
Dies liefert uns den Wert 8 den wir nun im abschließenden Schritt mit der INDEX-Funktion übersetzen in den tatsächlichen Zellinhalt an der 8. Position, in diesem Beispiel „Berlin“:
={WENNFEHLER(INDEX(Liste;KKLEINSTE(WENN(ISTNV(VERGLEICH(Nr;VERGLEICH(Liste;Liste;0);0));Nr;““);E9));“-„)}
Im Fall von Fehler wird über die WENNFEHLER-Funktion hier ein Stich („-„) ausgegeben. Alternativ können Sie aber auch vorgeben, dass die Zelle dann leer bleiben soll („“).
Nun können Sie die Matrixfunktion beliebig nach unten kopieren und erhalten so alle Mehrfacheinträge der Reihe nach aufgelistet.
Schritt 6:
Damit die Liste auch mehr als 10 Einträge (wie in unserem einfachen Beispiel) haben kann, dynamisieren wir noch den Ausdruck ZEILE($1:$10).
Wir vergeben bspw. den Namen „Nr“ und tragen im Namensmanager die folgende Formel ein:
=ZEILE(INDIREKT(„1:“&ANZAHL2(Liste)))
Die 1 hinter Indirekt steht dabei für die Spalte A.
Die finale, universell verwendbare Formel
Somit lautet unsere finale, universelle Formel, um dynamisch Mehrfacheinträge in jeder beliebigen Liste herauszusuchen:
{=WENNFEHLER(INDEX(Liste;KKLEINSTE(WENN(ISTNV(VERGLEICH(Nr;VERGLEICH(Liste;Liste;0);0));Nr;““);E9));“-„)}
Der Screenshot oben zeigt das Ergebnis für ein leicht angepasstes Beispiel mit zwei Duplikaten (Berlin und Bonn) in den grün hinterlegten Zellen. Selbstverständlich funktioniert das Ganze auch mit Zahlenwerten oder gemischten Listeneinträgen wie bspw. Rechnungsnummern der Form „RE 0815-465“ usw. Auch hier wird allerdings nicht zwischen Groß- und Kleinschreibung unterschieden, sondern Berlin, BERLIN und BeRLin würden bspw. als Duplikate erkannt.
2 Listen mit unterschiedlicher Sortierung auf gleiche Inhalte prüfen
Auch in diesem Beispiel adressieren wir wieder eine häufig im Alltag eines Excel-Anwenders vorkommende Fragestellung, insbesondere im Finanz- und Controllingbereich. Stellen Sie sich vor, Sie bekommen von verschiedenen Personen oder aus verschiedenen IT-Systemen zu unterschiedlichen Zeitpunkten Listen (gleich welcher Art) und müssen sicherstellen, dass sich in beiden Listen – auch bei unterschiedlicher Sortierung – definitiv die gleichen Inhalte befinden.
Diese Fragestellung lässt sich ebenfalls für beliebig lange Listen mit nur einer einzigen Formel klären.
Zunächst sollten Sie den beiden Listen Namen zuweisen, bspw. „Liste_A“ und „Liste_B“. Anschließend geben Sie in einer Zelle (im Screenshot Zelle E9) die folgende Formel ein, die als Matrixformel unbedingt mit „Shift+Strg+Return“ abgeschlossen werden muss.
{=WENN(ISTNV(SUMME(VERGLEICH(Liste_A;Liste_B;0))-SUMME(VERGLEICH(Liste_B;Liste_A;0)));“Listeninhalte nicht identisch“;“Listeninhalte identisch“)}
Die Texte der WENN-Funktion (jeweils in Anführungszeichen) sind natürlich beliebig austauschbar und können sofern gewollt auch durch Zahlenwerte etc. ersetzt werden.
Dynamisierung für beliebige Listen
Handelt es sich um „wachsende“ Listen oder soll eine universelle Vorlage für unterschiedlich lange Listen erstellt werden, empfiehlt sich auch hier wieder die Dynamisierung der beiden definierten Namen „Liste_A“ und „Liste_B“.
Für Liste_A in der Spalte A kann dann im Namensmanager die folgende Formel genutzt werden:
=BEREICH.VERSCHIEBEN($A$8;1;0;ANZAHL2($A$9:$A$5000);1)
Damit werden bspw. alle Listen bis zur Zeile 5.000 (Maximalwert anpassbar) abgedeckt.
Analog lautet die Formel für die Liste_B in Spalte C dann:
=BEREICH.VERSCHIEBEN($C$8;1;0;ANZAHL2($C$9:$C$5000);1)
Sofern ihre Listen in einer anderen Zeilennummer beginnen oder in einer anderen Spalte stehen, müssen die Formeln natürlich entsprechend angepasst werden.
Duplikate bereits bei der Eingabe verhindern
Nachdem in den vorangegangenen Beispielen verschiedene Ansätze erläutert wurden, um Duplikate finden, markieren und bearbeiten zu können, beschäftigt sich das abschließende Beispiel damit, eine Mehrfacheingabe durch den Nutzer bereits im Vorfeld, also bei der Dateneingabe, zu verhindern. In der Praxis gibt es viele Fälle in denen es wichtig ist, dass Daten nur ein einziges Mal in einer Liste vorkommen, denken Sie bspw. an eindeutige Rechnungsnummern, Prüfziffern oder ähnliches.
Eine Umsetzungsmöglichkeit liegt in der kombinierten Verwendung einer ZÄHLENWENN-Funktion mit der Excel-Funktionalität „Datenüberprüfung“. Unterstellen wir für ein einfaches Beispiel, dass der Eingabebereich B8:B14 lauten soll, dann ist folgendermaßen vorzugehen:
Markieren Sie den Bereich B8:B14 und rufen im Register „Daten“ in der Gruppe „Datentools“ den Befehl „Datenüberprüfung“ auf und wählen dort den ersten Eintrag „Datenüberprüfung“. In der erscheinenden Dialogbox (siehe Screenshot) wählen Sie im Feld „Zulassen“ die Option „Benutzerdefiniert“. Im Feld „Formel“ geben Sie die folgende Formel ein:
=(ZÄHLENWENN($B$8:$B$14;B8))<2
Achten Sie bei der Eingabe darauf, dass der Bereich $B$8:$B$14 als absoluter Bezug (also mit $-Zeichen) übergeben wird. Der Bezug auf die Zelle B8 hingegen muss relativ übergeben werden.
Unter dem Reiter „Fehlermeldung“ (siehe Markierung im Screenshot) sollten Sie den Typ „Stopp“ verwenden, damit keine Doppeleingaben möglich sind und unter „Fehlermeldung“ können Sie einen kurzen Hinweis eintragen, damit der Anwender weiß, warum die Fehlermeldung kommt (z.B. „Keine Doppeleingaben zulässig“ oder „Eingabewert bereits vorhanden“).
An dieser Stelle möchten wir Sie nochmal darauf hinweisen, dass wir eine spezielle Tutorial-Trilogie „From Good to Great“ veröffentlicht haben. In dieser wird im ersten Teil ausführlich auf die vielfältigen Möglichkeiten der Funktionalität „Datenüberprüfung“ in Excel eingegangen. Den Beitrag mit vielen illustrativen Beispielen finden sie hier.
Wie üblich haben wir auch zu diesem Tutorial eine entsprechende Excel-Datei vorbereitet, die wir ihnen gerne kostenlos zur Verfügung stellen.
