How to use Fuzzy Lookup Add-In for Excel

Owned by Genhan Chen
Last updated: Jan 24, 2017

During our OEA data migration, removing duplicated vendor records are required before we import the vendor table from OEA FileMaker database into our CPMS Oracle database.

However, finding out those duplicates cannot be done fully automatically, since the records in those two tables were entered by different people. A tiny difference, such as a period, might cause a difference between two vendor records. What we need to know is the similarity between two records, and then determine if a record is a duplicate and needs to be removed manually.

I have introduced two ways to calculate the similarity between two datasets: https://csjdpw.atlassian.net/wiki/x/EwDR

Here, I am going to demonstrate how to use Fuzzy Lookup Add-In for Excel to compare two tables in the attached sample data: Sample_FuzzyLookup.xlsx

Step-by-step guide


  1. Download and install Fuzzy Lookup Add-In for Excel from https://www.microsoft.com/en-us/download/details.aspx?id=15011
  2. Download and open Sample_FuzzyLookup.xlsx, and a new item Fuzzy Lookup appears on the menu bar
  3. Convert data in OEA and CPMS spreadsheets into tables by clicking INSERT → Table on the menu bar
  4. Open Fuzzy Lookup, and select the right tables, columns and configuration. See the settings below: 

     
     
  5. Click Go to run Fuzzy Lookup in a new spreadsheet
  6. Sort and format the results

Methods to find the similarity between 2 datasets