Matching Disparate Data Rows - Excel? Access? - Fuzzy Logic?

Posted on 2006-04-21
Last Modified: 2012-06-27
I have been given two spreadsheets -- customer lists for two companies.  My task is to come up with a list of customers that match.

1. One might say "ACME, Inc." and the other might say "acme incorporated"
2. I have the same "upper/lower/punctuation/abbreviation" issues for the address as well like:

ACME, Inc. - 123 Privat Drive
Acme - SE 123 Privat Dr.
ACME Incorporated - 17 Center St
ACME - P.O.Box 77
ACME - PO box 77
ACME - p.o. Box  77

I tried using a vlookup, but I couldn't figure out how to use fuzzy logic, like with a LIKE operator.

So I imported the spreadsheets into Access, and ran a query that says:

SELECT * FROM sheet1 s1, sheet2 s2
AND s1.addr LIKE s2.addr

And it came up with 16 records out of 60,000.  I have GOT to figure out a fuzzier way to get better results.  

I tried:
WHERE instr(s2.addr, left(s1.addr,8)) > 0
But it matched all of the P.O. BOX numbers with each other, and the ATTN: ACCOUNTS PAYABLE with each other.

So I came to Experts Exchange to see if I can find anybody with ingenuity and creativity that might spark more than 16 records out of 60,000.  In the mean time, I'll just keep trying things.
Question by:AptDev
    LVL 19

    Accepted Solution

    Access, try this

    SELECT a.*,b.*
    FROM (select sheet1.*,left([name],4) as connectname from sheet1) a INNER JOIN (select sheet2.*,left([name],4) as connectname from sheet2)  b ON a.connectname = b.connectname
    LVL 44

    Assisted Solution

    a LIKE query (LIKE in the Where clause) uses the syntax

    Field1 LIKE "*" & SomeCharacters & "*"


    LIKE Field1 LIKE field2

    A LIKE query uses 'wildcards' (* and _) to look for 'close' matches.  The * will match on ANY number of characters, the _ matches on a SINGLE character at that position in the text.

    So, if you wanted to find all the companies whose names started with acme

    Select * from Companies where CompanyName like "acme*"

    or those that had acme any where in the company name:

    Select * from Companies where CompanyName like "*acme*"

    or those that ENDED with acme (??)

    Select * from Companies where CompanyName like "*acme"

    or those that started with A..E

    Select * from Companies where CompanyName like "a__e*"

    now if you wanted to JOIN to tables with a 'fuzzy' join:

    Select * from Companies JOIN MyList on Companies.CompanyName LIKE Left(MyList.ComanyName,4) & "*"

    which would join on a MATCH on the first 4 characters in the CompanyName field in the MyList Table.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Database tuning – How to start and what to tune. This question is frequently asked by many people, both online and offline. There is no hard and fast rule-of-thumb for performance tuning, however, before beginning the tuning process one should a…
    Creating and Managing Databases with phpMyAdmin in cPanel.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now