Rare but a very relevant problem! Isn’t it? A quick heads up on how does it arise?
Imagine for a moment that the ERP at Peter Brothers is not very robust, every time a customer comes in (even if the same customer) the MIS guy makes a New Record.
His whims led to too many similar company names and now it is impossible for Vlookup to match similar company names.
I was asked this question by a participant in a training session about 2 years back, knowing that there was no straight answer to that I kept thinking of a Macro (VBA coding) based solution, a few days later to my surprise I found that Microsoft had already thought about it and rendered a beautiful solution. I am writing about it today (better late than never 😛 )
Without reinventing the wheel let’s see what Microsoft has to offer? Ready for it?
Here is the Data
Here is a list of 70+ records with similar but not matching company names. Notice that for each similar record a separate customer ID has been generated.
and we have another set of refined company names (without any similar duplication). This is mandatory to have
Download this data. Our objective is to delete the similar company names and retain just one with a unique Customer ID
Here Comes Fuzzy Lookup by Microsoft
- It has the download link
- And instructions and other details that you may need along the way
Fuzzy Lookup Download Link. Take a moment to install Fuzzy Lookup Add In
This is how it appears in Excel after installation
Now convert your data into Tables
- Use the Shortcut Ctrl + T to covert both the data sets into a table
- Give your tables a suitable name from the Design Tab (appears when you click on any cell inside the table)
Let’s Start Fuzzy Lookup
Click on Fuzzy Lookup. It will display a settings pop up
Follow the steps
- Select the Right Settings
- Click on the Relationship Button between ‘Left Column’ & ‘Right Column’
- Tick the items you want in the Output Columns
- Set the Similarity Threshold
- The higher the threshold, the more strictly will excel match the records
- The lesser the threshold, the more loosely will excel match the records
- Before clicking on Go. Choose the cell where you want to place the new table. Click on GO
Let’s see the Results
- These are the similar customer names
- Customer ID has been pulled as it is from the ‘SimilarName’ Table
- Customer Name (not similar ones) have been added in front of each similar customer name
- The empty records could not be matched
- May be reducing the similarity threshold can help
- This is what we wanted. Now we can easily filter out the similar ones
- Similarity Score – The higher the score, the higher the chance that excel has given the correct match
How did you find Fuzzy Lookup?
Put down your comments below and also share if you have solved a complex problem with a simple add in
Download The Ultimate VLOOKUP Guide
The guide contains tricks and tips beyond the conventional VLOOKUP and it’s free – DOWNLOAD THE ULTIMATE VLOOKUP GUIDE (pdf)
More on VLookup
- Comprehensive Guide on Vlookup + 5 Crazy Tricks
- Learn how to do Picture Vlookup
- 3 Common Mistakes while writing Vlookup