Vlookup similar but not maching records 1

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.

Vlookup similar but not maching records 4

and we have another set of refined company names (without any similar duplication). This is mandatory to have

Vlookup similar but not maching records 5

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

Vlookup similar but not maching records 2This page has everything you want to know about how to install this add in your Excel

  1. It has the download link
  2. 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

Vlookup similar but not maching records 3

Now convert your data into Tables

  1. Use the Shortcut Ctrl + T to covert both the data sets into a table
  2. Give your tables a suitable name from the Design Tab (appears when you click on any cell inside the table)

Vlookup similar but not maching records 6

 

Let’s Start Fuzzy Lookup

Click on Fuzzy Lookup. It will display a settings pop up

Vlookup similar but not maching records 7

 

Follow the steps

Vlookup similar but not maching records 8

  1. Select the Right Settings
  2. Click on the Relationship Button between ‘Left Column’ & ‘Right Column’
  3. Tick the items you want in the Output Columns
  4. Set the Similarity Threshold
    1. The higher the threshold, the more strictly will excel match the records
    2. The lesser the threshold, the more loosely will excel match the records
  5. Before clicking on Go. Choose the cell where you want to place the new table. Click on GO
  6. Done!

 

Let’s see the Results

Vlookup similar but not maching records 9

  1. These are the similar customer names
  2. Customer ID has been pulled as it is from the ‘SimilarName’ Table
  3. 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
  4. Similarity Score – The higher the score, the higher the chance that excel has given the correct match

Download the Finished Fuzzy Lookup File

 

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 Ulimate Vlookup Guide 1

The guide contains tricks and tips beyond the conventional VLOOKUP and it’s free – DOWNLOAD THE ULTIMATE VLOOKUP GUIDE (pdf)

 

More on VLookup

  1. Comprehensive Guide on Vlookup + 5 Crazy Tricks
  2. Learn how to do Picture Vlookup
  3. 3 Common Mistakes while writing Vlookup

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI