Gelijksoortige tekenreeksen vergelijken in Excel

Je zou kunnen overwegen om de Microsoft Fuzzy Lookup Addin te gebruiken.

Van MS site:

Overzicht

De Fuzzy Lookup Add-In voor Excel is ontwikkeld door Microsoft Research en voert fuzzy matching uit van tekstuele gegevens in Microsoft Excel. Het kan worden gebruikt om fuzzy dubbele rijen te identificeren binnen een enkele tabel of om fuzzy join soortgelijke rijen tussen twee verschillende tabellen. De matching is bestand tegen een grote verscheidenheid van fouten, waaronder spelfouten, afkortingen, synoniemen en toegevoegde/ontbrekende gegevens. Bijvoorbeeld, het kan detecteren dat de rijen “Mr. Andrew Hill”, “Hill, Andrew R.” en “Andy Hill” allemaal verwijzen naar dezelfde onderliggende entiteit, waarbij een gelijkenis score wordt teruggegeven bij elke overeenkomst. Hoewel de standaardconfiguratie goed werkt voor een grote verscheidenheid aan tekstuele gegevens, zoals productnamen of adressen van klanten, kan de matching ook worden aangepast voor specifieke domeinen of talen.

Ik zou eens kijken of ik deze lijst (alleen Engelse sectie) kan gebruiken om de gebruikelijke afkortingen eruit te halen.

Daarnaast zou je kunnen overwegen om een functie te gebruiken die je precies vertelt hoe “dicht” twee strings bij elkaar liggen. De volgende code komt van here en met dank aan 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

Wat dit zal doen is je vertellen hoeveel inserties en deleties je moet doen in de ene string om bij de andere te komen. Ik zou proberen dit aantal laag te houden (en achternamen moeten exact zijn).

Ik heb een (lange) formule die je kunt gebruiken. Het is niet zo goed uitgewerkt als die hierboven - en werkt alleen voor achternaam, in plaats van een volledige naam - maar je zou het nuttig kunnen vinden.

Dus als je een header rij hebt en je wilt A2 vergelijken met B2, plaats dit dan in een andere cel op die rij (bijv. C2) en kopieer naar beneden tot het einde.

=IF(A2=B2, “EXACT”,IF(SUBSTITUTE(A2,“-”,“ ”)=SUBSTITUTE(B2,“-”,“ ”), “Koppelteken”,IF(LEN(A2)>LEN(B2),IF(LEN(A2)>LEN(SUBSTITUTE(A2,B2,“”)), “Gehele tekenreeks”,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(SUBSTITUTE(B2,A2,“”)), “Hele tekenreeks”,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)&“°”))))

Dit zal teruggeven:

• EXACT - als het een exacte match is
• Hyphen - als het een paar dubbelgebarsten namen is, maar de ene een koppelteken heeft en de andere een spatie
• Whole string - als de hele achternaam van de ene deel uitmaakt van de andere (bijv. als een Smith een Frans-Smith is geworden)

Daarna geeft het je een graad van 0° tot 6°, afhankelijk van het aantal punten van vergelijking tussen de twee. (d.w.z., 6° vergelijkt beter).

Zoals ik al zei, een beetje ruw en kant-en-klaar, maar hopelijk brengt het je in ongeveer het juiste bolletje-park.

Was op zoek naar iets dergelijks. Ik vond de onderstaande code. Ik hoop dat dit helpt voor de volgende gebruiker die op deze vraag komt

Geeft 91% voor Abracadabra / Abrakadabra, 75% voor Hollywood Street / Hollywood Str, 62% voor Florence / Frankrijk en 0 voor Disneyland

Ik zou zeggen dat het dicht genoeg bij wat je wilde :)

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

Deze code scant kolom a en kolom b, als het enige overeenkomst vindt in beide kolommen toont het in geel. U kunt gebruik maken van kleur filter om de uiteindelijke waarde te krijgen. Ik heb dat deel niet toegevoegd aan de code.

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

Hoewel mijn oplossing het niet mogelijk maakt om zeer verschillende strings te identificeren, is het nuttig voor gedeeltelijke overeenkomst (substring match), bijv. “dit is een string” en “een string” zal resulteren als “overeenkomend”:

voeg gewoon “*” voor en na de te zoeken string in de tabel.

Gebruikelijke formule:

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

wordt

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

“&” is de “korte versie” voor concatenate()

Je kunt de gelijkenis functie (pwrSIMILARITY) gebruiken om de strings te vergelijken en een percentage overeenkomst van de twee te krijgen. Je kunt het hoofdlettergevoelig maken of niet. Je moet zelf beslissen welk percentage van overeenkomst “dicht genoeg” is voor jouw behoeften.

Er is een referentie pagina op http://officepowerups.com/help-support/excel-function-reference/excel-text-analyzer/pwrsimilarity/ .

Maar het werkt vrij goed voor het vergelijken van tekst in kolom A met kolom B.