Fuzzy Matching - new version plus explanation

A

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,546
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
ABCDE
1Starts WithEndsContains
2BilljelenBill
3Mr Bill Jelen433
4Bill Jelen
5Joe Bloggs
6Fred Smith
MATCH Example


... Continued ...
 
Sort by date Sort by votes
... and to vary the % Threshold:
<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;">% Threshold:</td><td style="font-weight: bold;;">50%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;;">Address1</td><td style="font-weight: bold;;">Address2</td><td style="font-weight: bold;text-align: right;;">Rank:</td><td style="font-weight: bold;text-align: center;;">1</td><td style="font-weight: bold;text-align: center;;">2</td><td style="font-weight: bold;text-align: center;;">3</td><td style="font-weight: bold;text-align: center;;">4</td><td style="font-weight: bold;text-align: center;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">1 Apple Avenue</td><td style=";">5 grapefruit Grotto</td><td style="text-align: right;;"></td><td style="text-align: center;;">5 grapefruit Grange</td><td style="text-align: center;;">5 grape grotto</td><td style="text-align: center;;">6 grapefruit grotto</td><td style="text-align: center;;">5 grapfruit gotto</td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">2 Orange Grove</td><td style=";">4 banana Boulevard</td><td style="text-align: right;;"></td><td style="text-align: center;;">4 banana road</td><td style="text-align: center;;">#N/A</td><td style="text-align: center;;">#N/A</td><td style="text-align: center;;">#N/A</td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">3 Pear Street</td><td style=";">3 Pear place</td><td style="text-align: right;;"></td><td style="text-align: center;;">#N/A</td><td style="text-align: center;;">#N/A</td><td style="text-align: center;;">#N/A</td><td style="text-align: center;;">#N/A</td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">4 banana road</td><td style=";">2 Orange Street</td><td style="text-align: right;;"></td><td style="text-align: center;;">2 Orange Grove</td><td style="text-align: center;;">#N/A</td><td style="text-align: center;;">#N/A</td><td style="text-align: center;;">#N/A</td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">5 grapefruit Grange</td><td style=";">1 Aple Avenue</td><td style="text-align: right;;"></td><td style="text-align: center;;">1 Apple Avenue</td><td style="text-align: center;;">#N/A</td><td style="text-align: center;;">#N/A</td><td style="text-align: center;;">#N/A</td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">5 grapfruit gotto</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">6 grapefruit grotto</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">5 grape grotto</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">77 grap grt</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D3</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">$B3,$A:$A,1,$D$1,D$2,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">$B3,$A:$A,1,$D$1,E$2,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F3</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">$B3,$A:$A,1,$D$1,F$2,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G3</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">$B3,$A:$A,1,$D$1,G$2,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H3</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">$B3,$A:$A,1,$D$1,H$2,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D4</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">$B4,$A:$A,1,$D$1,D$2,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E4</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">$B4,$A:$A,1,$D$1,E$2,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F4</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">$B4,$A:$A,1,$D$1,F$2,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G4</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">$B4,$A:$A,1,$D$1,G$2,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H4</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">$B4,$A:$A,1,$D$1,H$2,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D5</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">$B5,$A:$A,1,$D$1,D$2,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E5</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">$B5,$A:$A,1,$D$1,E$2,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F5</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">$B5,$A:$A,1,$D$1,F$2,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G5</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">$B5,$A:$A,1,$D$1,G$2,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H5</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">$B5,$A:$A,1,$D$1,H$2,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D6</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">$B6,$A:$A,1,$D$1,D$2,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E6</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">$B6,$A:$A,1,$D$1,E$2,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F6</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">$B6,$A:$A,1,$D$1,F$2,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G6</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">$B6,$A:$A,1,$D$1,G$2,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H6</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">$B6,$A:$A,1,$D$1,H$2,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D7</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">$B7,$A:$A,1,$D$1,D$2,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E7</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">$B7,$A:$A,1,$D$1,E$2,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F7</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">$B7,$A:$A,1,$D$1,F$2,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G7</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">$B7,$A:$A,1,$D$1,G$2,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H7</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">$B7,$A:$A,1,$D$1,H$2,2</font>)</td></tr></tbody></table></td></tr></table><br />
 
Upvote 0

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Do you just copy the cells to the right for the number of results you want within the list?? So if I want 15 results returned (if there were that many) I'd copy the formula across 15 times?

If so, it appears it will do exactly what I'm looking to do.
 
Upvote 0
Hi, yes, that's exactly right.
If you wished, you can also call Fuzzypercent against the lookup value & returned value to show the actual % match, but ensure you set the algorithm to the value 2.
 
Upvote 0
Not on laptop at the moment,so cannot test anything, but perhaps you could transpose lookup values into columns of, (e.g. into row 2) either by formula (using "Index" function) or the built in paste-and-transpose and then use fuzzyvlookup on the transposed entries.
The formula approach, assuming the first formula was in column D, would be something like
=index($b:$b,column()-3,1)
This would (should?) return the entry in B3.
Copy formula to the right & place suitably modified fuzzyvlookup formulae below.
 
Upvote 0
mybludrunsorange said:
Is there a built-in way to get the results in columnar format? Otherwise, this is perfect.
Click to expand...

Hi, At my laptop now :)

How about this:
<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="font-weight: bold;;">% Threshold:</td><td style="font-weight: bold;;">50%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;;">Address1</td><td style="font-weight: bold;;">Address2</td><td style="text-align: center;;">Rank</td><td style=";">5 grapefruit Grotto</td><td style=";">4 banana Boulevard</td><td style=";">3 Pear place</td><td style=";">2 Orange Street</td><td style=";">1 Aple Avenue</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">1 Apple Avenue</td><td style=";">5 grapefruit Grotto</td><td style="text-align: center;;">1</td><td style=";">5 grapefruit Grange</td><td style=";">4 banana road</td><td style="text-align: right;;">#N/A</td><td style=";">2 Orange Grove</td><td style=";">1 Apple Avenue</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">2 Orange Grove</td><td style=";">4 banana Boulevard</td><td style="text-align: center;;">2</td><td style=";">5 grape grotto</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">3 Pear Street</td><td style=";">3 Pear place</td><td style="text-align: center;;">3</td><td style=";">6 grapefruit grotto</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">4 banana road</td><td style=";">2 Orange Street</td><td style="text-align: center;;">4</td><td style=";">5 grapfruit gotto</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">5 grapefruit Grange</td><td style=";">1 Aple Avenue</td><td style="text-align: center;;">5</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">5 grapfruit gotto</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">6 grapefruit grotto</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">5 grape grotto</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">77 grap grt</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=INDEX(<font color="Blue">$B:$B,COLUMN(<font color="Red"></font>)-1,1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=INDEX(<font color="Blue">$B:$B,COLUMN(<font color="Red"></font>)-1,1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=INDEX(<font color="Blue">$B:$B,COLUMN(<font color="Red"></font>)-1,1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G2</th><td style="text-align:left">=INDEX(<font color="Blue">$B:$B,COLUMN(<font color="Red"></font>)-1,1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H2</th><td style="text-align:left">=INDEX(<font color="Blue">$B:$B,COLUMN(<font color="Red"></font>)-1,1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D3</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">D$2,$A:$A,1,$C$1,$C3,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">E$2,$A:$A,1,$C$1,$C3,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F3</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">F$2,$A:$A,1,$C$1,$C3,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G3</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">G$2,$A:$A,1,$C$1,$C3,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H3</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">H$2,$A:$A,1,$C$1,$C3,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D4</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">D$2,$A:$A,1,$C$1,$C4,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E4</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">E$2,$A:$A,1,$C$1,$C4,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F4</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">F$2,$A:$A,1,$C$1,$C4,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G4</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">G$2,$A:$A,1,$C$1,$C4,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H4</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">H$2,$A:$A,1,$C$1,$C4,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D5</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">D$2,$A:$A,1,$C$1,$C5,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E5</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">E$2,$A:$A,1,$C$1,$C5,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F5</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">F$2,$A:$A,1,$C$1,$C5,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G5</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">G$2,$A:$A,1,$C$1,$C5,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H5</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">H$2,$A:$A,1,$C$1,$C5,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D6</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">D$2,$A:$A,1,$C$1,$C6,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E6</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">E$2,$A:$A,1,$C$1,$C6,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F6</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">F$2,$A:$A,1,$C$1,$C6,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G6</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">G$2,$A:$A,1,$C$1,$C6,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H6</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">H$2,$A:$A,1,$C$1,$C6,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D7</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">D$2,$A:$A,1,$C$1,$C7,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E7</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">E$2,$A:$A,1,$C$1,$C7,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F7</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">F$2,$A:$A,1,$C$1,$C7,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G7</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">G$2,$A:$A,1,$C$1,$C7,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H7</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">H$2,$A:$A,1,$C$1,$C7,2</font>)</td></tr></tbody></table></td></tr></table><br />
 
Upvote 0
You've been a huge help! Is there a way to iterate through each address in column a, get the results and dump it into a 2nd sheet... then do it again for the next address in column b and continue until you reach the end of column b...

By the way, do you have a way to accept donations?
 
Upvote 0
Hi,

Try this macro, You need to create a new module, place the code in that and KEEP THE FUZZYLOOKUP code.
Note the constant declarations at the top of the module, in particular 'mlDataStartRow'
Code: 
Option Explicit
Const msInputSheet As String = "Sheet1"
Const msOutputSheet As String = "Sheet2"

Const msngThresholdValue As Single = 0.5    'Threshold value of 50%

Const msLookupTableColumn As String = "A"   'Column A for lookup table
Const msLookupValueColumn As String = "B"   'Column B for lookup values

Const mlDataStartRow As Long = 3            'start of data in input & output sheet

Sub GetAddresses()
Dim iResultsColumn As Integer

Dim lLookupValuesRow As Long
Dim lLookupTableRow As Long
Dim lResultsRow As Long

Dim sCurrentLookupValue As String
Dim sCurrentTableValue As String

Dim sngCurrentPercent As Single

Dim vaLookupTable As Variant
Dim vaLookupValues As Variant
Dim vaResults As Variant

Dim wsInput As Worksheet
Dim wsOutput As Worksheet

Set wsInput = Sheets(msInputSheet)
Set wsOutput = Sheets(msOutputSheet)

With wsInput
    vaLookupTable = Intersect(.UsedRange, .Columns(msLookupTableColumn))
    vaLookupValues = Intersect(.UsedRange, .Columns(msLookupValueColumn))
End With

wsOutput.UsedRange.ClearContents
iResultsColumn = 0

For lLookupValuesRow = mlDataStartRow To UBound(vaLookupValues, 1)
    ReDim vaResults(1 To 1, 1 To 1)
    lResultsRow = 1
    vaResults(1, 1) = vaLookupValues(lLookupValuesRow, 1)
    sCurrentLookupValue = WorksheetFunction.Trim(LCase$(CStr(vaLookupValues(lLookupValuesRow, 1))))
    For lLookupTableRow = mlDataStartRow To UBound(vaLookupTable, 1)
        sCurrentTableValue = WorksheetFunction.Trim(LCase$(CStr(vaLookupTable(lLookupTableRow, 1))))
        sngCurrentPercent = FuzzyPercent(String1:=sCurrentLookupValue, _
                                         String2:=sCurrentTableValue, _
                                         Algorithm:=2, _
                                         Normalised:=True)
        If sngCurrentPercent >= msngThresholdValue Then
            lResultsRow = lResultsRow + 1
            ReDim Preserve vaResults(1 To 1, 1 To lResultsRow)
            vaResults(1, lResultsRow) = vaLookupTable(lLookupTableRow, 1)
        End If
    Next lLookupTableRow
    iResultsColumn = iResultsColumn + 1
    With wsOutput
        .Range(.Cells(mlDataStartRow, iResultsColumn).Address).Resize(UBound(vaResults, 2), 1).Value = WorksheetFunction.Transpose(vaResults)
    End With
Next lLookupValuesRow

End Sub

Input:
<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;;">Address1</td><td style="font-weight: bold;;">Address2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">1 Apple Avenue</td><td style=";">5 grapefruit Grotto</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">2 Orange Grove</td><td style=";">4 banana Boulevard</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">3 Pear Street</td><td style=";">3 Pear place</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">4 banana road</td><td style=";">2 Orange Street</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">5 grapefruit Grange</td><td style=";">1 Aple Avenue</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">5 grapfruit gotto</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">6 grapefruit grotto</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">5 grape grotto</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">77 grap grt</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />
Results:
<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">5 grapefruit Grotto</td><td style=";">4 banana Boulevard</td><td style=";">3 Pear place</td><td style=";">2 Orange Street</td><td style=";">1 Aple Avenue</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">5 grapefruit Grange</td><td style=";">4 banana road</td><td style="text-align: right;;"></td><td style=";">2 Orange Grove</td><td style=";">1 Apple Avenue</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">5 grapfruit gotto</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">6 grapefruit grotto</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">5 grape grotto</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br />
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Fuzzy Match code
Replies
1
Views
2K
gelu
G
J
  • Question Question
Formula(s) to match cell value
Replies
3
Views
2K
Aladin Akyurek
Aladin Akyurek
E
  • Question Question
Fuzzy Excel problem
Replies
1
Views
5K
al_b_cnu
A
A
  • Question Question
How to Consolidate large Qty of unique numbers w/ matching criteria into fewer numbers
Replies
3
Views
1K
amerikanzero
A
A
  • Question Question
Form Automation: Populating a ComboBox during a dynamic userform build
2
Replies
12
Views
2K
Atroxell
A

Forum statistics

Threads
1,223,277
Messages
6,171,156
Members
452,385
Latest member
Dottj

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top