Connie hat ein Arbeitsblatt mit Firmennamen in jeder Zelle der Spalte B.

Sie sind in Spalte A unter einer Regionsüberschrift (Nordosten, Westen usw.) zusammengefasst. Sie möchte eine bedingte Formatierung auf die Firmennamen anwenden, sodass ein Name, der in mehr als einer Region erscheint, mit einem Hintergrund oder Text angezeigt wird Farbe, mit der die passenden Unternehmen leicht zu finden sind. Dies bedeutet, dass, wenn ein Unternehmen als rot formatiert ist, kein anderes Unternehmen als rot angezeigt werden sollte (es sollte in einer anderen Farbe angezeigt werden, z. B. blau oder grün). Connie ist sich nicht sicher, wie sie dies einrichten soll oder ob es überhaupt mit bedingter Formatierung möglich ist.

Es gibt eine Möglichkeit, Duplikate mithilfe der bedingten Formatierung zu markieren. Befolgen Sie einfach diese allgemeinen Schritte:

  1. Wählen Sie die Zellen aus, die Ihre Firmennamen enthalten.

  2. Zeigen Sie die Registerkarte „Startseite“ des Menübands an und klicken Sie in der Gruppe „Stile“ auf das Werkzeug „Bedingte Formatierung“.

  3. Wählen Sie Neue Regel. Excel zeigt das Dialogfeld Neue Formatierungsregel an.

  4. Klicken Sie oben im Dialogfeld auf die Option Nur eindeutige oder doppelte Werte formatieren.

  5. Stellen Sie sicher, dass Duplizieren in der Dropdown-Liste in der unteren Hälfte des Dialogfelds ausgewählt ist. (Siehe Abbildung 1.)

  6. Klicken Sie auf die Schaltfläche Formatieren und ändern Sie die Formatierung, um zu berücksichtigen, wie doppelte Firmennamen angezeigt werden sollen.

  7. Klicken Sie auf OK, um das Dialogfeld Neue Formatierungsregel zu schließen.

Zu diesem Zeitpunkt sollten alle Ihre Duplikate mit der in Schritt 6 ausgewählten Formatierung übereinstimmen. Das einzige Problem besteht darin, dass alle Duplikate auf dieselbe Weise formatiert werden. Mit anderen Worten, wenn Sie zwei Unternehmen haben (ABC Company und DEF Company) und es Duplikate für diese Unternehmen gibt, werden sie alle auf dieselbe Weise formatiert – Sie werden keine unterschiedlichen Formatierungen für die beiden Unternehmen sehen.

Natürlich können Sie die Filterfunktionen von Excel problemlos verwenden, um doppelte Unternehmen, nicht doppelte Unternehmen oder einzelne Firmennamen herauszusuchen. Dies ist möglicherweise der einfachste Weg, sich auf die Unternehmen zu konzentrieren, die Sie suchen möchten.

Die einzige Möglichkeit, die bedingte Formatierung zum Anwenden unterschiedlicher Farben auf verschiedene Gruppen doppelter Firmennamen zu verwenden, besteht darin, die tatsächlichen Duplikate im Voraus zu identifizieren. Mit dieser Liste können Sie eine Reihe von Regeln für die bedingte Formatierung erstellen, die ähnliche Formeln verwenden:

=AND(ISNUMBER(FIND("ABC Company",B1)),COUNTIF($B$1:$B$99,"ABC Company")>1)

In dieser Formel ist „ABC Company“ der Firmenname, B1 ist die erste Zelle des Bereichs und B1: B99 ist der gesamte Zellbereich. Für jede Formatierungsregel können Sie eine andere Formatierung anwenden, die für das jeweilige Unternehmen geeignet ist. Das heißt, wenn Sie im Voraus wüssten, dass es 24 verschiedene Firmennamen mit Duplikaten gibt, müssten Sie 24 Regeln für die bedingte Formatierung einrichten, um diese 24 Namen zu verarbeiten.

In der Tat komplex. Leider gibt es keinen einfacheren Weg, die bedingte Formatierung zu verwenden. Sie können jedoch auf die bedingte Formatierung verzichten und ein Makro verwenden, um Ihre Duplikate hervorzuheben. Das einfachste „automatische“ Makro, das wir entwickeln könnten (bei dem Sie die doppelten Namen nicht im Voraus kennen müssen), untersucht eine Reihe von Zellen und legt die interne Zellenfarbe basierend auf doppelten Firmennamen fest.

Sub ColorCompanyDuplicates()

Dim x As Integer     Dim y As Integer     Dim lRows As Long     Dim lColNum As Long     Dim iColor As Integer     Dim iDupes As Integer     Dim bFlag As Boolean

lRows = Selection.Rows.Count     lColNum = Selection.Column     iColor = 2

For x = 2 To lRows         bFlag = False         For y = 2 To x - 1             If Cells(y, lColNum) = Cells(x, lColNum) Then                 bFlag = True                 Exit For             End If         Next y         If Not bFlag Then             iDupes = 0             For y = x + 1 To lRows                 If Cells(y, lColNum) = Cells(x, lColNum) Then                     iDupes = iDupes + 1                 End If             Next y             If iDupes > 0 Then                 iColor = iColor + 1                 If iColor > 56 Then                     MsgBox "Too many duplicate companies!", vbCritical                     Exit Sub                 End If                 Cells(x, lColNum).Interior.ColorIndex = iColor                 For y = x + 1 To lRows                     If Cells(y, lColNum) = Cells(x, lColNum) Then                         Cells(y, lColNum).Interior.ColorIndex = iColor                     End If                 Next y             End If         End If     Next x End Sub

Um das Makro zu verwenden, wählen Sie einfach die Zellen aus, die die Firmennamen enthalten, und führen Sie es dann aus. Das Makro durchläuft drei Zellen. Der erste Durchgang blickt von der aktuell untersuchten Zelle rückwärts durch die Zellen. es wird verwendet, um festzustellen, ob es „rückwärts“

gibt Duplikate, denn wenn dann keine weitere Verarbeitung für diese bestimmte Zelle erforderlich ist. Der zweite Durchgang schaut durch die Zellen, um festzustellen, ob der aktuelle Firmenname doppelt vorhanden ist. Wenn dies der Fall ist, erhöht ein dritter Durchgang den Zellfarbenwert und wendet ihn dann auf die Duplikate an.

Beachten Sie, dass das Makro die ColorIndex-Eigenschaft aller gefundenen Duplikate festlegt und die Variable erhöht, mit der die Eigenschaft festgelegt wird, wenn ein neuer Satz doppelter Firmennamen gefunden wird. Für alle Firmennamen, für die es keine Duplikate gibt, wird die ColorIndex-Eigenschaft der Zelle nicht geändert. Dies bedeutet, dass die Anzahl der Unternehmen begrenzt ist. Der ColorIndex kann jedoch nur zwischen 0 und 56 liegen. Die vom Makro tatsächlich zugewiesenen Werte reichen von 3 bis 56, sodass nur 54 Unternehmensgruppen formatiert werden können .

_Hinweis: _

Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.

Klicken Sie hier, um diese spezielle Seite in einem neuen Browser-Tab zu öffnen.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (12673) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365.