Fuzzy Matching - new version plus explanation

[al_b_cnu]

It has been a while since I originally posted my Fuzzy matching UDF’s on the board, and several variants have appeared subsequently.

I thought it time to ‘put the record straight’ & post a definitive version which contains slightly more efficient code, and better matching algorithms, so here it is.

Firstly, I must state that the Fuzzy matching algorithms are very CPU hungry, and should be used sparingly. If for instance you require to lookup a match for a string which starts with, contains or ends with a specified value, this can be performed far more efficiently using the MATCH function:

Fuzzy Examples.xls
	A	B	C	D	E
1			Starts With	Ends	Contains
2			Bill	jelen	Bill
3	Mr Bill Jelen		4	3	3
4	Bill Jelen
5	Joe Bloggs
6	Fred Smith
MATCH Example

... Continued ...

 

[al_b_cnu]

Hi mikelopez,

The VBA code comprises several functions, one of which is 'FuzzyPercent' which will return the %age match between two strings.
try this in cell C1 and copy down:

=IF(FuzzyPercent(A1,B1,4)>50%,A1,9999)

This will, of course just match the pair of strings individually in each row, NOT find the best match from a column.

 

[patbarb]

Hi Mike, a slight digression here. To prep your data to make easier matches you might get rid of the spaces:

=SUBSTITUTE(A1, " ", "")

Now, Dan Smith becomes DanSmith and matches up directly with DanSmith in B1, or whatever. This should provide you with a number of automatic matches, and at least should make matching a lot easier. Perform that operation on both columns.

Once that's done, in column C test whether the newly cleaned A1 text is in B1, incorporating your (excellent) wild card search:

=IF(NOT(ISERROR(SEARCH("*"&A1&"*",B1))), A1, "")

Then, do the exact same test in column D to see if the reverse is true, that B1 text is contained in A1:

=IF(NOT(ISERROR(SEARCH("*"&B1&"*",A1))), B1, "")

I'd show you the table I ended up with in Excel but looks like a hassle to add all the html table formatting to keep it from being a mess here! At any rate, obviously, this could all be combined into one, more elegant, formula if desired, also.

 

[mikelopez]

Do i then save it if I want the changes to go into affect?

 

[mikelopez]

I copied the code above. How do I fix it in the vba editor in excel 2003 so that my changes will stay in effect because I when I put the fuzzypercent in? I was able to get the data that I wanted, now that I closed it and repopened it up I saw the #NAME? error. What am I doing Wrong here.

 

[al_b_cnu]

I copied the code above. How do I fix it in the vba editor in excel 2003 so that my changes will stay in effect because I when I put the fuzzypercent in? I was able to get the data that I wanted, now that I closed it and repopened it up I saw the #NAME? error. What am I doing Wrong here.

Hi Mike,

You have to enable macros on opening the workbook.

 

[patbarb]

Hey Mike, I might be confusing you here. Originally, Alan was explaining how to do what you wanted via VBA. I offered a non-VBA option but I perhaps didn't make that clear. If you're preferring a VBA method, I'll just kinda watch what y'all come up with. -)

 

[mikelopez]

How do I enable macros on opening the workbook? I have never done that before.

 

[mikelopez]

When I enable macros, is this considered part of the VBA and what do I do on the VBA editor if anything?

 

[mikelopez]

I want a non-VBA option. I just want to use an IF function formula.

 

[mikelopez]

Where do I put the =SUBSTITUTE(A1, " ", "") command? In which cell?