Comparação de cadeias de texto semelhantes em Excel

Pode considerar a utilização do Microsoft Fuzzy Lookup Addin .

A partir do site MS:

Visão geral & > O Fuzzy Lookup Add-In para Excel foi desenvolvido pela Microsoft Research e efectua a correspondência fuzzy de dados textuais no Microsoft Excel. Pode ser utilizado para identificar linhas duplas difusas dentro de uma única tabela ou para juntar linhas semelhantes entre duas tabelas diferentes. A correspondência é robusta a uma grande variedade de erros, incluindo erros ortográficos, abreviaturas, sinónimos e dados adicionados/falecidos. Por exemplo, pode detectar que as linhas “Mr. Andrew Hill”, “Hill, Andrew R.” e “Andy Hill” se referem todas à mesma entidade subjacente, devolvendo uma pontuação de similaridade juntamente com cada partida. Embora a configuração padrão funcione bem para uma grande variedade de dados textuais, tais como nomes de produtos ou endereços de clientes, a correspondência também pode ser personalizada para domínios ou idiomas específicos.

Eu estudaria a utilização de isto lista (apenas na secção inglesa) para ajudar a eliminar os encurtamentos comuns.

Adição, talvez queira considerar a utilização de uma função que lhe dirá, em termos exactos, como “fechar” duas cordas. O seguinte código veio de aqui e graças a smirkingman .

Option Explicit
Public Function Levenshtein(s1 As String, s2 As String)

Dim i As Integer
Dim j As Integer
Dim l1 As Integer
Dim l2 As Integer
Dim d() As Integer
Dim min1 As Integer
Dim min2 As Integer

l1 = Len(s1)
l2 = Len(s2)
ReDim d(l1, l2)
For i = 0 To l1
    d(i, 0) = i
Next
For j = 0 To l2
    d(0, j) = j
Next
For i = 1 To l1
    For j = 1 To l2
        If Mid(s1, i, 1) = Mid(s2, j, 1) Then
            d(i, j) = d(i - 1, j - 1)
        Else
            min1 = d(i - 1, j) + 1
            min2 = d(i, j - 1) + 1
            If min2 < min1 Then
                min1 = min2
            End If
            min2 = d(i - 1, j - 1) + 1
            If min2 < min1 Then
                min1 = min2
            End If
            d(i, j) = min1
        End If
    Next
Next
Levenshtein = d(l1, l2)
End Function

O que isto fará é dizer-lhe quantas inserções e supressões uma deve fazer a uma cadeia de caracteres para chegar à outra. Eu tentaria manter este número baixo (e os sobrenomes devem ser exactos).

Tenho uma fórmula (longa) que pode utilizar. Não é tão bem aperfeiçoada como as anteriores - e só funciona pelo apelido, em vez de um nome completo - mas pode achá-la útil.

Portanto, se tiver uma linha de cabeçalho e quiser comparar A2 com B2, coloque isto em qualquer outra célula dessa linha (por exemplo, C2) e copie até ao fim.

=IF(A2=B2, “EXACTO”,IF(SUBSTITUTO(A2,“-”,“ ”)=SUBSTITUTO(B2,“-”,“ ”), “Hífen”,IF(LEN(A2)>LEN(B2),IF(LEN(A2)>LEN(SUBSTITUTO(A2,B2,“”)), “Cordão Inteiro”,IF(MID(A2,1, 1)=MID(B2,1,1),1,0)+IF(MID(A2,2,1)=MID(B2,2,1),1,0)+IF(MID(A2,3,1)=MID(B2,3,1),1,0)+IF(MID(A2,LEN(A2),1)=MID(B2,LEN(B2),1),1,0)+IF(MID(A2,LEN(A2)-1,1)=MID(B2,LEN(B2)-1,1),1, 0)+IF(MID(A2,LEN(A2)-2,1)=MID(B2,LEN(B2)-2,1),1,0)&“°”),IF(LEN(B2)>LEN(SUBSTITUTO(B2,A2,“)), "Whole String”,IF(MID(A2,1,1)=MID(B2,1,1),1,0)+IF(MID(A2,2,1)=MID(B2,2,1),1, 0)+IF(MID(A2,3,1)=MID(B2,3,1),1,0)+IF(MID(A2,LEN(A2),1)=MID(B2,LEN(B2),1),1,0)+IF(MID(A2,LEN(A2)-1,1)=MID(B2,LEN(B2)-1,1),1,0)+IF(MID(A2,LEN(A2)-2,1)=MID(B2,LEN(B2)-2,1),1,0)&“°”))))

Isto irá regressar:

• EXACT* - se for um par exacto
• Hyphen - se for um par de nomes com duas barras, mas o outro tiver um hífen e o outro um espaço
• Whole string* - se todo um apelido fizer parte do outro (por exemplo, se um Smith se tiver tornado um Francês-Smith)

Depois disso, dar-lhe-á um grau de 0° a 6°, dependendo do número de pontos de comparação entre os dois. (i.e., 6° compara melhor).

Como eu digo um pouco áspero e pronto, mas esperemos que nos coloque aproximadamente no lugar certo da bola.

Estava à procura de algo semelhante. Encontrei o código abaixo. Espero que isto ajude o próximo utilizador que chegar a esta pergunta

Retorna 91% para Abracadabra / Abrakadabra, 75% para Hollywood Street/Hollyhood Str, 62% para Florence/France e 0 para Disneyland

Eu diria que está suficientemente perto do que queria :)

Public Function Similarity(ByVal String1 As String, _
    ByVal String2 As String, _
    Optional ByRef RetMatch As String, _
    Optional min_match = 1) As Single
Dim b1() As Byte, b2() As Byte
Dim lngLen1 As Long, lngLen2 As Long
Dim lngResult As Long

If UCase(String1) = UCase(String2) Then
    Similarity = 1
Else:
    lngLen1 = Len(String1)
    lngLen2 = Len(String2)
    If (lngLen1 = 0) Or (lngLen2 = 0) Then
        Similarity = 0
    Else:
        b1() = StrConv(UCase(String1), vbFromUnicode)
        b2() = StrConv(UCase(String2), vbFromUnicode)
        lngResult = Similarity_sub(0, lngLen1 - 1, _
        0, lngLen2 - 1, _
        b1, b2, _
        String1, _
        RetMatch, _
        min_match)
        Erase b1
        Erase b2
        If lngLen1 >= lngLen2 Then
            Similarity = lngResult / lngLen1
        Else
            Similarity = lngResult / lngLen2
        End If
    End If
End If

End Function

Private Function Similarity_sub(ByVal start1 As Long, ByVal end1 As Long, _
                                ByVal start2 As Long, ByVal end2 As Long, _
                                ByRef b1() As Byte, ByRef b2() As Byte, _
                                ByVal FirstString As String, _
                                ByRef RetMatch As String, _
                                ByVal min_match As Long, _
                                Optional recur_level As Integer = 0) As Long
'* CALLED BY: Similarity *(RECURSIVE)

Dim lngCurr1 As Long, lngCurr2 As Long
Dim lngMatchAt1 As Long, lngMatchAt2 As Long
Dim I As Long
Dim lngLongestMatch As Long, lngLocalLongestMatch As Long
Dim strRetMatch1 As String, strRetMatch2 As String

If (start1 > end1) Or (start1 < 0) Or (end1 - start1 + 1 < min_match) _
Or (start2 > end2) Or (start2 < 0) Or (end2 - start2 + 1 < min_match) Then
    Exit Function '(exit if start/end is out of string, or length is too short)
End If

For lngCurr1 = start1 To end1
    For lngCurr2 = start2 To end2
        I = 0
        Do Until b1(lngCurr1 + I) <> b2(lngCurr2 + I)
            I = I + 1
            If I > lngLongestMatch Then
                lngMatchAt1 = lngCurr1
                lngMatchAt2 = lngCurr2
                lngLongestMatch = I
            End If
            If (lngCurr1 + I) > end1 Or (lngCurr2 + I) > end2 Then Exit Do
        Loop
    Next lngCurr2
Next lngCurr1

If lngLongestMatch < min_match Then Exit Function

lngLocalLongestMatch = lngLongestMatch
RetMatch = ""

lngLongestMatch = lngLongestMatch _
+ Similarity_sub(start1, lngMatchAt1 - 1, _
start2, lngMatchAt2 - 1, _
b1, b2, _
FirstString, _
strRetMatch1, _
min_match, _
recur_level + 1)
If strRetMatch1 <> "" Then
    RetMatch = RetMatch & strRetMatch1 & "*"
Else
    RetMatch = RetMatch & IIf(recur_level = 0 _
    And lngLocalLongestMatch > 0 _
    And (lngMatchAt1 > 1 Or lngMatchAt2 > 1) _
    , "*", "")
End If

RetMatch = RetMatch & Mid$(FirstString, lngMatchAt1 + 1, lngLocalLongestMatch)

lngLongestMatch = lngLongestMatch _
+ Similarity_sub(lngMatchAt1 + lngLocalLongestMatch, end1, _
lngMatchAt2 + lngLocalLongestMatch, end2, _
b1, b2, _
FirstString, _
strRetMatch2, _
min_match, _
recur_level + 1)

If strRetMatch2 <> "" Then
    RetMatch = RetMatch & "*" & strRetMatch2
Else
    RetMatch = RetMatch & IIf(recur_level = 0 _
    And lngLocalLongestMatch > 0 _
    And ((lngMatchAt1 + lngLocalLongestMatch < end1) _
    Or (lngMatchAt2 + lngLocalLongestMatch < end2)) _
    , "*", "")
End If

Similarity_sub = lngLongestMatch

End Function

Este código escaneia a coluna a e a coluna b, se encontrar alguma semelhança em ambas as colunas, mostra-se a amarelo. Pode usar um filtro de cor para obter o valor final. Não adicionei essa parte ao código.

Sub item_difference()

Range("A1").Select

last_row_all = Range("A65536").End(xlUp).Row
last_row_new = Range("B65536").End(xlUp).Row

Range("A1:B" & last_row_new).Select
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 65535
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With

For i = 1 To last_row_new
For j = 1 To last_row_all

If Range("A" & i).Value = Range("A" & j).Value Then

Range("A" & i & ":B" & i).Select
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = 0
  .PatternTintAndShade = 0
End With

End If
Next j
Next i
End Sub

Embora a minha solução não permita identificar cordas muito diferentes, é útil para a correspondência parcial (correspondência de substrato), por exemplo “isto é uma corda” e “uma corda” resultará como “correspondência”:

** basta adicionar “*” antes e depois da cadeia de caracteres para procurar na tabela.**

Fórmula habitual:

• vlookup(A1,B1:B10,1,0)

• cerca.vert(A1;B1:B10;1;0)

• vlookup(“*” & A1 & “*”,B1:B10;1,0)

• cerca. vert(“*” & A1 & “*”;B1:B10;1;0)

“&” é a “versão curta” para concatenate()

Pode usar a função de semelhança (pwrSIMILARITY) para comparar as cordas e obter uma correspondência percentual das duas. Pode fazer com que seja ou não sensível a maiúsculas e minúsculas. Terá de decidir que percentagem de uma correspondência é “suficientemente próxima” para as suas necessidades.

Existe uma página de referência em http://officepowerups.com/help-support/excel-function-reference/excel-text-analyzer/pwrsimilarity/ .

Mas funciona bastante bem para comparar texto na coluna A contra coluna B.