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

 

[earthworm]

I tired this formula but it dosent work

example if the text is John and the other text is John & co it dosent work

 

[sitewolf]

doesn't work as in John and John & Co doesn't result in a fuzzy match?
The last digit in your formula is the percentage of characters- i.e. if it was something like (a1,data!b:b,1,.85) it would be looking for a match of 85% of the characters (I used .85 because that's what I often use)

 

[shanerpark]

I just wanted to thank OP for this amazing code. I was looking for an alternative to Fuzzy Lookup (Add-on) version and using your code I'm able to use it with Macros. This is awesome! Thanks again!

 

[modiria50989]

Is it possible to create a progress indicator for this code (especially when I drag it down to do the process for all rows in a column)? I also tried to put a

MsgBox "done!" at the end of the FuzzyVLookup function, but, after running, the message pops up then I have to hit ok infinite times, seems the process doesn't stop at all even though I see the right match into the cell.​

 

[al_b_cnu]

Hi Modiria,
No, I'm afraid not, as it's a function called many times, however it IS possible if you use the FuzzyPercent function within a bespoke macro, but for that I need the layout of the data in your workbook.
It would be helpful to know what algorithm you require for the fuzzy match and also also an indication of data volumes.

 

[wisefreakz]

Hi,
Is there any update on this code? I have realized that since last version where Metaphone and Soundex were implemented, the results became very very very slow even for the primary algorithms.
Also, was the Jaccard implemented?

Thank you

 

[Romanseight18]

<center>

Page is useless. Add covers it up to the point that you cannot see everything or make heads or tails out of it!

</center>

<tbody>
</tbody>

 

[MattehWoo]

I have a query.

In the Fuzzy Lookup add-on it brings back the top 5 matches for each row with a say 80% match.

How would i duplicate that with this code?

 

[al_b_cnu]

I have a query.

In the Fuzzy Lookup add-on it brings back the top 5 matches for each row with a say 80% match.

How would i duplicate that with this code?

Hi MattehWoo, not familiar with the add-in (this was written years before the add-in was available) but have you tried the"Rank" parameter?

 

[Formula11]

Alan, the code that you have provided is quite useful.
Could you please advise which Post Number and which Algorithm contains the latest code for my situation, described as follows:
I want to output a number of row numbers, say a top ten, for the ten closest matches. The search criteria is either a single word or a number of words. The range cells typically contain 5-10 words (representing document names).

These ten rows will then be shown, while all others hidden. I can work through this part ... but need to know the post number and best algorithm to use. At the moment I'm using the Levenshtein method but am getting some unexpected results, maybe because I searched for one word and comparing to cells with 5-10 words, it may not think there is a close match. I just tried your Vlookup on the first post and got a better result.