Doppelte Einträge über mehrere Spalten kontrollieren und automatisch per Farbe signalisieren

Arbeiten mehrere Personen an einer Liste oder werden Daten aus Fremdsystemen importiert, erhöht sich das Risiko, dass Datensätze mehrfach vorkommen. Excel bietet zwar die Befehlsfolge DatenDuplikate entfernen, aber was dabei passiert, lässt sich nur zum Teil kontrollieren. Daher ziehen es viele Anwender vor, zunächst alle mehrfach vorkommenden Datensätze kenntlich zu machen. Dafür gibt es in Excel eine vorgefertigte Regel (StartBedingte Formatierung – Regeln zum Hervorheben von Zellen – Doppelte Werte). Doch sie hat einen Nachteil: Sie untersucht stets nur EINE Spalte nach identischen Daten.

Muss jedoch – wie im Beispiel in Bild 1 – überprüft werden, ob GLEICHZEITIG in MEHREREN Spalten identische Daten stehen, ist ein anderer Lösungsweg erforderlich.
Hier führt eine Bedingte Formatierung in Verbindung mit ZÄHLENWENNS zum Ziel.

Vorschau auf die fertige Lösung

Bild 1: Links die Ausgangsliste, rechts sind die Datensätze gekennzeichnet, die in den drei Spalten „Systemnr.“, „Von“ sowie „Fehlercode“ mehr als einmal vorkommen

Die spezielle Formatierungsregel anlegen

Die Regel wird in drei Schritten aufgestellt:
1) Markieren der betreffenden Daten.
2) Eingeben der Formel, die auf ZÄHLENWENNS beruht.
3) Wählen der Kennzeichnungsfarbe.

Die Liste, in der zunächst einmal die Datensätze markiert werden

Bild 2: In der Liste den Bereich von A3 bis E11 markieren

  • Zunächst markiere ich den zu überwachenden Datenbereich – hier also A3:E11.
  • Dann wähle ich über die Registerkarte Start die Befehlsfolge Bedingte Formatierung – Neue Regel und klicke im folgenden Dialogfeld – wie in Bild 3 zu sehen – die unterste Regel an.

Die Formatierungsregel aufstellen

Bild 3: Im Dialogfeld zum Aufstellen der Regel den untersten Regeltyp wählen

Nun gebe ich in das Eingabefeld die in Bild 4 gezeigte Formel ein, die zunächst mit Hilfe von ZÄHLENWENNS prüft, wie oft ein Wert in den Spalten B, D und E vorkommt. Mit der anschließenden Anweisung >1 werden alle Ergebnisse der ZÄHLENWENNS-Funktion herausgefiltert, wo Daten in allen drei untersuchten Spalten mehr als einmal vorkommen. Für diese Daten trifft der Wert WAHR zu und genau auf diese Datensätze wird eine farbige Kennzeichnung angewendet.

Absolute Bezüge für die Spalten setzen

Bild 4: Die Formel in der Vergrößerung: Wichtig sind die gemischten Bezüge für die Suchkriterien

Wichtig beim Anlegen der Formel: Korrekte Wahl der Art der Zellbezüge

  • Die zu durchsuchenden Bereiche in Spalte B, D und E werden nach dem Markieren jeweils mit einmal F4 auf absolut gesetzt: also $B$3:$B$11, $D$3:$D$11 und $E$3:$E$11.
  • Der zu prüfende Wert (das Suchkriterium) wird ebenfalls für die Spalte auf absolut gesetzt ($B, $D, $E), muss aber bei der Zeile relativ bleiben, die Zeilennummer muss sich also anpassen. Dazu betätige ich nach Anklicken des Suchkriteriums (B3, D3, E3) jeweils dreimal die F4-Taste und erhalte so $B3, $D3 sowie $E3.
  • Anschließend klicke ich rechts unten auf die Schaltfläche Formatieren und wähle über die Registerkarte Ausfüllen eine gut sichtbare Füllfarbe zum Signalisieren der Datensätze, die in den Spalten B, D und E identische Daten haben.
  • Mit OK schließe ich alle Dialogfelder.

Ausblick

Mit der hier gezeigten Lösung lassen sich IM NACHHINEIN mehrfach vorkommende Daten in mehreren Spalten nachweisen.

Wie sich VON VORNHEREIN vermeiden lässt, dass gleiche Datensätze mehrfach eingegeben werden, zeige ich in diesem Beitrag.

 

Dieser Beitrag wurde unter Excel abgelegt und mit , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.

3 Antworten auf Doppelte Einträge über mehrere Spalten kontrollieren und automatisch per Farbe signalisieren

  1. rayko lehmann sagt:

    Hallo,

    ich habe eine Frage dazu bevor ich Ihre Lösung angehen werde. Ich habe versucht 3x Felder zu verketten, darauf dann die bedingte Formatierung / Doppelte Werte laufen lassen. Warum funktioniert diese nicht?

    Danke und VG
    R.Lehmann

    • Auch diese Variante funktioniert … erstelle eine neue Spalte, die die drei relevanten Felder verkettet (beispielsweise =A1&B1&C1) und wende auf diese Spalte Start > Bedingte Formatierung > Doppelte Werte an. Allerdings ist darüber nur die Markierung in der neu berechneten Spalte möglich, nicht aber eine komplette Zeilenmarkierung.

  2. Klaus Selle sagt:

    Besten Dank dieser Beitrag hat mir weitergeholfen. Doch das Problem ist sie zu finden.

    MFG Klaus

Schreibe einen Kommentar

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