Wie entfernt man Duplikate, aber behält leere Zeilen in Excel bei?

In Excel bietet die Funktion „Duplikate entfernen“ eine praktische Möglichkeit, wiederholte Werte aus einer Liste zu eliminieren. Allerdings entsteht oft ein häufiges Problem, wenn Ihre Listen leere Zeilen enthalten; die Standardfunktion wird diese ebenfalls entfernen und kann dadurch die ursprüngliche Struktur Ihrer Daten stören, indem sie die Lücken schließt. Dies ist oft problematisch, wenn die leeren Zeilen als logische Trennzeichen oder aus Formatierungsgründen dienen.

Wenn Ihr Ziel ist, doppelte Einträge zu entfernen, aber alle leeren Zeilen genau so zu belassen, wie sie sind, gibt es mehrere wirksame Ansätze. Diese Methoden helfen Ihnen, Ihre Liste zu bereinigen, ohne die leeren Zeilen zu beeinträchtigen, was professionellere Datenverwaltung ermöglicht und die visuelle Struktur Ihres Arbeitsblatts beibehält. Im Folgenden werden mehrere Lösungen für verschiedene Szenarien in Excel beschrieben.

Duplikate entfernen, aber Leerzeilen mit Formel und Duplikate entfernen

Duplikate auswählen und hervorheben mit Auswählen von Duplikat- & Eindeutigen Zellen

VBA-Makro: Duplikate entfernen, aber leere Zeilen automatisch behalten

Excel-Formel/Erweiterter Filter: Extrahieren Sie eindeutige Werte, während Sie leere Zeilen behalten

Duplikate entfernen, aber Leerzeilen mit Formel und Duplikate entfernen

Um Duplikate zu entfernen und die leeren Zeilen zu erhalten, ist es notwendig, zuerst zu identifizieren und zu markieren, welche Zeilen leer sind, indem man eine Hilfsspalte verwendet, und dann das in Excel integrierte Werkzeug „Duplikate entfernen“ nutzt. Diese kombinierte Methode stellt sicher, dass eindeutige Werte erhalten bleiben und alle vorhandenen leeren Zeilen unberührt bleiben. Diese Lösung funktioniert gut, wenn Sie einen integrierten, überwiegend manuellen Excel-Workflow mit sichtbaren Hilfsspalten wünschen.

Schritt 1. In einer benachbarten Spalte (zum Beispiel, wenn Ihre Daten in A1 beginnen, starten Sie in B1), geben Sie die folgende Formel ein, um leere Zeilen zu markieren:

=IF(LEN(TRIM(A1))=0,ROW(),"")

Diese Formel überprüft, ob Zelle A1 leer ist oder nur Leerzeichen enthält. Wenn ja, zeigt sie die Zeilennummer an; andernfalls bleibt die Zelle leer. Ziehen Sie die Formel nach unten neben allen Einträgen in Ihrer Liste, um jede leere Zeile zu markieren.

Hinweis: Wenn sich Ihre Daten über mehrere Spalten erstrecken, passen Sie den Bezug an (z. B. verwenden Sie A1 oder B1 je nach Bedarf). Mit TRIM wird sichergestellt, dass Zellen mit nur Leerzeichen auch als leer behandelt werden.

Schritt 2. Wählen Sie sowohl Ihre ursprüngliche Datenspalte als auch die neue Hilfsspalte aus. Gehen Sie dann zu Daten > Duplikate entfernen. Im erscheinenden Dialogfenster aktivieren Sie nur das Kontrollkästchen für Ihre ursprüngliche Spalte (nicht die Hilfsspalte), sodass Duplikate basierend auf Ihren Zielen erkannt werden.

Tipp: Stellen Sie sicher, dass Ihre Datenauswahl die Hilfsspalte einschließt, aber verwenden Sie die ursprüngliche Datenspalte als Schlüssel zum Entfernen von Duplikaten. Dadurch wird verhindert, dass versehentlich leere Zeilen entfernt werden, die durch die Hilfsspalte markiert wurden.

Schritt 3. Eine Benachrichtigung wird angezeigt, die zeigt, wie viele doppelte Werte entfernt wurden. Klicken Sie auf OK , um die Nachricht zu bestätigen und zu schließen.

An diesem Punkt zeigt die Liste alle eindeutigen Werte sowie alle zuvor vorhandenen leeren Zeilen an. Sie können die Hilfsspalte löschen, wenn Sie sie nicht mehr benötigen.

Anwendungsbereiche: Diese Methode wird empfohlen, wenn Sie nur Standard-Excel-Funktionen verwenden möchten und Ihre Daten einfach sind. Sie ist nicht vollständig automatisch, wenn Ihre Daten häufig aktualisiert werden, da die Schritte wiederholt werden müssen. Stellen Sie außerdem sicher, dass Sie vor dem Entfernen von Duplikaten eine Sicherungskopie Ihrer Daten erstellen, da dieser Vorgang nach dem Speichern und Schließen der Datei nicht rückgängig gemacht werden kann.

Fehlerbehebung: Wenn leere Zeilen immer noch entfernt werden, überprüfen Sie, ob Ihre Hilfsformel alle leeren Zeilen korrekt markiert und dass Sie nur Ihre Hauptspalte als „Entferne Duplikate“-Schlüssel im Dialogfeld verwenden.

Duplikate auswählen und hervorheben mit Auswählen von Duplikat- & Eindeutigen Zellen

Das Dienstprogramm „Auswählen von Duplikat- & Eindeutigen Zellen“ von Kutools für Excel ist besonders nützlich, um doppelte Einträge visuell zu identifizieren, bevor sie gelöscht werden, während leere Zeilen standardmäßig erhalten bleiben. Diese Lösung ist ausgezeichnet, wenn Sie Duplikate vor dem Löschen manuell überprüfen möchten oder einfach nur hervorheben wollen, um weitere Maßnahmen zu ergreifen.

1. Wählen Sie den Bereich aus, der Ihre Daten enthält (einschließlich leerer Zeilen), und klicken Sie dann auf Kutools > Auswählen > Auswählen von Duplikat- & Eindeutigen Zellen.

2. Im Auswählen von Duplikat- & Eindeutigen Zellen Dialog, wählen Sie Duplikate (außer dem Ersten) unter „Regel“. Sie können auch Hintergrundfarbe füllen und Schriftfarbe füllen aktivieren, um Duplikate automatisch mit Farben Ihrer Wahl zu markieren.

Tipp: Wenn Sie die gesamte doppelte Zeile auswählen möchten, wählen Sie den gesamten Datenbereich statt nur einer Spalte aus und wenden Sie das Dienstprogramm entsprechend an.

3. Klicken Sie auf OK. Ein Dialogfeld wird anzeigen, wie viele doppelte Zellen oder Zeilen gefunden und ausgewählt wurden. Klicken Sie auf OK , um zu schließen.

Jetzt sind die Duplikate in Ihrer Liste hervorgehoben, was es leicht macht, sie bei Bedarf manuell zu finden und zu löschen, ohne leere Zeilen zu beeinträchtigen.

Vorteile: Flexibler, risikofrei und unterstützt die Überprüfung vor dem Entfernen. Leere Zeilen bleiben standardmäßig unberührt. Dieser Ansatz ist auch nützlich, wenn Sie mehr visuelle Kontrolle wünschen oder Duplikate fallweise bearbeiten müssen.

Hinweis: Sie können die hervorgehobenen Zeilen sortieren oder filtern, wenn Sie Duplikate stapelweise löschen möchten, und dabei die visuelle Struktur Ihres ursprünglichen Arbeitsblatts beibehalten.

Demo: Duplikate & eindeutige Zellen mit Kutools für Excel auswählen

VBA-Makro: Duplikate entfernen, aber leere Zeilen automatisch behalten

Für Benutzer, die häufig den Prozess des Entfernens von Duplikaten automatisieren müssen, während sie alle leeren Zeilen beibehalten, ist die Verwendung eines VBA-Makros eine praktische Wahl. Mit einem Makro können Sie Ihre Daten mit einem Klick bereinigen, egal wie oft sich Ihre Liste ändert, und ohne auf Hilfsspalten angewiesen zu sein. Diese Methode ist ideal, wenn die Struktur Ihres Arbeitsblatts konsistent ist und Sie eine wiederholbare Lösung wünschen, die Leistung und Genauigkeit sicherstellt.

Hinweis: Speichern Sie immer eine Kopie Ihres Arbeitsblatts, bevor Sie Makros ausführen, da Aktionen nach der Ausführung nicht leicht rückgängig gemacht werden können.

1. Gehen Sie zu Entwicklertools > Visual Basic. Im VBA-Editor klicken Sie auf Einfügen > Modul. Fügen Sie dann den folgenden Code in das neue Modulfenster ein:

Sub RemoveDuplicatesKeepBlankRows()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dict As Object
    Dim cell As Range
    Dim checkRange As Range
    Dim i As Long
    
    Set ws = ActiveSheet
    Set dict = CreateObject("Scripting.Dictionary")
    
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    Set checkRange = ws.Range("A1:A" & lastRow)
    
    Application.ScreenUpdating = False
    
    For i = lastRow To 1 Step -1
        Set cell = ws.Cells(i, 1)
        
        If Trim(cell.Value) = "" Then
            ' Blank row, do nothing and keep row
        Else
            If dict.Exists(cell.Value) Then
                cell.EntireRow.Delete
            Else
                dict.Add cell.Value, True
            End If
        End If
    Next i
    
    Application.ScreenUpdating = True
End Sub

2. Um den Code auszuführen, klicken Sie auf die Schaltfläche oder drücken Sie F5 , während das Modul ausgewählt ist. Das Makro wird sofort Ihre erste Spalte (A) scannen, doppelte Werte entfernen und alle zuvor leeren Zeilen in ihren ursprünglichen Positionen belassen.

So funktioniert es: Das Makro durchläuft von unten nach oben die angegebene Spalte, löscht vorherige Werte und behält dabei alle leeren Zeilen bei. Es passt sich automatisch an Listen unterschiedlicher Länge an, indem es die letzte nicht leere Zelle identifiziert.

Parameter und Tipps:

• Wenn sich Ihre Daten in einer anderen Spalte befinden, ändern Sie Cells(i,1) in die relevante Spaltennummer (z. B. 2 für B, 3 für C).
• Das Makro startet von der letzten Zeile aufwärts, um zu verhindern, dass Zeilenschichten das Löschverfahren beeinträchtigen.
• Jede Zelle, die nur Leerzeichen enthält, wird ebenfalls als leer behandelt. Wenn Sie nur wirklich leere Zellen identifizieren möchten, ersetzen Sie Trim(Zelle.Wert) = "" durch Zelle.Wert = "".

Fehlerbehebung: Wenn das Makro nicht wie erwartet funktioniert, überprüfen Sie auf verbundene Zellen, ausgeblendete Zeilen oder unerwartete Datentypen in der Ziel-Spalte.

Am besten geeignet für: Wiederholte Bereinigungsoperationen, das Verarbeiten größerer Datensätze oder die Arbeit mit Makros als Teil eines automatisierten Workflows.

Excel-Formel/Erweiterter Filter: Extrahieren Sie eindeutige Werte, während Sie leere Zeilen behalten

Wenn Sie eine eindeutige Liste aus den Originaldaten extrahieren und leere Zeilen dort, wo sie erscheinen, ausgerichtet lassen möchten, können Sie einen erweiterten Filter mit einer Kriterienformel verwenden oder geschickt Excel-Formeln nutzen, um eine neue Liste zu erstellen. Dies ist besonders nützlich, wenn Sie eine separate, deduplizierte Liste für Berichte oder weitere Analysen erstellen möchten, wobei das ursprüngliche Layout, einschließlich der Leerzeilen, beachtet wird.

Sie können die folgende Formel verwenden, um eine Liste eindeutiger Werte zu erstellen, während Sie leere Zeilen an ihrem Platz belassen. Hier ist ein optimierter Ansatz:

1. In einer neuen Spalte (z. B. B1), verwenden Sie diese Formel (angenommen, Ihre Originaldaten sind in A1:A100):

=IF(TRIM(A1)="","",IF(COUNTIF(A$1:A1,A1)=1,A1,""))

2. Ziehen Sie diese Formel nach unten neben alle Zeilen Ihrer Daten. Eindeutige Einträge werden an ihren ursprünglichen Positionen erscheinen; nachfolgende Duplikate werden als leer angezeigt, und ursprünglich leere Zeilen bleiben leer.

Vorsichtsmaßnahmen: Stellen Sie sicher, dass es keine versteckten Werte oder führende/nachlaufende Leerzeichen gibt, die die Eindeutigkeit beeinflussen könnten. TRIM hilft dabei, dies zu vermeiden. Für ältere Versionen von Excel müssen Methoden möglicherweise angepasst werden oder Hilfsspalten mit manueller Filterung verwendet werden.

Geeignet für: Neue Berichte erstellen, nebeneinander liegende Listen oder bereinigte Daten für die weitere Verarbeitung vorbereiten. Dieser Ansatz ist auch ideal für diejenigen, die eine rein formelbasierte Herangehensweise ohne VBA oder Add-ins wünschen.