Duplikate in Excel über mehrere Spalten finden 8

Dubletten über mehrere Spalten mit einer eigenen Formatierungsregel erkennen
 

Das Auffinden von doppelten Einträgen in langen Tabellen gehört zu den häufigen Aufgaben des geplagten Anwenders: Auftragsnummern, Kontonummern, Kundennummern und so weiter, die Liste ließe sich beliebig erweitern.

Zum Glück stellt Excel einige praktische Hilfsmittel zur Verfügung, um solche Dubletten in einzelnen Spalten schnell zu identifizieren. Die Betonung liegt hier auf „einzelnen“ Spalten. Was ist jedoch, wenn mich nur solche doppelten Einträge interessieren, die über mehrere Spalten hinweg identisch sind. Also zum Beispiel Adressen?

Auch dafür gibt es eine Lösung.

Dem Problem mit doppelt vorkommenden Tabelleneinträgen habe ich mich auch schon in einem anderen Artikel gewidmet, den du gerne nochmal hier nachlesen kannst. Dort werden auch ein paar andere Techniken beschrieben.

Der heutige Beitrag befasst sich speziell mit den Möglichkeiten der bedingten Formatierung.

Dubletten in einzelnen Spalten

Doppelte Auftragsnummern etc. lassen sich am schnellsten über eine bedingte Formatierung erkennen. Sollte dir dieses extrem einfache Hilfsmittel bisher noch nicht bekannt gewesen sein, hier nochmal die Vorgehensweise am Beispiel von IP-Adressen. Nehmen wir an, ich bin Systemadministrator und habe eine Liste von IP-Adressen:

Liste mit IP-Adressen

Liste mit IP-Adressen

Nun möchte ich ohne großen Aufwand die doppelten Werte farblich hervorheben. Genau dafür gibt es eine vordefinierte Regel in den bedingten Formatierungen: Erst die Liste markieren (1), danach das Menü „Start | Bedingte Formatierung | Regeln zum Hervorheben von Zellen | Doppelte Werte…“ öffnen (2).

Vordefinierte Formatierungsregel für doppelte Werte

Die vorgeschlagenen Werte im nächsten Fenster kann ich unverändert übernehmen, da hier bereits die Option „Doppelte Werte“ voreingestellt ist:

Duplikate werden farbig markiert

Duplikate werden farbig markiert


Nachdem ich das Fenster mit OK geschlossen habe, werden mir sofort alle Duplikate farbig angezeigt. Voraussetzung für diese Art der bedingten Formatierung ist, dass immer nur die jeweilige einzelne Zelle für den Abgleich herangezogen werden soll.

Dubletten mit mehreren Spalten

In einem anderen Szenario funktioniert diese vordefinierte Regel nicht, nämlich dann, wenn mehrere Zellen berücksichtigt werden müssen. Also beispielsweise Adressen:

Beispiel: Adressenlste

Beispiel: Adressenlste

Eine Dublette ist hier nur dann gegeben, wenn die Werte über alle Spalten hinweg identisch sind. Bevor wir hier eine entsprechende bedingte Formatierung einrichten können, sollten wir uns überlegen, wie dieses Problem generell zu lösen ist.

Im Klartext formuliert lautet die Regel in etwa so:
Du bist eine Dublette, wenn die jeweilige Kombination aus Vorname UND Nachname UND Straße UND Postleitzahl UND Ort mehr als einmal in der Liste vorkommt. Wir zählen also und arbeiten dabei mit mehreren Bedingungen.

Zu diesem Zweck gibt es in Excel die Funktion ZÄHLENWENNS:
=ZÄHLENWENNS(Bereich 1; Kriterium 1; Bereich 2; Kriterium 2; Bereich 3; Kriterium 3; ....)

Die Bereiche sind dabei die verschiedenen Spalten, das Kriterium ist der Eintrag in der jeweiligen aktuellen Zeile. Bezogen auf meine Beispielliste sieht das also so aus:
=ZÄHLENWENNS($A$2:$A$30;$A2;$B$2:$B$30;$B2;$C$2:$C$30;$C2;$D$2:$D$30;$D2;$E$2:$E$30;$E2)
Sieht wie ein ziemliches Monster aus, ist aber eigentlich leicht zu durchschauen:

Die ZÄHLENWENNS-Funktion

Die ZÄHLENWENNS-Funktion

Ganz wichtig:
Da wir die Formel anschließend einfach nach unten kopieren wollen, müssen die Bereiche zwingend mit absoluten Bezügen angegeben werden. Also $A$2:$A$30, $B$2:$B$30 und so weiter.
Die Kriterien hingegen dürfen nur mit gemischten Bezügen angegeben werden. Die Spalte ist mit dem Dollarzeichen fixiert, die Zeilennummer jedoch nicht. Also $A2, $B2 und so weiter. Ansonsten stimmen die Bezüge nicht mehr, wenn die Formel kopiert wird.

Als Ergebnis seht die 1, wenn diese Adresse in der ganzen Liste nur ein einziges Mal vorkommt. Ansonsten zeigt die Zahl eben an, ob sie zweimal, dreimal oder noch öfter auftaucht:

Wie oft kommt die Adresse vor?

Wie oft kommt die Adresse vor?


(Die farbigen Markierungen habe ich noch manuell gesetzt)

Für unsere Dublettenprüfung interessiert uns jetzt weniger die exakte Zahl 1, 2 usw., es reicht eigentlich aus zu wissen, ob das Ergebnis größer als 1 ist. Daher hängen wir an die Formel noch eine kleine Prüfung an und erhalten als Ergebnis nur noch WAHR oder FALSCH:

Erweiterung der Formel

Erweiterung der Formel

Und mit dieser Formel haben wir alles, was wir für eine eigene bedingte Formatierungsregel benötigen.

Wir markieren also zunächst wieder die Adressenliste ohne die Überschriften (1), in meinem Beispiel den Bereich A2:E30 und legen dann eine neue Formatierungsregel an (2):

Eigene Formatierungsregel anlegen

Eigene Formatierungsregel anlegen

Im nächsten Fenster markieren wir oben den Eintrag „Formel zur Ermittlung der zu formatierenden Zellen verwenden“ (3) und kopieren dann die fertige ZÄHLENWENN-Funktion in das Formelfeld (4):
=ZÄHLENWENNS($A$2:$A$30;$A2;$B$2:$B$30;$B2;$C$2:$C$30;$C2;$D$2:$D$30;$D2;$E$2:$E$30;$E2)>1

Die ZÄHLENWENNS-Funktion kommt zum Einsatz

Die ZÄHLENWENNS-Funktion kommt zum Einsatz

Wichtig ist nur, dass die Zeilennummern in der Formel auch mit der ersten markierten Zeile übereinstimmen. Und wie man im Hintergrund schon sieht, werden jetzt wirklich nur die vollständigen Duplikate hervorgehoben.

Wie man an diesem Beispiel sieht, macht es durchaus Sinn, sich bei eigenen Formatierungsregeln die benötigten Formeln erst einmal in der Tabelle zusammenzubasteln. Dann sieht man auch schnell, ob sie grundsätzlich funktioniert oder nicht.

Falls du beim Nachbauen trotzdem Probleme hattest, kannst du dir meine Beispieldatei hier herunterladen.

 

Das könnte dich auch interessieren:

No related posts found

Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Schreibe einen Kommentar

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

8 Gedanken zu “Duplikate in Excel über mehrere Spalten finden

  • Uwe Kuhlenberg

    Hallo Martin,

    ich habe ein Problem, bei der diese Funktion die Grundlage bilden, jedoch vermutlich noch ergänzt werden müsste (Excel 2010):

    Die Spalten A und B enthalten Werte, die auf Dubletten hin untersucht werden sollen. Die Spalte C enthält jeweils einen bestimmten Betrag (€). Dieser Wert soll am Ende der Spalte (Summe C) dann nur einmal bei evtl. vorkommenden Dubletten (Spalten A und B) in der zu bildenden Summe berücksichtigt werden. Wie müsste die Summenformel lauten?

  • Uwe Kuhlenberg

    Ergänzung: Gemeint sind Dubletten in Spalte A und B im o. g. Sinne zusammengenommen! Nur die mehrfachen Beträge nur einmal zu zählen würde nicht zum Ziel führen, da diese auch bei anderen Einträgen in den Spalten A+B identisch sein könnten, dort jedoch wiederum bei der Summenbildung berücksichtigt werden müssten.

    • Martin Weiß Autor des Beitrags

      Hallo Uwe,

      ich bin mir nicht sicher, ob ich es richtig verstanden habe:
      Wenn also ein Dublette im Sinne von identischen Einträgen in den Spalte A + B existiert, dann soll der dazugehörige Wert in Spalte C aber nur einmal in der Summe berücksichtigt werden?
      Aber welcher der Werte in C ist dann relevant, wenn diese Dubletten unterschiedliche Werte in Spalte C enthalten?

      Oder habe ich dich doch falsch verstanden?

      Schöne Grüße,
      Martin

      • Uwe Kuhlenberg

        Ja, richtig verstanden, die Beträge in Spalte P (nicht C, war nur beispielhaft) sind in diesem Fall dann auch doppelt und dürfen nur einmal in die Summe P einfließen. Käme es nur auf doppelt bzw. mehrfach vorhandene Beträge in Spalte P an, wären diese sicherlich auch auf andere Weise (einfacher) herauszufiltern, es könnte theoretisch! aber vorkommen, dass sich bei mehrfach vorhandenen Einträgen in Spalte P die Werte in Spalte A + B zusammengenommen unterscheiden und diese daher bei der Summe P wiederum nur einmal berücksichtigt werden dürften. Ich habe inzwischen eine Lösung mit einer Hilfsspalte AL (die Tabelle ist in Wirklichkeit größer) gefunden, in der die Werte aus den Spalten A + B verkettet werden, die Matrixformel lautet: {=SUMME(WENN(ZÄHLENWENN(INDIREKT(„AL3:AL“&ZEILE(3:29));AL3:AL29)=1;P3:P29))}. Ich hatte lange versucht, die VERKETTEN-Funktion in diese Summenformel zu integrieren, also ohne Hilfsspalte auszukommen, was aber leider nicht funktioniert hat. Falls du noch eine einfachere Variante weißt (auch VBA), wäre ich dir dankbar. Da diese Tabelle je nach Fall eine unterschiedliche Anzahl von Zeilen haben kann, würde mich interessieren, ob es eine Möglichkeit gibt, statt der „29“ einen Wert für die „letzte Zeile“, die bei der Summenbildung zu berücksichtigen ist, einzugeben, so dass man die Formel nicht ständig anpassen muss. Herzlichen Dank!

  • Christopher Methler

    Vielen Dank für Ihre Tipps rund um Excel. Für viele Benutzer, sowie auch meine Teilnehmer in meinen Microsoft Office Schulungen, sind diese Websites eine große Hilfe zur Selbsthilfe und ich empfehle dies in meinen Seminaren für den Arbeitsalltag.

    Viele Grüße aus Bochum
    Christopher Methler

    • Martin Weiß Autor des Beitrags

      Hallo Herr Methler,

      das freut mich sehr zur hören. Vielen Dank für das tolle Feedback und die Empfehlungen!

      Schöne Grüße,
      Martin