+ Reply to Thread
Results 1 to 3 of 3

Finding duplicates and approximate duplicates on two separate lists of accounts

  1. #1
    Registered User
    Join Date
    08-28-2012
    Location
    Seattle
    MS-Off Ver
    Excel 2010
    Posts
    11

    Finding duplicates and approximate duplicates on two separate lists of accounts

    Does anyone happen to know if there is a way to edit this formula so it would catch non-exact matches as well??

    =IF(ISERROR(MATCH(A1,Sheet2!$A$1:$A$100,0)),"Unique","Duplicate")

    I'm comparing two lists of companies, one list is 1200 rows, the one I'm comparing it against has over 100,000 rows. Some of the names don't completely match but still need to be flagged if they are approximate.

    Example would be "Amazon.com" on one list and "Amazon.com headquarters" or "Amazon LLC" on another.

    Thanks!

  2. #2
    Registered User
    Join Date
    08-28-2012
    Location
    Seattle
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Finding duplicates and approximate duplicates on two separate lists of accounts

    Any takers?

    I've had some luck with using an approximate lookup based on the first several characters in the account name but its still not completely accurate..

    Any ideas on something that would work better than this?:

    =IF(ISNA(VLOOKUP(LEFT([@[USACCT_NAME]],7) & "*",ALLACCOUNTS!D2:D131506,1,FALSE)),"No Match","Match")

    thanks!

  3. #3
    Registered User
    Join Date
    12-12-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Finding duplicates and approximate duplicates on two separate lists of accounts

    Super late reply, but just came across this post.
    I would use the Fuzzy Lookup Add-in for Excel - http://www.microsoft.com/en-au/downl....aspx?id=15011
    It's amazing... allows you to match tables and finds exact matches as well as "fuzzy" matches. For non-exact matches it calculates the similarity rating and allows you to select a minimum threshold. It would be perfect for the example you've given.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1