Suchen von Duplikaten mithilfe von bedingten Formatierungen

Mithilfe von bedingten Formatierungen und der Funktion ZÄHLENWENN können Sie Duplikate in einem Datenbereich suchen. Im Folgenden wird genau erläutert, wie Sie dazu vorgehen.

Einrichten der ersten Formel für die bedingte Formatierung

Zunächst lege ich ein bedingtes Format für die erste Datenzelle fest. Danach kopiere ich das bedingte Format in den gesamten Bereich.

In meinem Beispiel enthält Zelle A1 eine Spaltenüberschrift (Rechnung), daher wähle ich Zelle A2 aus und klicke dann im Menü Format auf Bedingte Formatierung. Das Dialogfeld Bedingte Formatierung wird geöffnet. Das erste Feld enthält den Text Zellwert ist. Wenn Sie auf den Pfeil neben diesem Feld klicken, können Sie Formel ist auswählen.

Wenn Sie auf Formel ist klicken, ändert sich das Erscheinungsbild des Dialogfelds. Anstelle der Felder zwischen x und y wird nun ein großes Formelfeld angezeigt. Mit diesem Formelfeld können Sie eine Vielzahl von Aktionen durchführen. Sie können dort alle Formeln eingeben, die Sie sich vorstellen können, solange dabei der Wert WAHR oder FALSCH zurückgegeben wird.

In diesem Fall müssen wir die Formel ZÄHLENWENN verwenden. Geben Sie daher Folgendes im Feld ein:

=ZÄHLENWENN(A:A;A2)>1

Diese Formel bedeutet: Der gesamte Bereich in Spalte A soll betrachtet werden. Anschließend soll gezählt werden, bei wie vielen Zellen in diesem Bereich der Wert dem Wert in Zelle A2 entspricht. Schließlich wird ermittelt, ob diese Anzahl größer als 1 ist.

Wenn keine Duplikate vorhanden sind, ist die Anzahl immer 1. Da sich Zelle A2 im Bereich befindet, sollte der Wert genau einer Zelle in Spalte A dem Wert in Zelle A2 entsprechen.

 Hinweis   In dieser Formel steht A2 für die aktuelle Zelle – d. h. für die Zelle, für die Sie das bedingte Format festlegen. Wenn sich Ihre Daten also in Spalte E befinden und Sie das erste bedingte Format in Zelle E5 einrichten, lautet die Formel =ZÄHLENWENN(E:E;E5)>1.

Auswählen einer Farbe zum Hervorheben von doppelten Einträgen

Nun sollten Sie ein auffälliges (d. h. gut erkennbares) Format für die gefundenen Duplikate auswählen. Klicken Sie hierzu im Dialogfeld Bedingte Formatierung auf die Schaltfläche Format.

Klicken Sie auf die Registerkarte Muster, und klicken Sie auf eine helle Farbe wie Rot oder Gelb. Klicken Sie dann auf OK, um das Dialogfeld Zellen formatieren zu schließen.

Das ausgewählte Format wird in der Vorschau angezeigt. Klicken Sie auf OK, um das Dialogfeld Bedingte Formatierung zu schließen.

Passiert nichts? Huch! Wenn Sie zum ersten Mal eine bedingte Formatierung eingerichtet haben, wäre es natürlich toll, jetzt zu sehen, dass es geklappt hat. Wenn Sie allerdings nicht das Glück haben, dass es sich bei den Daten in Zelle A2 tatsächlich um das Duplikat der Daten in einer anderen Zelle handelt, ist die Bedingung FALSCH, und es wird keine Formatierung angewendet.

Kopieren der bedingten Formatierung in die restlichen Zellen

Sie müssen die bedingte Formatierung nun von Zelle A2 in die anderen Zellen im Bereich kopieren. Lassen Sie den Mauszeiger in Zelle A2, und klicken Sie im Menü Bearbeiten auf Kopieren. Drücken Sie STRG+LEERTASTE, um die ganze Spalte zu markieren. Klicken Sie dann im Menü Bearbeiten auf Inhalte einfügen. Klicken Sie im Dialogfeld Inhalte einfügen auf Formate und dann auf OK.

So wird die bedingte Formatierung in alle Zellen in der Spalte kopiert. Nun werden vermutlich – endlich – ein paar Zellen mit dem formatierten farbigen Hintergrund angezeigt, um ein Duplikat zu kennzeichnen.

Es kann ganz informativ sein, die Bedingungsformel in Zelle A3 zu betrachten (nachdem sie aus A2 kopiert wurde). Markieren Sie Zelle A3, und klicken Sie im Menü Format auf Bedingte Formatierung. Die Formel im Feld Formel ist wurde geändert und gibt nun an, wie oft die Daten aus Zelle A3 in Spalte A vorkommen.

Sie können für bis zu 65.536 Zellen bedingte Formatierungen festlegen und dabei die jeweils aktuelle Zelle mit den restlichen 65.535 Zellen vergleichen. Die Formel im ersten Schritt könnte also auch =ZÄHLENWENN($A$2:$A$1751;A2)>1 lauten.

Beim Kopieren des bedingten Formats in die ganze Spalte können Sie außerdem mit dem Befehl Inhalte einfügen nur die Zellen auswählen, die Daten enthalten.

Hervorheben der zweiten Instanz eines Duplikats

Bei der vorigen Lösung wurde davon ausgegangen, dass Sie beide doppelte Rechnungsnummern hervorheben möchten, um dann manuell auszuwählen, welchen Wert Sie löschen bzw. korrigieren möchten. Wenn die erste Instanz des Duplikats nicht gekennzeichnet werden soll, können Sie die Formel folgendermaßen ändern:

=ZÄHLENWENN($A$2:$A2;A2)>1

 Hinweis    Sie müssen darauf achten, die Dollarzeichen wie hier dargestellt einzugeben.

Im ersten Argument dieser Formel wird nur der zweite Zellverweis für den Datenbereich geändert, wenn er in den Rest der Spalte kopiert wird. Das bedeutet, dass mit dieser Formel beim Suchen von doppelten Einträgen nur die Zellen von der aktuellen Zelle bis zur ersten Zelle im Datenbereich verglichen werden.

Sortieren der Daten

Sie können Spalten nicht auf Grundlage eines bedingten Formats sortieren. Um die Daten so zu sortieren, dass sich alle Duplikate in einem Bereich befinden, führen Sie die folgenden Schritte aus:

Geben Sie zunächst die Überschrift Doppelt in Zelle B1 ein. Geben Sie dann die folgende Formel in Zelle B2 ein:

=ZÄHLENWENN(A:A;A2)>1

Platzieren Sie den Mauszeiger in Zelle B2, und doppelklicken Sie auf den AutoAusfüllen-Ziehpunkt (das kleine Quadrat unten rechts in der Zelle), um die Formel in die ganze Spalte zu kopieren.

Sie können nun die Spalten nach Spalte B (absteigend) und dann nach Spalte A (aufsteigend) sortieren, um die doppelten Rechnungsnummern oben im Bereich anzuzeigen.