Comparing two lists with one being inaccurate

[Ditch]

Hi,
I have a base list of ~4,000 customers and I want to compare that with a
shorter list that contains inaccurate customer names.
With accurate Customer names I would normally use the OFFSET($A$1,
MATCH(Value,Array,0),ColumnRequired) function combination. I was thinking of
using some LEFT(InaccurateName,1) iterative style of comparings first,
second, third...n, letters of the inaccurate customer names with my list to
get a % Match per string and then manually sort through them from here. eg

70% of letters (& order) match in a string = Review for manual match.

Any tips?
Thanks,
Ditch

 

[NBVC]

Hi,
I have a base list of ~4,000 customers and I want to compare that with
a
shorter list that contains inaccurate customer names.
With accurate Customer names I would normally use the OFFSET($A$1,
MATCH(Value,Array,0),ColumnRequired) function combination. I was
thinking of
using some LEFT(InaccurateName,1) iterative style of comparings first,
second, third...n, letters of the inaccurate customer names with my
list to
get a % Match per string and then manually sort through them from here.
eg
Any tips?
Thanks,
Ditch

Check here for a "Fuzzy" vlookup code that may help.

http://tinyurl.com/lj38c7

 

[Joel]

I found some c language code on the web that I converted to basic. The code
is taking a word (CompareString) and finding the best match in column B of
the worksheet.


Sub GetBestMatch()
Dim CompareString As String
Dim CompareWord As String

CompareString = "azi"
RowCount = 1
CompareWord = Range("B" & RowCount)
Diff = ComputeDistance(CompareString, CompareWord)
Best = Diff
BestWord = CompareWord

RowCount = RowCount + 1
Do While Range("B" & RowCount) <> "" And _
Best <> 0

CompareWord = Range("B" & RowCount)
Diff = ComputeDistance(CompareString, CompareWord)
If Diff < Best Then
Best = Diff
BestWord = CompareWord
End If

RowCount = RowCount + 1
Loop

MsgBox ("The Best match is : " & BestWord)
End Sub


Function ComputeDistance(s As String, t As String)

Dim distance
n = Len(s)
m = Len(t)
ReDim distance(0 To (n), 0 To (m)) ' // matrix
Cost = 0
If n = 0 Then
ComputeDistance = m
Exit Function
End If
If m = 0 Then
ComputeDistance = n
Exit Function
End If

'init1
For i = 0 To n
distance(i, 0) = i
Next i

For j = 0 To m
distance(0, j) = j
Next j

'//find min distance
For i = 1 To n

For j = 1 To m
If Mid(t, j, 1) = Mid(s, i, 1) Then
Cost = 0
Else
Cost = 1
End If

a = distance(i - 1, j) + 1
b = distance(i, j - 1) + 1
c = distance(i - 1, j - 1) + Cost
If a < b And a < c Then
distance(i, j) = a
Else
If b < c Then
distance(i, j) = b
Else
distance(i, j) = c
End If
End If
Next j
Next i

ComputeDistance = distance(n, m)

End Function

 

[Joel]

He is the webpage I used

http://www.codeproject.com/KB/recipes/improvestringsimilarity.aspx