Bestimmt kennst du das: Es gibt eine Liste mit Daten von euren Kunden, die zum Beispiel für Serienbriefe verwendet wird. Immer, wenn neue Kunden akquiriert werden, werden diese der Liste hinzugefügt.
Wenn man nun einen Serienbrief an diese Kunden schreiben möchte, stellt man fest, dass manche Kunden doppelt vorkommen.
Ein solches oder ähnliches Szenario habe ich in mehreren Firmen erlebt und führt immer zu Chaos. Wie geht man mit so etwas um? Wie bereinige ich doppelte Werte in Excel, damit es nicht zu doppelten Briefen kommt?
Ich zeige dir im Folgenden, wie du Duplikate in Listen findest und in Excel doppelte Werte löschen kannst.
Duplikate anzeigen lassen und doppelte Werte löschen in Excel
Damit wir in Excel doppelte Werte löschen können, müssen wir sie zunächst finden. Dafür gibt es mehrere Methoden, die ich dir in diesem Beitrag nach und nach vorstelle: So kann man sich in Excel Duplikate anzeigen lassen und diese löschen bzw. entfernen.
Immer dann, wenn Listen oder Tabellen manuell gepflegt werden, kann es dabei zu doppelten Einträgen kommen. Der Klassiker ist dabei die Adressliste für Weihnachtskarten, Erinnerungsschreiben oder Ähnliches. Besonders anfällig für solche Fehler sind Listen, die von mehreren Personen gepflegt werden: Man erinnert sich – Mensch, den soundso von gestern muss ich ja noch eintragen – und macht das Mal eben. Weil es schnell gehen muss, prüft man auch nicht, ob der Eintrag vielleicht bereits vorhanden ist. Und Schwupps, ist er doppelt vorhanden – weil die liebe Kollegin das gestern bereits erledigt hat. Was gut gemeint war, führt nun zu einem Problem.
Ein weiteres Problem sind Tippfehler oder unterschiedliche Schreibweisen: Manchmal wird die komplette Adresse mit allen Komponenten ausgeschrieben (Hauptstraße), manchmal wird abgekürzt (Hauptstr.) – und schon sind solche Einträge von Excel nicht mehr so einfach als Duplikate zu erkennen. Natürlich kann es auch zu ganz normalen Tippfehlern kommen – jemand hat die falsche Taste auf der Tastatur erwischt, Buchstaben versehentlich verdreht oder zwei Tasten gleichzeitig gedrückt. Auch solche Einträge führen in der Folge zu Schwierigkeiten.
Egal, welchen Befehl man verwendet, vergleicht Excel die Einträge ganz genau: Ist ein Unterschied vorhanden – egal wie klein er ist-, findet die Software, dass es sich um unterschiedliche Einträge handelt. Es geht eben ganz genau, wie beispielsweise auch beim Eingeben eines Passworts: Bei der kleinsten Abweichung wird das Passwort nicht akzeptiert.
Sehen wir uns zunächst an, wie man sich Duplikate in Excel grundsätzlich anzeigen lassen kann. Weiter unten im Beitrag zeige ich dir dann, wie du vorgehen kannst, um auch Duplikate mit Tippfehlern oder unterschiedlicher Schreibweise zu finden. Das ist leider relativ umständlich und meistens nicht in einem einzigen Arbeitsschritt zu erledigen. Daher wäre es besser, gleich beim Eintragen von Daten genau zu prüfen, ob dieser Eintrag vielleicht bereits vorhanden ist und dabei auch auf Tippfehler zu achten.
Da es aber ziemlich utopisch ist, davon auszugehen, dass solche Fehler nie passieren, zeige ich dir nun mehrere Wege, wie du im Nachhinein Listen bereinigen kannst.
Schritt für Schritt: Duplikate anzeigen
Der erste Schritt – bevor du in Excel doppelte Werte löschen kannst – ist zunächst das Finden der mehrfachen Einträge. Ich zeige dir nun anhand eines Beispiels, wie du dir von Excel Duplikate grundsätzlich anzeigen lassen kannst.
Hier eine beispielhafte Kundenliste, die sowohl mehrfache als auch „falsche“ Einträge enthält:
Im Laufe des Jahres wurden die Kunden einfach untereinander aufgelistet. Nun möchte ich wissen, ob in der Excel Liste Duplikate vorhanden sind. Am einfachsten geht das so:
Markiere den Bereich, in dem Excel nach doppelten Werten suchen soll. Die Anrede würde ich hier zum Beispiel weglassen, da es in dieser Spalte natürlich Duplikate gibt (Herrn und Frau kommen sicherlich häufiger vor). Interessant sind vor allem die Spalten für Vorname, Nachname und Straße.
Gehe im zweiten Schritt nun in der Registerkarte Start auf „Bedingte Formatierung“, „Regeln zum Hervorheben von Zellen“ und dann auf „Doppelte Werte“:
Gib an, wie die doppelten Inhalte von Excel formatiert werden sollen, zum Beispiel rot.
Alle Duplikate werden nun rot angezeigt. Beachte aber, dass ein doppelter Vorname noch nicht bedeutet, dass es sich um die gleiche Person handelt!
Für eine bessere Übersicht macht es Sinn, die Liste alphabetisch zu sortieren, das Ergebnis könnte dann so aussehen:
Wenn nun alle drei Spalten rot markiert sind, handelt es sich sehr wahrscheinlich um die gleiche Person. Nun kannst du ganz einfach einen der doppelten Inhalte entfernen: Markiere die entsprechende Zeile, die du löschen möchtest, mache einen Rechtsklick und klicke auf „Zellen Löschen“.
Vielleicht fällt dir aber auch schon ein Problem auf: Wenn etwas nicht exakt gleich geschrieben ist (Marktstr. / Markt Str.) werden diese Zellen von Excel nicht als Duplikate erkannt und somit auch nicht rot markiert. Auch bei Tippfehlern (zum Beispiel Hocke / ‚Hocke) wird kein Duplikat erkannt.
Ein bisschen besser geworden ist unsere Liste also, aber perfekt ist sie noch nicht. Außerdem kann das manuelle Löschen der Daten ziemlich aufwändig sein, je nachdem, wie gut oder schlecht die Liste gepflegt wurde.
Wenn du noch mehr Excel Tipps und Tricks lernen möchtest: Schau dir mein Excel Buch an: Excel Grundlagen für Einsteiger
Sehen wir uns noch eine zweite Möglichkeit an, wie du vorgehen kannst.
Doppelte Werte löschen in Excel mit einem Klick
Bevor wir uns anschauen, wie wir die restlichen Duplikate anzeigen lassen und diese doppelten Werte löschen können, hier noch ein Tipp zum Entfernen von Duplikaten. Es gibt einen entsprechenden Befehl in Excel, den man durch einfaches Anklicken auslösen kann:
- Klicke in deine Excel Liste.
- Gehe in die Registerkarte Daten und klicke auf „Duplikate entfernen“:
- Gib an, in welchen Spalten nach Duplikaten gesucht werden soll, also zum Beispiel wieder Vorname, Nachname und Straße.
- Die doppelten Werte, die Excel erkennt, werden automatisch entfernt.
Hier gibt es nun aber ein kleines Problem: Ich habe keine Möglichkeit mehr, die Daten, die gelöscht wurden, zu überprüfen! Kopiere die Excel Liste also lieber vorher in ein neues Arbeitsblatt, damit du hinterher überprüfen kannst, ob Daten verloren gegangen sind.
Außerdem werden auch bei dieser Methode Tippfehler oder unterschiedliche Schreibweisen nicht als doppelte Einträge erkannt.
Im Folgenden noch eine weitere Möglichkeit. Diese klingt zwar zunächst ein bisschen kompliziert, ist aber eine sehr gute Option. Und wenn man sie einmal verstanden hat, ist es nicht so kompliziert, wie es zunächst scheint.
Der Matchcode: Verwendung einer Formel
Zum Glück gibt es den sogenannten Matchcode. Mit dieser Formel können wir uns weitere Duplikate anzeigen lassen und diese löschen.
So gehst du dabei vor:
- Füge eine neue Spalte vor deiner Liste ein und nenne diese „Matchcode“:
- Der Matchcode besteht aus diesen Funktionen: „links“ und „rechts“. Diese werden verbunden mit &. Der Sinn des Ganzen ist es, eine Buchstabenkombination aus zum Beispiel Vorname, Nachname und Straße zu erstellen und dann diese Kombinationen zu vergleichen.
- Erstelle also eine solche Formel in der ersten leeren Zelle deiner Liste.
Zum Beispiel: =LINKS(C2;3)&LINKS(D2;3)&LINKS(E2;4)
Das bedeutet:
= es folgt eine Formel
LINKS – gib mir eine Zeichenfolge aus von links aus gezählt
(C2;3) – in der Zelle C2, drei Buchstaben
& – und
Oder auf Deutsch: Gib mir die ersten drei Buchstaben in Zelle C2 aus und die ersten drei aus Zelle D2 etc.
Hier findest du eine Zusammenfassung über die wichtigsten Excel Formeln und Funktionen.
Es kann hier eine beliebige Kombination aus links (= vom Wortanfang an) und rechts (= ausgeben der Zeichen von rechts ausgehend, also vom Wortende) erstellt werden.
Diese Formel kannst du nun nach unten kopieren:
Auf diese Weise kann man Tippfehler ausblenden – es werden also mehr Duplikate angezeigt, weil Excel nun zum Beispiel Gün mit Gün vergleicht, und nicht Günter mit Günther.
Mit der bedingten Formatierung kann man sich von Excel nun in Spalte A wieder doppelte Werte anzeigen lassen. Oder man kann in Excel auch gleich alle doppelten Werte löschen mit „Duplikate entfernen“, siehe oben.
Allerdings ist es auch hier notwendig, noch einmal genau zu schauen. Unser Herr Hocke wurde immer noch nicht als doppelter Wert erkannt, weswegen es wichtig ist, weitere Formel-Kombinationen zu erstellen und diese abwechselnd Mal von rechts und Mal von links zählen zu lassen.
Ein Matchcode mit „rechts“ könnte zum Beispiel so aussehen:
=RECHTS(C2;4)&RECHTS(D2;3)&RECHTS(E2;4)
Nun wird auch Herr Hocke erkannt:
Nach jedem neuen Matchcode solltest du die Formel in die anderen Zellen deiner Matchcode-Spalte nach unten kopieren und dann die doppelten Werte löschen, bis die Liste bereinigt ist.
Am Schluss kannst du die Spalte mit dem Matchcode natürlich wieder löschen.
Passende Artikel für dich:
Schritt für Schritt Anleitung für Serienbriefe in Word.
Sehr nützlich: Zahlen formatieren in Excel und eigene Einheiten erstellen
Alles zum Erstellen von Excel Diagrammen.
Wie du siehst, gibt es verschiedene Möglichkeiten, in Excel doppelte Einträge zu finden und diese zu entfernen. Allerdings bringen alle das ein oder andere Problem mit sich: Unterschiedliche Schreibweisen (Straße / Str.) oder auch bei vorhandenen Tippfehlern werden Einträge nicht als bereits vorhanden erkannt. Excel ist da pingelig.
Wer großes Vertrauen hat, kann über den Befehl Duplikate entfernen in der Registerkarte Daten automatisch alles entfernen lassen, was von Excel als solches erkannt wird. Allerdings findet die Löschung ohne vorherige Überprüfung statt. Was da gelöscht wurde, weiß man hinterher also nicht. Und Tippfehler bzw. unterschiedliche Schreibweisen werden auch so nicht erkannt.
Es ist daher immer notwendig, auch manuell durch die Daten zu schauen. Hierfür kann man entweder die bedingte Formatierung nutzen (die allerdings auch keine Schreibfehler erkennt) oder mit dem Matchcode die Einträge überprüfen.
Am besten ist eine Kombination aus allen drei Varianten: Als erstes ein automatisches Entfernen der doppelten Einträge. Dann eine Überprüfung durch die Anwendung des Matchcodes in Verbindung mit der bedingten Formatierung, damit man die doppelten Werte einfacher findet. Der Matchcode sollte dabei mehrmals auf unterschiedliche Weise erstellt und angewendet werden. Nach jeder Anwendung muss erneut auf Duplikate geprüft werden.
Wie du siehst, ist das eine Menge Arbeit. Daher wäre es am allerbesten, vor jedem Eintrag zu prüfen, ob die Daten vielleicht bereits vorhanden sind – und auf diese Art gar keine Duplikate zu erstellen.
Egal, wie du vorgehst: Erstelle dir vor dem Löschen immer eine Sicherheitskopie, damit du eventuell fälschlich gelöschte Daten wiederherstellen kannst!
