☰

Как определить дубликаты в тексте в двух столбцах в Excel

tag-icon • tag-icon microsoft-excel worksheet-function
Как определить дубликаты в тексте в двух столбцах в Excel

У меня есть рабочий лист, в котором в столбце A указаны различные имена в разных форматах:
A1 Джон Смит
A2 Джонс, Мэри
A3 Салли Гомес
A4 Семья Гонсалес

В столбце B указаны похожие данные:
B1 Семейный траст Смит
B2 Боб и Мэри Джонс
B3 Блэквелл, Джон
B4 Лус Гонсалес

Я хотел бы определить случаи, когда в столбце A и столбце B встречается одна и та же фамилия. В приведенных выше примерах формула, если ее поместить в столбец C, даст результат

C1 ИСТИНА (потому что «Смит» встречается и в A1, и в B1)
C2 ИСТИНА (потому что «Джонс» встречается и в A2, и в B2)
C3 ЛОЖЬ (потому что между A3 и B3 нет общих слов)
C4 ИСТИНА (потому что «Гонсалес» встречается и в A4, и в B4)

Возможно ли это вообще?

решение1

Учитывая ваши комментарии, а также ваш вопрос, похоже, вы хотите вернуть TRUE, если любое слово в одной фразе совпадает со словом в соседней фразе. Один из способов сделать это — использовать определяемую пользователем функцию (VBA). Следующий код исключает все слова, которые находятся в arrExclude, которые вы можете добавлять по своему усмотрению. Он также исключит все символы, которые не являются буквами, цифрами или пробелами, и все слова, которые состоят только из одного символа.

Посмотрите, подойдет ли это вам.

Другой вариант — взглянуть на бесплатную надстройку нечеткого поиска, предоставляемую MS для Excel версии 2007 и более поздних.

Чтобы ввести эту пользовательскую функцию (UDF), alt-F11откройте редактор Visual Basic. Убедитесь, что ваш проект выделен в окне Project Explorer. Затем в верхнем меню выберите Insert/Moduleи вставьте код ниже в открывшееся окно.

Чтобы использовать эту пользовательскую функцию (UDF), введите формулу, например:

=WordMatch(A1,B1)

в какой-то камере.

ПРАВКА2: Find Matchesсегмент изменен, чтобы проверить, будет ли он работать лучше на Mac

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

РЕДАКТИРОВАТЬ:Вот скриншот результатов, на котором показаны как ваши исходные примеры, так и примеры, которые вы привели в комментарии ниже, где указали, что у вас возникла проблема.

введите описание изображения здесь

решение2

Самая сложная часть этого упражнения — определить, что в столбце A является фамилией. В вашем примере это либо:

  1. Первое слово, если в имени есть запятая
  2. Второе слово

Если это правило верно, то вы можете просто воспользоваться такой формулой:

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

Формула для определения фамилии немного сложнее. По сути, вам нужно выяснить, в каких позициях символов находятся пробелы, а затем вытащить буквы между ними. Есть хорошее объяснение в этой теме:

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

решение3

В ячейке C1 используйте эту формулуСвязь

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

решение4

Выделите оба столбца > условное форматирование (вкладка «Главная») > правила выделения ячеек > дублирующиеся значения. Это выделит все дубликаты в обоих столбцах.
Убедитесь, что вы выделяете столбцы, а не ячейки.

Связанный контент