Como identificar duplicatas no texto em duas colunas no Excel

Question

Eu tenho uma planilha onde a coluna A tem vários nomes em formatos variados:
A1 John Smith
A2 Jones, Mary
A3 Sally Gomez
A4 A família Gonzalez

A coluna B tem dados semelhantes:
B1 The Smith Family Trust
B2 Bob e Mary Jones
B3 Blackwell, John
B4 Luz Gonzalez,

gostaria de identificar os casos em que o mesmo sobrenome é encontrado na coluna A e na coluna B. Nos exemplos acima, a fórmula, se colocada na coluna C, resultaria em

C1 TRUE (porque "Smith" é encontrado em ambos A1 e B1)
C2 VERDADEIRO (porque "Jones" é encontrado em A2 e B2)
C3 FALSO (porque não há palavras comuns entre A3 e B3)
C4 VERDADEIRO (porque "Gonzalez" é encontrado em A4 e B4)

Isto é mesmo possível?

Answer 1

Dados seus comentários e também sua pergunta, parece que você deseja retornar TRUE se alguma palavra em uma frase corresponder a uma palavra na frase adjacente. Uma maneira de fazer isso é com uma função definida pelo usuário (VBA). O seguinte exclui quaisquer palavras que estejam em arrExclude, que você pode adicionar conforme achar adequado. Também excluirá quaisquer caracteres que não sejam letras, dígitos ou espaços, e quaisquer palavras que consistam em apenas um único caractere.

Veja se isso funciona para você.

Outra opção seria dar uma olhada no suplemento gratuito de pesquisa difusa fornecido pela MS para versões do Excel 2007 e posteriores.

Para inserir esta Função Definida pelo Usuário (UDF), alt-F11abra o Editor do Visual Basic. Certifique-se de que seu projeto esteja destacado na janela Project Explorer. Em seguida, no menu superior, selecione Insert/Modulee cole o código abaixo na janela que se abre.

Para usar esta função definida pelo usuário (UDF), insira uma fórmula como

=WordMatch(A1,B1)

em alguma cela.

EDITAR2: Find Matchessegmento alterado para ver se funciona melhor no 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

---

EDITAR:Aqui está uma captura de tela dos resultados, usando seus exemplos originais e também os exemplos que você forneceu em um comentário abaixo, onde indicou que estava tendo um problema.

Answer 2

A parte mais difícil deste exercício é determinar o que, na coluna A, constitui um sobrenome. No seu exemplo, é:

1. A primeira palavra, se houver vírgula no nome inteiro
2. A segunda palavra

Se essa regra for verdadeira, você pode simplesmente fazer uma fórmula como esta:

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

A fórmula para realmente determinar o sobrenome é um pouco mais complexa. Basicamente, você precisa descobrir em que posições dos caracteres estão os espaços e, em seguida, colocar as letras entre elas. Há uma boa explicação neste tópico:

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

Answer 3

Na célula C1 use esta fórmulaLink

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

Answer 4

Destaque ambas as colunas > formatação condicional (guia inicial) > destaque regras de células > valores duplicados. Isso destacará todas as duplicatas em ambas as colunas.
Certifique-se de destacar as colunas e não as células.