У меня есть лист, где столбец А имеет разные имена в разных форматах:
А1 Джон Смит
А2 Джонс, Мэри
A3 Салли Гомес
A4 Семья Гонсалес
Столбец B имеет аналогичные данные:
B1 Смит Фэмили Траст
B2 Боб и Мэри Джонс
B3 Blackwell, Джон
B4 Луз Гонсалес
Я хотел бы определить случаи, когда одна и та же фамилия встречается в столбце A и столбце B. В приведенных выше примерах формула, если ее поместить в столбец C, приведет к
C1 TRUE (потому что "Смит" встречается как в A1, так и в B1)
C2 TRUE (потому что "Джонс" встречается как в A2, так и в B2)
C3 FALSE (потому что между A3 и B3 нет общих слов)
C4 TRUE (потому что "Гонсалес" встречается как в A4, так и в B4)
Это вообще возможно?
3
4 ответа
2
Учитывая ваши комментарии, а также ваш вопрос, кажется, что вы хотите вернуть 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
РЕДАКТИРОВАТЬ: Вот снимок экрана результатов, используя как ваши оригинальные примеры, а также примеры, которые вы дали в комментарии ниже, где вы указали, что у вас возникли проблемы.
1
Самая сложная часть этого упражнения - определить, что в колонке А представляет собой фамилию. В вашем примере это либо:
- Первое слово, если во всем имени есть запятая
- Второе слово
Если это правило верно, то вы можете просто сделать формулу, например, так:
=NOT(ISERROR(FIND(last_name, B1:B4)))
Формула для фактического определения фамилии немного сложнее. По сути, вы должны выяснить, в каких позициях символов находятся пробелы, а затем потянуть буквы между ними. В этой теме есть хорошее объяснение:
0
В ячейке С1 используйте эту формулу Ссылка
=IF(VLOOKUP(B1:B4,A:A,1)=B1,"",B1)
0
Выделите оба столбца> условное форматирование (домашняя вкладка)> выделите правила ячейки> дублирующиеся значения.
Это выделит все дубликаты в обоих столбцах.
Убедитесь, что вы выделяете столбцы, а не ячейки.