MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Help with VBA function checksum

  Asked By: Lucille    Date: Mar 05    Category: MS Office    Views: 2220

Have received a lot of helpful code from you folks, was hoping you
could help with this one.

I work with files that include names and often times the names are
misspelled. I'm looking for some code that would compare two names
and determine the probability that they are actually the same name.
For example, A1 has "John Smith" and B1 has "John Smyth". The code
would capitalize everything and go letter by letter. If the letters
match, it would move on until it finds two that don't match. Then at
the end it would need to determine how close the two names are. If it
was off by one letter, 90% probability, two letters, 70% probability,
and so on. The code would also need to know that some letters are
similar or next to each other on the keyboard, are often confused or
doubled, for example I and Y, double-S, and use that as part of the
rating system.

I'm guessing that a function would be best for this, i.e.

Function Compare(first name, second name)

and then I would pass both names as an argument to the function and
it would return the probability.

Any code you could provide as a starting point would be very



3 Answers Found

Answer #1    Answered By: Woodrow Jones     Answered On: Mar 05

There are some applications like Soundex that will do what you want at a
higher level. If you give Soundex Smith it will find smyth, smythe, etc.
However it does not work  at the letter by letter level and is rally a search
process rather than a comparison.

I do not know of anything which will do what you want - but there may be
something out there somewhere.

Another thing you would need to allow for is letters  out of order. Simth for

Answer #2    Answered By: Iris Sanders     Answered On: Mar 05

Had no idea what Soundex was, but after
reading your message I found an article about it on wikipeda. Then I
found a Soundex example  on j-walk.com that looks like a good start.
You've been more helpful than you realize!

Answer #3    Answered By: Olga Allen     Answered On: Mar 05

I agree that a Soundex routine is closer to what you
specified, but here is a function  that compares 2
strings and returns a percent (a double) indicating
how similar they are.

Public Function Str_Comp(st1 As String, st2 As String)
As Double
'From Analyzing Business Data with Excel, O'Reilly
Dim MtchTbl(100, 100)
Dim MyMax As Double, ThisMax As Double
Dim i As Integer, j As Integer, ii As Integer, jj
As Integer
st1$ =
st2$ =
MyMax# = 0
For i% = Len(st1$) To 1 Step -1
For j% = Len(st2$) To 1 Step -1
If Mid(st1$, i%, 1) = Mid(st2$, j%, 1)
ThisMax# = 0
For ii% = (i% + 1) To Len(st1$)
For jj% = (j% + 1) To Len(st2$)
If MtchTbl(ii%, jj%) >
ThisMax# Then
ThisMax# = MtchTbl(ii%,
End If
Next jj%
Next ii%
MtchTbl(i%, j%) = ThisMax# + 1
If (ThisMax# + 1) > ThisMax# Then
MyMax# = ThisMax# + 1
End If
End If
Next j%
Next i%
Str_Comp# = MyMax# / ((Len(st1$) + Len(st2$)) / 2)
End Function

I have had good results with this function.

Didn't find what you were looking for? Find more on Help with VBA function checksum Or get search suggestion and latest updates.