Comparing 2 strings - 70% the same

[Ragnar1211]

I am attempting to compare two different strings and find out how similar they are. I really am not sure how to approach it --- i want to compare a string like this:

RETAIL PURCHASE 805011644964;Point Of Sale or Debit Card

and this

RETAIL PURCHASE 837001001025;Point Of Sale or Debit Card

and say they are similar enough - like 80% or something like that.

Is there any vba magic out there that does this?

 

[Sal Paradise]

It entirely depends on how you want to compare it.

If your entries can be logically separated into groups and then analyzed by section, then it's probably possible. If you just want a blanket similarity score then you'd have to decide how you want the algorithm to work.

For instance, if the exact same entry is written in reverse order, is that similar? All the characters are the same, but the order will only be similar for the middle. Or if 'Retail Purchase' is written as 'Purchase Retail' -- the words are totally dissimilar if you look word-by-word, but they are probably the same thing in reality. If you use the same logic for numbers, however, you would end up saying account number 12345678 is the same as 56781234, which is probably not the case.

 

[Ragnar1211]

I think as long as the words are the same I could do the following with an array:

1) split the string I want to lookup into pieces - the words
2) use instr to compare
3) create a score of the results - eg if 5/6 words are found then etc.

Does that make sense?

 

[Sal Paradise]

Yes, it does. However, if you have less than 6 words, or a word is misspelled, then you may run into problems. I don't know what your data looks like or how you get it.

 

[PA HS Teacher]

You might want to have a look at Alan's famous "Fuzzy Match" function.

http://www.mrexcel.com/forum/showthread.php?p=955137#974873

 

[Ragnar1211]

Thanks! That was what I had in mind.