We help IT Professionals succeed at work.

Fuzzy Match in Excel

jelen
jelen asked
on
I would like a macro or VBA function to calculate a percentage match between two strings.  Here are some examples:

String1: "A. Schulman"
String2: "A Schulman Co"
These strings have 11 characters in order that match, so the percentage match would be 11/12 or 92%

String1: "IBM"
String2: "I B M"
3 characters match in sequence, out of 3 characters in string 1, so this would be a 100% match.

String1: "Ask MrExcel Consulting"
String2: "MrExcel.com Consulting"
These strings have 18 characters in order in common
(M-r-E-x-c-e-l- -C-o-n-s-u-l-t-i-n-g),
so the percentage match would be 18/22 or 81%.

It is incredibly easy for our brain to look at these strings and pick out the common letters.  Getting a VBA program to do the same seems incredibly hard.

I've tried to find an existing macro that will do this, but have not had any luck. If there is one publicly available and someone can point to it, that would be great.

If not, and if anyone can offer building blocks that lead to a solution, this would be greatly appreciated.  I'll start at 200 points, even though I think the level of difficulty is way beyond that.

Thanks in advance for any ideas you might have.  

Bill Jelen
Comment
Watch Question

Commented:

Hi Jelen,

a very complex problem indeed!
I do not know what the scale is of the problem you are trying to solve, but I can point you to a company that has built software to both search and score text similarity.

http://www.searchsoftware.com/

Do not know of any smaller/freeware applications.

Greetings,
2810

Author

Commented:
Hey 2810, Thanks for the link.

I thought this one was going to be impossible to solve, but amazingly, a Colombian engineering student solved it a few hours after I posted it to another board.  The solution is quick and very effective.

I would like to post another question to give you 50 points and then delete this 200 point question.

Is that OK with you?

Commented:

Author

Commented:
A complete solution to this problem was proposed on another message board.  If you are interested in seeing the solution, it is at:
http://www.mrexcel.com/fuzzy/fuzzy.shtml

50 Points for 2810's suggestion of a commercial package have been posted at http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=msoffice&qid=20204526

Bill

Commented:
Hi Jelen,

I see my comment did not get in.
It was just saying, do not worry about the points,
I am however quite interested in this quick and effective solution.
But you read my mind I suppose, so thanks for the points and the link!

2810
 
Bill: Could we get some feedback here? Let me know what you want to do with this Q.

We're cleaning up this topic area and need all the help we can get.

Thanks
amp
Community Support Moderator

Author

Commented:
Hi Amp,

I had asked that it be deleted. I already assigned points in another question to 2810 for his helpful suggestions.
Tell you what, then. I'll PAQ it, and return your points.

Thanks for responding, Bill.

amp
community support moderator

(glad you're around 'cause I think there's gonna be some expert points coming your way...)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.