I have a row of 10,000 names in two corresponding columns, 10,000 in each. Each cell in Column A corresponds to the adjacent cell in Column B. I want to do a fuzzy match and get a compatibility score on all of them just with the adjacent cell. I do not want it to search entire column versus entire column, just adjacent cells, which I don't seem to be able to do with the Fuzzy Match Excel add in, ideas?
Example:
Column A: Column B: Value:
Apple Aplle 80%
Banana Banana 100%
Orange Ornge 85%
Well, I don't know about Fuzzy Match Addin but you can accomplish similar to your requirement using UDF.
Something like this based on your sample data
Function FuzzyComparision(String1 As String, String2 As String) As Double
Dim intStringLength As Integer
Dim dblScore As Double
Dim dblUnitScore As Double
Dim intCounter As Integer
intStringLength = WorksheetFunction.Max(Len(String1), Len(String2))
dblUnitScore = 1 / intStringLength
dblScore = 0#
For intCounter = 1 To intStringLength
If Mid(UCase(String1), intCounter, 1) = Mid(UCase(String2), intCounter, 1) Then
dblScore = dblScore + dblUnitScore
Else
If Len(String1) <> Len(String2) And intCounter < intStringLength Then
If Mid(UCase(String1), intCounter + 1, 1) = Mid(UCase(String2), intCounter, 1) Then
dblScore = dblScore + dblUnitScore
End If
End If
End If
Next
FuzzyComparision = dblScore
End Function
Of course the comparison can be further improvised as required.
Let me know if that helps
Edit: as Grade Bacon pointed (thanks for that observation), this does not work as expected for missing letters. I have tweaked the function a bit to do a case insensitive comparison and compensate for missing letters as in example.
Even though it works, utility depends on acceptable score deviation +/-. OP may shed some light.