Searching names in a column and coloring them if they match in another column

totallynotabot

New Member
Joined
Dec 24, 2019
Messages
1
Office Version
  1. 2010
Platform
  1. Windows
Hello.
Sorry if my English is bad, I am french. I would like to compare a column of name with another column of names.
I used a levenshtein code to have a percentage of similarity with the columns.
What i want to do is like if the words are matching with a > 70%, it colors the name in green
If it doesn't match at all,( <30%) it colors the name in red. And if it is really close but some letters are missing, ( >30% and <70%) then it is orange.
here is what i did with an engineer student that left the company.
I am very new to vba and don't understand completely what he did.
Here is the levenshtein function we used using with his macro.

VBA Code:
Function Levenshtein3(ByVal string1 As String, ByVal string2 As String) As Long

Dim i As Long, j As Long, string1_length As Long, string2_length As Long
Dim distance(0 To 60, 0 To 50) As Long, smStr1(1 To 60) As Long, smStr2(1 To 50) As Long
Dim min1 As Long, min2 As Long, min3 As Long, minmin As Long, MaxL As Long

string1_length = Len(string1):  string2_length = Len(string2)

distance(0, 0) = 0
For i = 1 To string1_length:    distance(i, 0) = i: smStr1(i) = Asc(Mid$(string1, i, 1)): Next
For j = 1 To string2_length:    distance(0, j) = j: smStr2(j) = Asc(Mid$(string2, j, 1)): Next
For i = 1 To string1_length
    For j = 1 To string2_length
        If smStr1(i) = smStr2(j) Then
            distance(i, j) = distance(i - 1, j - 1)
        Else
            min1 = distance(i - 1, j) + 1
            min2 = distance(i, j - 1) + 1
            min3 = distance(i - 1, j - 1) + 1
            If min2 < min1 Then
                If min2 < min3 Then minmin = min2 Else minmin = min3
            Else
                If min1 < min3 Then minmin = min1 Else minmin = min3
            End If
            distance(i, j) = minmin
        End If
    Next
Next

' Levenshtein3 va retourner un pourcentage (100%=exacte) basé sur les similaritées et longueurs etc...
MaxL = string1_length: If string2_length > MaxL Then MaxL = string2_length
Levenshtein3 = 100 - CLng((distance(string1_length, string2_length) * 100) / MaxL)

End Function

VBA Code:
Sub test()
    Workbooks("FinTer_012019_KTS_fichiers 12.xlsx").Activate
    Sheets("SanctionsFile").Select
    list_lines = Cells(Rows.Count, 1).End(xlUp).Row
    Workbooks("FinTer_012019Cie AZEC.xlsx").Activate
    Sheets("Liste nationale").Select
    terro_lines = Cells(Rows.Count, 1).End(xlUp).Row
    
    Workbooks("FinTer_012019_KTS_fichiers 12.xlsx").Activate
    Sheets("SanctionsFile").Select
    For i = 2 To list_lines
        Debug.Print i
        cur_max = 0
        cur_name = Range("D" & i).Value & " "
        If Range("E" & i).Value <> "n/a" Then
            cur_name = cur_name & Range("E" & i).Value
        End If
        For j = 2 To terro_lines
            nat_name = Workbooks("FinTer_012019Cie AZEC.xlsx").Sheets("Liste nationale").Range("B" & j).Value & " " & Workbooks("FinTer_012019Cie AZEC.xlsx").Sheets("Liste nationale").Range("C" & j).Value
            leven = Levenshtein3(Range("H" & i).Value, Range("D" & j).Value)
            cur_max = WorksheetFunction.Max(leven, cur_max)
        Next j
        If cur_max >= 70 Then
            Range("D" & i).Select
            Selection.Interior.Color = 255
            Range("E" & i).Select
            Selection.Interior.Color = 255
        End If
    Next i
    
End Sub

Thanks you for your help in advance.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,988
Office Version
  1. 2016
Platform
  1. Windows
I assume your function returns a figure between 0-100% that depends on your criteria. Assuming the function works as you want it to then I would use it in a Conditional Format statement that compares its return value to the levels you require. Let me know if you need more help and some sample data would be helpful.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,404
Messages
5,624,563
Members
416,035
Latest member
GISperson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top