So identifizieren Sie Duplikate in Text in zwei Spalten in Excel

Question

Ich habe ein Arbeitsblatt, in dem Spalte A verschiedene Namen in unterschiedlichen Formaten enthält:
A1 John Smith
A2 Jones, Mary
A3 Sally Gomez
A4 Die Familie Gonzalez

Spalte B enthält ähnliche Daten:
B1 The Smith Family Trust
B2 Bob und Mary Jones
B3 Blackwell, John
B4 Luz Gonzalez

Ich möchte die Fälle ermitteln, in denen in Spalte A und Spalte B derselbe Nachname vorkommt. In den obigen Beispielen würde die Formel, wenn sie in Spalte C eingesetzt würde, Folgendes ergeben:

C1 WAHR (weil „Smith“ sowohl in A1 als auch in B1 vorkommt)
C2 WAHR (weil „Jones“ sowohl in A2 als auch in B2 vorkommt)
C3 FALSCH (weil es zwischen A3 und B3 keine gemeinsamen Wörter gibt)
C4 WAHR (weil „Gonzalez“ sowohl in A4 als auch in B4 vorkommt)

Ist das überhaupt möglich?

Answer 1

Aus Ihren Kommentaren und Ihrer Frage geht hervor, dass Sie TRUE zurückgeben möchten, wenn ein beliebiges Wort in einer Phrase mit einem Wort in der angrenzenden Phrase übereinstimmt. Dies lässt sich beispielsweise mit einer benutzerdefinierten Funktion (VBA) erreichen. Im Folgenden werden alle Wörter ausgeschlossen, die in enthalten sind arrExclude, und Sie können nach Belieben weitere hinzufügen. Außerdem werden alle Zeichen ausgeschlossen, die keine Buchstaben, Ziffern oder Leerzeichen sind, sowie alle Wörter, die nur aus einem einzigen Zeichen bestehen.

Probieren Sie aus, ob das für Sie funktioniert.

Eine andere Möglichkeit wäre, einen Blick auf das kostenlose Fuzzy-Lookup-Add-In zu werfen, das von MS für Excel-Versionen ab 2007 bereitgestellt wird.

Um diese benutzerdefinierte Funktion (UDF) einzugeben, alt-F11öffnen Sie den Visual Basic-Editor. Stellen Sie sicher, dass Ihr Projekt im Fenster „Projekt-Explorer“ hervorgehoben ist. Wählen Sie dann im oberen Menü Insert/Moduleden folgenden Code aus und fügen Sie ihn in das sich öffnende Fenster ein.

Um diese benutzerdefinierte Funktion (UDF) zu verwenden, geben Sie eine Formel wie folgt ein:

=WordMatch(A1,B1)

in irgendeiner Zelle.

EDIT2: Find MatchesSegment geändert, um zu sehen, ob es auf dem Mac besser funktioniert

---

Option Explicit
Option Base 0
Option Compare Text
Function WordMatch(S1 As String, S2 As String) As Boolean
    Dim arrExclude() As Variant
    Dim V1 As Variant, V2 As Variant
    Dim I As Long, J As Long, S As String
    Dim RE As Object
    Dim sF As String, sS As String

'Will also exclude single letter words
arrExclude = Array("The", "And", "Trust", "Family", "II", "III", "Jr", "Sr", "Mr", "Mrs", "Ms")

'Remove all except letters, digits, and spaces
'remove extra spaces
'Consider whether to retain hyphens

Set RE = CreateObject("vbscript.regexp")
With RE
    .Pattern = "[^A-Z0-9 ]+|\b\S\b|\b(?:" & Join(arrExclude, "|") & ")\b"
    .Global = True
    .ignorecase = True
End With

With WorksheetFunction
    V1 = Split(.Trim(RE.Replace(S1, "")))
    V2 = Split(.Trim(RE.Replace(S2, "")))
End With

'Find Matches
If UBound(V1) <= UBound(V2) Then
    sS = " " & Join(V2) & " "
    For I = 0 To UBound(V1)
        sF = " " & V1(I) & " "
        If InStr(sS, sF) > 0 Then
            WordMatch = True
            Exit Function
        End If
    Next I
Else
    sS = " " & Join(V1) & " "
    For I = 0 To UBound(V2)
        sF = " " & V2(I) & " "
        If InStr(sS, sF) > 0 Then
            WordMatch = True
            Exit Function
        End If
    Next I
End If

WordMatch = False

End Function

---

BEARBEITEN:Hier ist ein Screenshot der Ergebnisse. Dabei werden sowohl Ihre ursprünglichen Beispiele als auch die Beispiele verwendet, die Sie in einem Kommentar weiter unten angegeben haben, in dem Sie auf ein Problem hingewiesen haben.

Answer 2

Der schwierigste Teil dieser Übung besteht darin, zu bestimmen, was in Spalte A einen Nachnamen darstellt. In Ihrem Beispiel ist es entweder:

1. Das erste Wort, wenn im gesamten Namen ein Komma vorkommt
2. Das zweite Wort

Wenn diese Regel zutrifft, können Sie einfach eine Formel wie diese verwenden:

=NOT(ISERROR(FIND(last_name, B1:B4)))

Die Formel zur tatsächlichen Ermittlung des Nachnamens ist etwas komplexer. Sie müssen im Wesentlichen herausfinden, an welchen Zeichenpositionen sich die Leerzeichen befinden, und dann die Buchstaben dazwischen ziehen. In diesem Thread gibt es eine gute Erklärung:

http://www.mrexcel.com/forum/excel-questions/17559-return-first-second-word-text-string-find-subsitute.html

Answer 3

Verwenden Sie in Zelle C1 diese FormelVerknüpfung

=IF(VLOOKUP(B1:B4,A:A,1)=B1,"",B1)

Answer 4

Markieren Sie beide Spalten > bedingte Formatierung (Registerkarte „Start“) > Zellregeln markieren > doppelte Werte. Dadurch werden alle Duplikate in beiden Spalten markiert.
Stellen Sie sicher, dass Sie die Spalten und nicht die Zellen markieren.