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

 

[ttawd]

I have a problem where this fuzzymatching might help. I have 2 excel sheets of 10,000+ records each. One of the common elements is NAME. I need to compare these two sheets, and when the computer finds a close to 100% match (say 80%), then to combine the 2 records into a 3rd sheet. Ideally, at the end of this comparison, I will have a 3rd sheet with just several hundret names and all the information from that row from sheet 1 and 2. Then I can eyeball these few records and see if the computer was right. Can fuzzymatching be used for such a problem? Thanks for the help.

 

[al_b_cnu]

Hi ttawd, Welcome to the board!

FuzzyVLookup is merely a fuzzy replacement for VLookup, so if you can envisage how you would set out the sheets with VLookup, you can do something similar with FuzzyVLookup.

Having said that, I dont know the format of your sheets, but from your description, it sounds like you need a bespoke macro (which may possibly utilise the FuzzyPercent function).

In either case, you have to define what form of fuzzy matching you require - there are 3 algorithms available:
Algorithm 1: a fuzzy match for minor misspellings
Algorithm 2: a fuzzy match for words / phrases not in the same order (e.g. matches 'Bill Jelen' with 'Jelen Bill')
Algorithm 3 a compromise match of the above.

 

[al_b_cnu]

Hi ttawd, can you adapt this:

Input sheet 1:

Excel Workbook
	A	B	C	D	E
1	Name	DataA 1	DataA 2	DataA 3	DataA 4
2	Lndon	B$B$2	B$C$2	B$B$2	B$C$2
3	Mnchestr	B$B$3	B$C$3	B$B$3	B$C$3
4	Lvrpl	B$B$4	B$C$4	B$B$4	B$C$4
5	Glsgw	B$B$5	B$C$5	B$B$5	B$C$5
6	Hll	B$B$6	B$C$6	B$B$6	B$C$6
7	Yrk	B$B$7	B$C$7	B$B$7	B$C$7
8	Brmnghm	B$B$8	B$C$8	B$B$8	B$C$8
Sheet1

Continued ...

 

[al_b_cnu]

... continued

Input sheet 2:

Excel Workbook
	A	B	C
1	Name	DataB 1	DataB 2
2	London	B$B$2	B$C$2
3	Manchester	B$B$3	B$C$3
4	Liverpool	B$B$4	B$C$4
5	Glasgow	B$B$5	B$C$5
6	Hull	B$B$6	B$C$6
7	York	B$B$7	B$C$7
8	Birmingham	B$B$8	B$C$8
Sheet2

 

[al_b_cnu]

Output sheet:

Excel Workbook
	A	B	C	D	E	F	G
1	Lookup Row	% Match	Data A 1	Data A 2	Data A 3	Data B 1	Data B 2
2	2	73.33%
3	3	67.86%
4	4	73.33%
5	5	80.00%	B$B$5	B$C$5	B$B$5	B$B$5	B$C$5
6	6	75.00%
7	7	75.00%
8	8	69.57%
9	#N/A	0.00%
10	#N/A	0.00%
11	#N/A	0.00%
12	#N/A	0.00%
Sheet3

 

[Randombard]

Hi all,

I am not sure if this has already been posted but

If you are using the original example sheets format then this works great for just giving the result with the highest % match.

{=IF(MIN(IF(G3:I3=MAX(G3:I3),COLUMN(G3:I3),""))=7,C3,IF(MIN(IF(G3:I3=MAX(G3:I3),COLUMN(G3:I3),""))=8,D3,E3))}

I am curiose about using this kind of format to froduce a fuzzy match array formula all in one cell so that it takes up less data ink?

Any ideas.

 

[ttawd]

Thanks Alan for the solution to my problem. I made your code work for my application and my sheets that I was comparing had 30,000 rows each, so it took about 24 hours to run. Now, I have a new file with 30,000 rows, and with meaningful data in about 5% of the rows with values > 0.8. The computer will not let me save the file due to insuficcient resources.

Basically this is what I need:
IF B1 to B30000 >.8 THEN copy the VALUES of the entire row to a new worksheet in a new file.

Hopefully whatever macro I run, will not disturb my original sheet, because I cannot wait another 24 hours to generate the results.

Thanks for all your help. I can tell that the Computer Scientists/Engineers that are on this forum are really knowleageable. Your help made an impossible problem possible. Thanks again.

-Mike-
ttawd

 

[al_b_cnu]

Hi Mike, place this macro in the original workbook. It will create a new w/book & write the appropriate rows to it.

The macro assumes that the data is in Sheet3:

Sub CopyRows()
Dim dblCurValue As Double
Dim lRow As Long, lRowEnd As Long, lCounter As Long
Dim rCur As Range
Dim wbNew As Workbook
Dim wsInput As Worksheet, wsOutput As Worksheet

Set wsInput = Sheets("Sheet3")
lRowEnd = wsInput.Cells(Rows.Count, "B").End(xlUp).Row

Set wbNew = Workbooks.Add
Set wsOutput = wbNew.Sheets(1)

lRow = 1
lCounter = 1000
For Each rCur In wsInput.Range("B2:B" & lRowEnd)
    lCounter = lCounter - 1
    If lCounter < 1 Then
        Application.StatusBar = "Processing row " & rCur.Row & " of " & lRowEnd
        lCounter = 1000
    End If
    
    dblCurValue = 0
    On Error Resume Next
    dblCurValue = Val(rCur.Value)
    On Error GoTo 0
    If dblCurValue > 0.8 Then
        lRow = lRow + 1
        wsOutput.Rows(lRow).Value = wsInput.Rows(rCur.Row).Value
    End If
Next rCur
Application.StatusBar = False
End Sub

 

[ttawd]

Alan,

Wow! Thanks a million. I just applied your macro to my original results (which I obtained with your help this morning after more than a full day of processing for my computer). It took less than 1 minute for this latest COPYROWS macro to run through my 30,000 rows of data and pull out 1700 records. Hurray! You are awesome. You saved me a a day of cut and paste.

-Mike-

 

[srkodees]

Hi,

Ur code works fine.but i need some wht clear ..For ex i have list of names in a table(table array).lookup value of the word ,length is 7.it will search in table array and find the closest match.whether the closest match word of the lenght is 6 or 7 or 8 or 9.it would print the result.But i need a exact lenghtwise match..

table array lookup value result

George Harrison george harros ?
george harris

our lookup value is george harros.instead of harris, i hve mispelled harros.if any chance to get correct answer by using exact length match?