+ Reply to Thread
Results 1 to 5 of 5

Fuzzy Lookup

  1. #1
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Fuzzy Lookup

    I have a large list of companies, around 20,000 (all in a sinlge list). There are numerous duplicate entries in the list but with different spellings or abbreviations, e.g. you might have ABC Inc and ABC Incorporated and ABC Corp and so on and many other variations on a theme. Equally you might also have the same company in the list several times, but different branches, so in that case, it isn't a duplicate. I have the company name, the address, in some cases the town and county and the postcode, but it isn't inconceivable that postcodes could be incorrectly entered in some cases or that two different companies share a postcode.

    I need to find a way to easily and quickly identify duplicate entries in this 'single' list. A Fuzzy lookup would be perfect but I think this only works on two lists, does anyone know of anyway to achieve a similar thing on a single list?

    Many thanks

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Fuzzy Lookup

    shorrt of a macro, which i suck at, the only other way i can think of is to use a helper column to "help" in finding the duplicates.

    using something like =left(A1,3) or 4 or 5, would be a start to then using =countif() to find the dup's. you could then filter on >1, =2, =3 etc to narrow the search down a bit
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Fuzzy Lookup

    I don't mind if there is a macro solution, I just wouldn't know where to start with that.

    I guess in reality because there may be identical company names in the list, the solution needs to consider multiple columns, because there may be Vodafone in several rows, but each may be a different branch or office location. In this instance, these would be considered to be unique entries, but if there is more than one entry for Vodafone and all the address details and the postcode match, it is safe to assume this is a duplicate entry, so although using something like =left(A1,3) or 4 or 5 would be a good start, I'm not sure if it is going to give me the ability to diffrentiate between the genuine duplicates and the faux duplicates... I guess the overall approach is what I'm missing here...

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Fuzzy Lookup

    do you have a sample file for me to look at? maybe I can get you started?

    this post may be worth watching, it sounds like it is similar to what you want

    http://www.excelforum.com/excel-form...l-results.html
    Last edited by FDibbins; 12-13-2012 at 09:45 PM.

  5. #5
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Fuzzy Lookup

    I'll see if I can pull a sample together, I'll need to extract something from the main sheet, that will have to be tomorrow as it is almost 2am over here...

    Thanks for the link to the other post, certainly sounds as though we are both trying to achieve the same thing...

+ 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