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 ...

 

[mikelopez]

I tried using this formula but all I got was nothing in my C cells respectively. I copied the code above. Could that be the problem. Would the VBA code be conflicting with my IF functions.

All I am trying to do is the following using the IF function but no one's solution can seem to help me for some strange reason. I don't know why or how:

If

Column A1 has
edcoronado and B1 has edcoronado1234 what I want to appear in cell C1 is the matching text which is edcoronado.

Likewise for the following examples:

A1 B1 C1
dan smith dansmith dansmith
johnsmith1882 johnsmith johnsmith
janet smith janetsmith222 janetsmith

 

[mikelopez]

I tried using this command but it didn't work.

=IF(FuzzyPercent(A9,B9,4)>50%,A9,9999)

Does the above command have anything to do with the code that gets copied and pasted over into a Visual Basic Editor?

 

[patbarb]

Hi Mike, put the =SUBSTITUTE(A1, " ", "") in cell C1 and copy it down. Then fix column B by putting the =SUBSTITUTE(B1, " ", "") formula in cell D1, and copy that down. If you now delete columns A and B, your fixed columns will be the new A and B.

Now just do the second half of my above instructions -
>>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, "")

 

[mikelopez]

I tried doing that and this was my output in both
columns A and columns B

[TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]

<colgroup><col style="mso-width-source:userset;mso-width-alt:3527;width:74pt" width="99"> <col style="mso-width-source:userset;mso-width-alt:3783;width:80pt" width="106"> </colgroup><tbody>
[TD="width: 99, align: center"]#REF![/TD]
[TD="width: 106, align: center"]#REF![/TD]

[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]

[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]

[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]

[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]

[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]

[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]

[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF!
[/TD]

[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF!

with nothing of course in column C.
[/TD]

</tbody>

[TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]

 

[al_b_cnu]

I tried using this command but it didn't work.

=IF(FuzzyPercent(A9,B9,4)>50%,A9,9999)

Does the above command have anything to do with the code that gets copied and pasted over into a Visual Basic Editor?

Hi Mike,

In what way did it not work?

 

[patbarb]

My bad, Mike, I should have told you to convert the clean-up formulas in columns C and D 'to values' before deleting columns A and B. That is, in the first part of the process after using the Substitute formula, you're going to highlight columns C and D (by clicking on the column heads) and select Copy, then Paste/Paste Special/'Values'. Now your formulas are converted to text and you wont get that #Ref stuff when you delete columns A and B and continue on with the second half of the process.

 

[patbarb]

Hey Mike, I think I'm just creating a bit of 'thread confusion' here with my alternate way of doing this. If you have any more questions about the way I was going about it, why don't you ping me directly at patceebee(at)gmail(dotcom). --patrick

 

[mikelopez]

I tried it and it works. Thank you so much. I also found other solutions on how to do this but I will use both solutions. Again thank you.

 

[jnycm]

Hi Everyone, I'm a newbie to this board and I know I found the exact resource I've been looking for. Need to complete this over the weekend so thank everyone in advance for their assistance.

Here's what I'm trying to tackle: I have two spreadsheets, one with the reference data and the other with my data sheet. I'm trying to do a two part operation with the 2nd part relevant to this thread 1) Combine three columns of text (Excel 2010) combine the text in the first column and add a colon and space + text 2nd column and space + text in third column. 2) Then with the new column of combined text lookup in the reference spreadsheet (single column of string of text and return a corresponding alphanumeric code from a separate column. My datasheet has over 10K rows to match and obtain a code.

Please help me get started with the correct page thread. I've read through some of the pages- I never used VBA and I have used commands and macros at a very rudimentary level. Thank you!

 

[jnycm]

As and update to my question above. I've completed the part 1 concatenation and would be grateful for the fuzzy matching part. Thanks!