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

 

[QuietRiot]

also you don't 'run' anything persay

they're functions

=fuzzyvlookup($B3,$A:$A,1,,,C$2)

that is from the example above on how he uses fuzzyvlookup

 

[Grodek]

Confused

I have followed the directions on how to add this VBA in. I copied the data sets and when I try to use the formulas, I get the standard #VALUE! output.

I have basically no experience with VBA. What could I be doing wrong?

Thanks a lot!

 

[al_b_cnu]

Hi,

Can you post an example using Colo's HTMLMaker (see bottom of this webpage for a link for the download)

 

[Grodek]

Sorry it took so long

Sorry, how do you get that to work - to post my sheet I mean?

Can anyone get this VBA to work and save and e-mail it?[/code]

 

[al_b_cnu]

Hi Grodek,

Email me your s/sheet as is, tell me what you want doing & I'll have a look.

Alan

 

[arkusM]

Alan,

I got the #Value! error as well.
Here is what the formula I entered looks like:

Fuzzy logic Example.xls
	A	B	C	D	E	F
1
2			1	2
3	PaulMcCartney	GeorgeBush	#VALUE!	#VALUE!	#VALUE!
4	GeorgeHarris	Gargehrs	#VALUE!	#VALUE!	#VALUE!
5	RingoStarr	Starr,Ringo	#VALUE!	#VALUE!	#VALUE!
6	JohnLennon	Bush,George	#VALUE!	#VALUE!	#VALUE!
7	TonyBlair
8	GeorgeClooney
9	ChuckNorris
10	ClaudeVanDamme
11	BruceLee
12
13
Sheet1

 

[Diffy]

Sticky?

 

[GlennUK]

Well, you forgot to specify the Index argument ( 3rd argument ), which is wrong. The only value that could be in a 1 column table is 1 . What are you trying to specify in cells C2 and D2 ... Rank? If so, then you can't have a blank in E2!

 

[unc2plo]

I know this post is super old, but I am hoping that someone still gets the email notification and can help me understand what I have gotten myself into. I know zero VBA, so I am probably missing something simple.

I am trying to compare two lists of retail products based on the name only. My hope is to find the two product that are the closest based on the similarity of the texts. If one item has the words "apple cider" in it I would like to know how many others have "apple cider" (or something close to it).

I pasted the fuzzyvlookup VBA into a new module. It seems to be OK, but when I enter the data & formulas I get a #NAME result.

Do I need to name ranges, or customize any of the VBA in any way?

test match with formula.xls
	A	B	C	D	E	F	G	H
1
2			FuzzyPercent-Algorithm
3	Vendor1	Vendor2	1	2	3	1	2	3
4	#2CoffeeFilters	AppleCider	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?
5	#4CoffeeFilters	100%PruneJuice	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?
6	10-12CupCoffeeFitlers	AllPurposeFlour	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?
7	AllPurposeFlour	AngelFoodCake	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?
8	AluminumFoil-25ft.	AnimalCookies	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?
9	AluminumFoil-37.5ft	AppleJuiceConcentrate-Froz	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?
Sheet1

 

[arkusM]

unc2plo

I posted in this site months ago, and I did not get a notification that you posted today. I can't help you but maybe you should send a message to AL__B_CNU though the board for help.

Cheers