Farris hat ein Arbeitsblatt, das Adressen enthält. Einige Adressen sind sehr ähnlich, so dass die Straße dieselbe ist und nur der Suite-Nummer-Teil der Adresse unterschiedlich ist. Beispielsweise kann eine Zeile die Adresse „85 Seymour Street, Suite 101“ und eine andere Zeile die Adresse „85 Seymour Street, Suite 412“ haben. Farris fragt sich, wie die Duplikate in der Adressliste basierend auf einer teilweisen Übereinstimmung entfernt werden können – nur basierend auf der Adresse und unter Ignorieren der Suite-Nummer.

Die einfachste Lösung besteht darin, die Adressen weiter in separate Spalten aufzuteilen, sodass sich die Suite-Nummer in einer eigenen Spalte befindet. Sie können dies tun, indem Sie die folgenden Schritte ausführen:

  1. Stellen Sie sicher, dass sich rechts von der Adressspalte eine leere Spalte befindet.

  2. Wählen Sie die Zellen aus, die Adressen enthalten.

  3. Zeigen Sie die Registerkarte Daten des Menübands an.

  4. Klicken Sie in der Gruppe Datenwerkzeuge auf das Werkzeug Text in Spalten. Excel startet den Assistenten zum Konvertieren von Text in Spalten. (Siehe Abbildung 1.)

  5. Stellen Sie im ersten Schritt des Assistenten sicher, dass die Option Begrenzt ausgewählt ist, und klicken Sie dann auf Weiter.

  6. Stellen Sie im zweiten Schritt des Assistenten sicher, dass das Kontrollkästchen Komma aktiviert ist, und klicken Sie dann auf Weiter.

  7. Klicken Sie im dritten Schritt des Assistenten auf Fertig stellen.

Die Straße sollte sich jetzt in der ursprünglichen Spalte befinden, und die zuvor leere Spalte sollte jetzt alles enthalten, was in den ursprünglichen Adressen nach dem Komma stand. Mit anderen Worten, die Suite-Nummer befindet sich in einer eigenen Spalte. Wenn sich Ihre Daten in diesem Zustand befinden, ist es ein einfacher Schritt, die eindeutigen Straßenadressen mithilfe der Filterung anzuzeigen oder zu extrahieren.

Wenn Sie die Adressen nicht dauerhaft in zwei Spalten aufteilen möchten, können Sie mithilfe einer Formel Duplikate ermitteln. Angenommen, die Adressliste ist sortiert, könnten Sie eine Formel verwenden, die der folgenden ähnelt:

=IF(OR(ISERROR(FIND(",",A3)),ISERROR(FIND(",",A2))), "",IF(LEFT(A3,FIND(",",A3))=LEFT(A2,FIND(",",A2)), "Duplicate",""))

Diese Formel setzt voraus, dass sich die zu überprüfenden Adressen in Spalte A befinden und dass diese Formel irgendwo in Zeile 3 einer anderen Spalte steht. Zunächst wird geprüft, ob die Adresse in der aktuellen Zeile oder die Adresse in der vorherigen Zeile ein Komma enthält. Wenn in keiner der Adressen ein Komma enthalten ist, wird davon ausgegangen, dass kein Duplikat vorhanden ist.

Wenn in beiden ein Komma steht, überprüft die Formel den Teil der Adressen vor dem Komma. Wenn sie übereinstimmen, dann das Wort „Duplizieren“

ist zurück gekommen; Wenn sie nicht übereinstimmen, wird nichts zurückgegeben.

Das Ergebnis des Kopierens der Formel in die Spalte (so dass eine Formel jeder Adresse entspricht) ist, dass Sie das Wort „Duplizieren“

haben werden neben den Adressen angezeigt, die mit dem ersten Teil der vorherigen Adresse übereinstimmen. Sie können dann herausfinden, was Sie mit den gefundenen Duplikaten tun möchten.

Eine andere Möglichkeit besteht darin, ein Makro zu verwenden, um mögliche Duplikate zu ermitteln.

Es gibt eine Reihe von Möglichkeiten, wie ein Makro zum Ermitteln von Duplikaten erstellt werden kann. Das hier gezeigte vergleicht einfach die ersten X-Zeichen eines „Schlüssel“ -Werts mit einem Bereich und gibt die Adresse der ersten übereinstimmenden Zelle zurück.

Function NearMatch(vLookupValue, rng As Range, iNumChars)

Dim x As Integer     Dim sSub As String

Set rng = rng.Columns(1)

sSub = Left(vLookupValue, iNumChars)

For x = 1 To rng.Cells.Count         If Left(rng.Cells(x), iNumChars) = sSub Then             NearMatch = rng.Cells(x).Address             Exit Function         End If     Next     NearMatch = CVErr(xlErrNA)

End Function

Angenommen, Ihre Adressen liegen im Bereich A2: A100.

In Spalte B können Sie diese NearMatch-Funktion verwenden, um Adressen möglicher Duplikate zurückzugeben. Geben Sie in Zelle B2 die folgende Formel ein:

=NearMatch(A2,A3:A$100,12)

Der erste Parameter für die Funktion (A2) ist die Zelle, die Sie als „Schlüssel“ verwenden möchten. Die ersten 12 Zeichen dieser Zelle werden mit den ersten 12 Zeichen jeder Zelle im Bereich A3: A $ 100 verglichen. Wenn eine Zelle in dem Bereich gefunden wird, in dem die ersten 12 Zeichen übereinstimmen, wird die Adresse dieser Zelle von der Funktion zurückgegeben. Wenn keine Übereinstimmung gefunden wird, wird der Fehler # N / A zurückgegeben. Wenn Sie die Formel in B2 nach unten in die Zellen B3: B100 kopieren, wird jede entsprechende Adresse in Spalte A mit allen darunter liegenden Adressen verglichen. Sie erhalten eine Liste möglicher Duplikate in der ursprünglichen Liste.

_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 (7886) gilt für Microsoft Excel 2007, 2010, 2013 und 2016.

Eine Version dieses Tipps für die ältere Menüoberfläche von Excel finden Sie hier:

Entfernen von Duplikaten basierend auf einer Teilübereinstimmung.