Microsoft Office Tutorials and References
In Depth Information
FIND THE CLOSEST MATCH
FIND THE CLOSEST MATCH
Challenge: People enter data in various ways. If you ask 50 sales reps to record
a forecast for General Motors, you will ﬁ nd that there are a dozen ways to spell
and/or abbreviate the name of that customer. Combine all the forecasts from
all the sales reps, and you will have the same customer spelled a multitude of
ways. Column A in Figure 126 shows some of the different ways to enter the
names of customers whose ofﬁ cial names are listed in column D.
Part
3
Figure 126. When asked to type customer names, various employees will spell
or abbreviate them in various ways.
Setup: This is a classic problem known as the fuzzy match problem. The
problem was ﬁ rst discussed at the MrExcel message board back in fall 2001,
with the fuzzy match challenge of the month. At that time, Juan Pablo Gonzalez
wrote in with a routine to determine the percentage match between two strings.
Damon Ostrander and others later followed up.
The question arose again at the message board in late 2003. Al_B_Cnu
adapted the code from the challenge to write complete FuzzyVLOOKUP ,
FuzzyHLOOKUP , and FuzzyPercent functions. I won’t reprint the 373 lines of
code here, but you can examine them in the sample ﬁ le for this topic (download
from www.MrExcel.com/guruﬁ les.html).
Solution: The FuzzyPercent function compares text from two cells and
determines what percentage of the characters in the ﬁ rst cell are in the same
sequence in the second cell. In Figure 127, cells A2 and B2 share 11 characters
in common. Because cell A2 contains 11 characters total, 73% of the characters
match cell B2, and the FuzzyPercent is 73%. Note that if you reverse A2

Search JabSto ::

Custom Search