Dubletten erkennen, bevor du irgendetwas löschst
Excel vergleicht Zeichen und keine Bedeutung, deshalb entscheidet die Definition des Schlüssels darüber, ob ein Lauf saubere Daten hinterlässt oder Datensätze verschluckt.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Excel vergleicht Zeichen, nicht Bedeutung
Zwei Systeme liefern dieselbe Kundenliste, und niemand kann sagen, wie viele Adressen doppelt sind. Der Reflex ist Daten > Duplikate entfernen. Der Befehl arbeitet sofort, meldet eine Zahl und lässt sich nur über Strg und Z zurücknehmen. Nach dem Speichern ist er endgültig.
Vorher muss geklärt sein, was in diesen Daten überhaupt derselbe Datensatz ist. Selten ist die ganze Zeile doppelt, meist ist es die Kombination aus wenigen Feldern, während Telefonnummer und Anrede abweichen. Hakst du nur den Nachnamen an, löscht du zwei verschiedene Personen zusammen, die zufällig beide Meier heißen.
Umgekehrt findet ein Lauf gar nichts, wenn ein Leerzeichen am Ende steht, ein geschütztes Leerzeichen aus einem Export mitkommt oder ein Datum in der einen Zeile als Text und in der anderen als echter Datumswert vorliegt. Beide Fälle sehen nach einem Ergebnis aus, und beide sind falsch.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Symptom, Ursache, Lösung
Symptom
Duplikate entfernen meldet null entfernte Zeilen, obwohl zwei Zeilen identisch aussehen.
Ursache
Verglichen wird der gespeicherte Wert. Ein Leerzeichen am Ende, ein geschütztes Leerzeichen mit dem Code 160 oder ein Datum als Text gegen einen echten Datumswert reichen für einen Unterschied.
Lösung
Zwei verdächtige Zellen mit =A2=A3 und mit =LÄNGE(A2) vergleichen und die Schlüsselspalten vorher über GLÄTTEN und WECHSELN bereinigen.
Symptom
Die bedingte Formatierung färbt Werte, die in ihrer Spalte nur einmal vorkommen.
Ursache
Die Regel Doppelte Werte arbeitet über die gesamte Markierung. Bei zwei markierten Spalten gilt schon ein Wert als doppelt, der in jeder Spalte genau einmal steht.
Lösung
Nur eine Spalte markieren, oder für einen zusammengesetzten Schlüssel eine Hilfsspalte anlegen und die Regel ausschließlich darauf anwenden.
Symptom
Nach dem Entfernen fehlen Datensätze, die gar keine Dubletten waren.
Ursache
Im Dialog waren zu wenige Spalten angehakt, deshalb galten zwei verschiedene Personen mit demselben Nachnamen als derselbe Eintrag.
Lösung
Den Lauf auf einer Kopie des Blatts wiederholen, alle Schlüsselspalten anhaken und die Treffer vorher über eine Zählformel ansehen.
Symptom
Die Artikelnummern 007 und 7 werden als dieselbe Nummer gezählt.
Ursache
ZÄHLENWENN behandelt eine Zeichenkette, die wie eine Zahl aussieht, wie eine Zahl. Sternchen und Fragezeichen liest es zusätzlich als Platzhalter.
Lösung
=SUMMENPRODUKT(--IDENTISCH($A$2:$A$5000;$A2))>1 verwenden, weil IDENTISCH zeichengenau vergleicht und keine Platzhalter kennt.
Symptom
Power Query lässt Meier und MEIER beide stehen, Excel selbst nicht.
Ursache
Power Query vergleicht Text zeichengenau, während Excel beim Vergleich die Groß- und Kleinschreibung ignoriert.
Lösung
In der Abfrage unter Transformieren, Format die Schritte Kleinbuchstaben und Kürzen auf die Schlüsselspalten setzen, bevor die Duplikate entfernt werden.
Fünf Schritte von der Ahnung zur bereinigten Liste
- 01 Leg fest, welche Spalten zusammen einen Datensatz eindeutig machen.
- 02 Bereinige diese Spalten von Leerzeichen und unsichtbaren Zeichen.
- 03 Bau daraus eine Hilfsspalte mit einem eindeutigen Trennzeichen.
- 04 Zähl die Vorkommen und sieh dir die Treffer im Zusammenhang an.
- 05 Entferne erst danach, und zwar auf einer Kopie des Blatts.
Damit bereinigst du Listen, ohne Datensätze zu verlieren
Dublettenarbeit besteht zum größten Teil aus Vorbereitung. Ist der Schlüssel definiert und sind die Spalten bereinigt, ist das Finden selbst nur noch eine Formel.
Schlüssel fachlich festlegen
Du entscheidest, welche Spalten zusammen einen Datensatz eindeutig machen, und begründest das aus den Daten, statt diese Entscheidung dem Dialogfeld zu überlassen.
Vor dem Vergleich bereinigen
Mit GLÄTTEN und WECHSELN räumst du Leerzeichen und das geschützte Leerzeichen mit dem Code 160 weg, die sonst identische Werte auseinanderhalten.
Hilfsspalte mit Trennzeichen
Du baust aus den Schlüsselspalten eine einzige Zeichenkette mit einem klaren Trennzeichen, damit aus zwei verschiedenen Kombinationen nicht zufällig dieselbe entsteht.
Zählen statt löschen
Mit einem mitwachsenden Bereich nummerierst du die Vorkommen durch, filterst auf alles über 1 und legst der Fachabteilung die Fälle im Zusammenhang vor.
Fallstricke von ZÄHLENWENN
Du weißt, dass Sternchen und Fragezeichen als Platzhalter gelesen werden und 007 und 7 als gleich gelten, und weichst bei Belegnummern auf SUMMENPRODUKT mit IDENTISCH aus.
Wiederholbar mit Power Query
Für Listen, die jeden Monat neu kommen, hältst du die Bereinigung als Abfrage fest, samt Kleinbuchstaben und Kürzen auf den Schlüsselspalten.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Erst die Definition, dann das Werkzeug
In einer Kundenliste ist selten die ganze Zeile doppelt. Doppelt ist die Kombination aus Nachname, Postleitzahl und Geburtsdatum, während sich Telefonnummer und Anrede unterscheiden. Welche Spalten den Schlüssel bilden, entscheidest du fachlich, nicht Excel. Erst danach ist die Frage nach dem Werkzeug überhaupt sinnvoll.
Excel vergleicht Text ohne Rücksicht auf Groß- und Kleinschreibung. Meier und MEIER gelten sowohl für ZÄHLENWENN als auch für Duplikate entfernen als derselbe Wert. Ein Leerzeichen am Ende macht dagegen sehr wohl einen Unterschied, ebenso ein geschütztes Leerzeichen mit dem Zeichencode 160, das beim Kopieren aus dem Browser oder aus einem ERP-Export mitkommt und in der Zelle unsichtbar bleibt.
Deshalb gehört vor jeden Dublettenlauf eine Bereinigung. =GLÄTTEN(A2) entfernt führende und nachgestellte Leerzeichen und reduziert Mehrfachleerzeichen im Text auf eines. Gegen das geschützte Leerzeichen brauchst du zusätzlich =WECHSELN(A2;ZEICHEN(160);" "). Ob unsichtbare Zeichen im Spiel sind, verrät ein Vergleich von =LÄNGE(A2) mit der Zahl der Zeichen, die du in der Zelle siehst.
Markieren, ohne etwas zu verändern
Der schnellste Blick geht über Start, Bedingte Formatierung, Regeln zum Hervorheben von Zellen, Doppelte Werte. Die Regel arbeitet aber innerhalb der Markierung und Zelle für Zelle. Markierst du zwei Spalten, färbt sie auch Werte, die in Spalte A und Spalte C je genau einmal vorkommen. Für einen Spaltenvergleich ist das nützlich, für die Suche nach doppelten Datensätzen führt es in die Irre.
Für einen zusammengesetzten Schlüssel legst du eine Hilfsspalte an: =GLÄTTEN(A2)&"|"&GLÄTTEN(B2)&"|"&TEXT(C2;"TT.MM.JJJJ"). Das Trennzeichen verhindert, dass aus zwei verschiedenen Kombinationen zufällig dieselbe Zeichenkette entsteht, und TEXT sorgt dafür, dass ein Datum nicht als nackte Seriennummer im Schlüssel landet. Auf diese Hilfsspalte wendest du dann die Regel oder ZÄHLENWENN an.
=ZÄHLENWENN($H$2:$H$5000;$H2)>1 als Regelformel färbt jede Zeile, deren Schlüssel mehr als einmal vorkommt, also auch das erste Vorkommen. Willst du nur die Wiederholungen sehen und den ersten Treffer stehen lassen, nimmst du =ZÄHLENWENN($H$2:$H2;$H2)>1. Der Bereich beginnt hier fest oben und endet in der aktuellen Zeile, er wächst also beim Herunterwandern mit.
Zählen statt löschen
Dieselbe Formel ohne Vergleich, also =ZÄHLENWENN($H$2:$H2;$H2), nummeriert die Vorkommen durch: 1, 2, 3. Danach filterst du auf alles größer 1 und siehst genau die Zeilen, die zur Diskussion stehen, samt ihrer Nachbarspalten. Das ist der richtige Weg, wenn die Fachabteilung die Fälle vor dem Löschen ansehen soll.
ZÄHLENWENN hat zwei Eigenheiten, die genau hier durchschlagen. Es liest die Zeichen * und ? im Suchkriterium als Platzhalter, weshalb Artikelnummern mit Sternchen zu viele Treffer liefern. Und es behandelt eine Zeichenkette, die wie eine Zahl aussieht, wie eine Zahl, sodass 007 und 7 als gleich gelten. Bei Artikel-, Kunden- und Belegnummern ist das eine reale Fehlerquelle, die niemand im Ergebnis sieht.
Wenn Groß- und Kleinschreibung einen Unterschied machen soll, hilft ZÄHLENWENN nicht weiter. Dann brauchst du =SUMMENPRODUKT(--IDENTISCH($A$2:$A$5000;$A2))>1, weil IDENTISCH zeichengenau vergleicht. In Microsoft 365 sowie in Excel 2021 und 2024 liefert außerdem =EINDEUTIG(A2:A100;;WAHR) genau die Werte, die exakt einmal vorkommen, was eine schnelle Gegenprobe ergibt: Was dort fehlt, ist mehrfach vorhanden.
Kontrolliert entfernen
Daten, Datentools, Duplikate entfernen fragt zuerst, welche Spalten den Vergleich bilden. Nur die angehakten Spalten zählen, die übrigen fahren einfach mit. Excel behält jeweils das erste Vorkommen, löscht die späteren und meldet danach die Zahl der entfernten und der verbliebenen Zeilen. Zurück kommst du nur über Strg und Z, deshalb arbeitest du auf einer Kopie des Blatts.
Beim Vergleich zählt der gespeicherte Wert, nicht die Anzeige. 1,00 und 1 sind dasselbe, ein als Text gespeichertes Datum und ein echtes Datum dagegen nicht. Genau daran scheitern Läufe auf frisch importierten Listen, in denen ein Teil der Werte als Text angekommen ist: Der Lauf meldet null Duplikate, obwohl man sie mit bloßem Auge sieht.
Power Query ist der Weg, wenn die Bereinigung wiederholbar sein soll. Über Daten, Aus Tabelle/Bereich lädst du die Liste, markierst die Schlüsselspalten und wählst Zeilen entfernen, Duplikate entfernen. Achtung: Power Query vergleicht Text zeichengenau, anders als Excel selbst, Meier und MEIER bleiben also beide stehen. Setz vorher unter Transformieren, Format die Schritte Kleinbuchstaben und Kürzen auf die Schlüsselspalten, dann verhält sich die Abfrage wie erwartet, und beim nächsten Export läuft alles mit einem Klick auf Aktualisieren erneut ab.
Dazu passende Kurse
Wenn solche Listen bei dir jeden Monat neu aus einem Fremdsystem kommen, lohnt es, Datenbereinigung im Excel-Kurs üben .
Werden dieselben Stammdaten in mehreren Listen parallel gepflegt, entstehen Dubletten immer wieder neu, und die Ursache behebst du eher über Datenmodelle mit Microsoft Access .
Wie sicher bist du beim Thema wirklich?
Lesen fühlt sich schnell nach Können an. Ein kurzer Test zeigt dir, was davon schon sitzt und wo sich ein Kurs lohnt. Kostenlos, ohne Anmeldung, mit einer Erklärung zu jeder Antwort.
Der passende Lernpfad
Wenn du nicht nur ein Thema abhaken, sondern eine Rolle ausfüllen willst, zeigt dir der Lernpfad die Kurse in der Reihenfolge, in der sie aufeinander aufbauen.
Excel Allrounder
Excel halbwegs zu können, das reicht heute nicht mehr. Entweder du beherrschst es – oder du verlierst täglich Zeit, Überblick und jede Menge Effizienz.
Dieser Lernpfad macht dich zur verlässlichen Excel-Anlaufstelle im Team: schneller arbeiten, sauber analysieren, überzeugend präsentieren. Warte nicht, bis andere den Job besser machen, nur weil sie Excel professionell nutzen. Steig jetzt ein – und mach aus Excel dein echtes Allround-Werkzeug.
Der Einstieg
Wo du genau das übst
Sehr kompetenter Dozent mit einem intensiven Training welches anwendungsorientiert ist.
Das doch recht "langweilige" Thema Excel, wurde einem sehr gut präsentiert. Durch direktes mitarbeiten wurde es einem nie langweilig.
Sehr gut vorbereitetes Seminar mit hilfreichen Informationen und anschaulicher Vermittlung. Vielen Dank!
Häufige Fragen
Warum findet Duplikate entfernen nichts, obwohl die Zeilen gleich aussehen?
Wie bekomme ich eine Liste der eindeutigen Werte, ohne das Original anzufassen?
Zählt Duplikate entfernen Groß- und Kleinschreibung mit?
Deine Ansprechpartner
Du bist dir nicht sicher, welcher Kurs oder welches Level zu dir passt? Wir beraten dich persönlich und kostenlos.
Yves Hoppe
Weiterbildung & Beratung
Hilft dir, aus dem Excel-Programm den passenden Kurs für deinen Stand zu finden.
Norbert Jansen
Beratung & Inhouse
Plant mit dir Inhouse-Trainings, die auf eure Abläufe und euren Datenbestand zugeschnitten sind.
Saubere Stammdaten, ohne dass etwas verschwindet
Wie du einen Schlüssel definierst und die Bereinigung so festhältst, dass sie beim nächsten Export von allein läuft, zeigen die Excel-Kurse von cmt an Listen, wie sie im Alltag ankommen.