+ Reply to Thread
Results 1 to 8 of 8

Match partial cells in a single EXCEL column?

  1. #1
    Registered User
    Join Date
    03-19-2010
    Location
    VT, USA
    MS-Off Ver
    Excel 2010
    Posts
    32

    Match partial cells in a single EXCEL column?

    I have a list of over 11,000 company names - and the data is not consistent. I'm trying to identify partial matches in a single column. For example, ABC Company and ABCCompany.

    Is there a way to easily identify partial matches in a single excel column?

  2. #2
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Match partial cells in a single EXCEL column?

    Will ABC be first or could it be CompanyABC?
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Match partial cells in a single EXCEL column?

    Hi,

    The topic might fall under "Fuzzy Logic" to match those things that aren't exact matches. You can search for VBA code to do it or look at http://www.mrexcel.com/forum/showthr...=955137#974873 for a quick example.

    If you want a better answer you need to supply a sample workbook with some examples. Also, do you have a full list of companies to compare to?
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Match partial cells in a single EXCEL column?

    Hi MP,

    Not particularly... depends on ABCCompany or CompanyABC. The former can be grabbed using vlookup with a wildcard. The second is definately fuzzy I prefer using the levenstein approach (slow but more accurate) than other methods. Currently I am working on a matching algorithm using a scripting dictionary for speed to weed out the possiblities and then levenstein for a more accurate result. It's for identifying similar titles/ paragrahs of texts in a large database.

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Match partial cells in a single EXCEL column?

    Hi,

    I guess we won't know what works for the OP without some feedback. I'd like to see some sample data to see how off the matches are. Good luck with Levenstien at http://en.wikipedia.org/wiki/Levenshtein_distance

  6. #6
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Match partial cells in a single EXCEL column?

    shg already coded it before. Check this post using a UDF. Then of course there is alan's code that you posted the link to and now MS has a add-in for fuzzy vlookup also. Unfortunately it only works for Excel 2010 and it's pretty slow and it requries two distinct tables to work.

  7. #7
    Registered User
    Join Date
    03-19-2010
    Location
    VT, USA
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: Match partial cells in a single EXCEL column?

    The list is sorted in alphabetical order, so ABC would appear first.

  8. #8
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Match partial cells in a single EXCEL column?

    No, that's not what we are asking. What we mean is could the name of the company be backwards. Could you show us an example?

+ 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