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

 

[rochak_khandelwal]

Hey, thanks for reply. I will try and let you know in case I get any success with that.
Any thoughts on which algorithm to in case of lead / trailing spaces or how to remove them? (I have tried all the trimming)

 

[al_b_cnu]

Hey, thanks for reply. I will try and let you know in case I get any success with that.
Any thoughts on which algorithm to in case of lead / trailing spaces or how to remove them? (I have tried all the trimming)

The function I posted above removes ALL characters except 0 to 9, a to z (and their equivalents). Any alphabetics are converted to lower case so ABCD will match 100% with abcd.

 

[al_b_cnu]

Hi Rochak,
In view of the number of rows that the code would have to trawl through, almost certainly a bespoke macro would be more efficient, and it could give you a progress report whilst it's computing.
Can you post a sample of the worksheet layout?

 

[rochak_khandelwal]

Hi Alan,
I tried using XL2BB but I am not able to (maybe it's a mac issue). Can I mail you or is there some workaround possible?

 

[al_b_cnu]

Hi Alan,
I tried using XL2BB but I am not able to (maybe it's a mac issue). Can I mail you

 

[al_b_cnu]

Hi Rochak
Can you upload a screenshot?

 

[richung99]

Made an account just to say that this is absolutely fantastic. Thanks for all your work and continued support Alan.

 

[duckuser7ie]

This is an amazing function, I've been using it to good success.

I have a question, though - what I need is the ability to do a fuzzy match with a condition.

I have a sheet, "Beers," that has beer names, a brewery ID, and a key for that beer:

I have another sheet with Brewery IDs and then some unrefined "Beer Names" that need to be mapped to a key in the beer sheet:

Beer names might be repeated many times across breweries, though, so I want to do the fuzzy lookup based on the BreweryID. What I tried is this:

=fuzzyvlookup(H9,FILTER(Beers!A2:E2437,Beers!B2:B2437=E9,""),5)

The FILTER() formula is correct, if I drop that into its own cell I get the array back that I expect, but I get the above #VALUE! when I try to use it.

How can I use the fuzzy vlookup under an additional condition?

Thanks!

 

[al_b_cnu]

This is an amazing function, I've been using it to good success.

I have a question, though - what I need is the ability to do a fuzzy match with a condition.

I have a sheet, "Beers," that has beer names, a brewery ID, and a key for that beer:
View attachment 51617

I have another sheet with Brewery IDs and then some unrefined "Beer Names" that need to be mapped to a key in the beer sheet:
View attachment 51618

Beer names might be repeated many times across breweries, though, so I want to do the fuzzy lookup based on the BreweryID. What I tried is this:

=fuzzyvlookup(H9,FILTER(Beers!A2:E2437,Beers!B2:B2437=E9,""),5)

The FILTER() formula is correct, if I drop that into its own cell I get the array back that I expect, but I get the above #VALUE! when I try to use it.

How can I use the fuzzy vlookup under an additional condition?

Thanks!

Hi duckuser,
I'm constantly amazed that this piece of code is still useful after over 15 years!
Instead of the Filter function, try the parameters "GroupColOffset" and "GroupValue", as described in the comments in the code, the extract of which is here:
'** The following parameters allow matching by group, and only those values
'** which are in the group specified will be considered for matching.
'** GroupColOffset: This parameter specifies the offset column which contains the group values.
'** To trigger group matching, this must be a non-zero integer
'** GroupValue : This parameter specifies the Group to be considered for matching

Hope this helps

Alan

 

[Tetonne]

where is the last release? (which post?)
would be more practical to share the file vs htm shared lol