Log In
Register
Home
September 6, 2019
Offline
Hi,
Please can you help me to update attached macro.
Currently, this Code get the ratio between two text "found in same row" using Jaro-Winkler Algorithm.
| Column A | Column B | Column C |
| Given Name | Standard Name | Similarity Ratio |
| Qorvo | Qorvo Inc. | 85.18518519 |
| Monolithic Power Systems | Monolithic Power Systems, Inc. | 95.23809524 |
I need your help to update this code to compare each Given Name with All Standard Names column then get the Standard Name which have highest ratio as below steps.
1- The Given Name will be found in "G Column".
2- The Standard Names will be found in "B Column".
So the code will get the Standard Name which have highest ratio then put it in Approved Standard Name "H column" and it's ratio will be in Approved Similarity Ratio "I column"
Thanks;
Marisl
Trusted Members
Moderators
November 1, 2018
Offline
You could try something like this:
Sub GetApprovedNames()
Dim GivenNamesRange As Range
Set GivenNamesRange = Range("G3:G" & Cells(Rows.Count, "G").End(xlUp).Row)
Dim NamesIn
NamesIn = GivenNamesRange.Value
ReDim namesOut(1 To UBound(NamesIn), 1 To 2)
Dim StandardNames
StandardNames = Range("B3:B" & Cells(Rows.Count, "B").End(xlUp).Row).Value
Dim rwIn As Long
For rwIn = LBound(NamesIn) To UBound(NamesIn)
Dim currMatch As String
Dim maxMatchPerc As Double
currMatch = vbNullString
maxMatchPerc = 0
Dim rwCheck As Long
For rwCheck = LBound(StandardNames) To UBound(StandardNames)
Dim currMatchPerc As Double
currMatchPerc = JaroWink(CStr(NamesIn(rwIn, 1)), CStr(StandardNames(rwCheck, 1)))
If currMatchPerc > maxMatchPerc Then
currMatch = CStr(StandardNames(rwCheck, 1))
maxMatchPerc = currMatchPerc
If maxMatchPerc = 1 Then Exit For
End If
Next rwCheck
If Len(currMatch) <> 0 Then
namesOut(rwIn, 1) = currMatch
namesOut(rwIn, 2) = maxMatchPerc * 100
End If
Next rwIn
GivenNamesRange.Offset(, 1).Resize(, 2).Value = namesOut
End Sub
1 Guest(s)
