Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I suspect this is impossible for a computer and Excel to do, but thought I'd toss out my problem.

    I have two workbooks. In column H of one workbook are client names, and intermingled before and after any given name there might be some additional information that the creator of this file uses. In column C of the other workbook there are also client names, many of which "match" (note the quotes) those in the first file if one could peel away some of the clutter.

    For example: H1 in the first workbook might read: TC200 Jones, Thomas 1234 and somewhere in the second workbook in the C column there might be an entry that reads: Thomas Jones.

    The challenge is to come close to matching things. Since (a) Jones and (b) Thomas from the H1 cell matches the Thomas and the Jones in the other workbook somewhere in the C column, I want to flag that (maybe a new column has an "x" in it). I know I'll never do this perfectly, but is there a way to get close and possibly narrow down my options of matching?

    I almost need a function that is: =CLOSE(Workbook1!H1,Workbook2!C1:C1000) that returns a 1 or 0.

    Any ideas? -- Thanks.

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello - Can you give a couple more examples of what might appear in column H in the first workbook and what might appear in the second workbook in the C column?

    There might be a couple of solutions to the problem.

    Tim

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    C column:

    Dorothy Waters
    Getz, Michael
    Bush, David
    Kevin Hobbs

    H column:

    OC214 HOBBS, Kevin
    941 WATERS, Dorothy BofA System
    201 Getz, Mike


    These are clearly matches. Does that help at all??

    H column would have many others that don't match in the C column at all or not even close.
    For example 1084 Dominique, Lillian doesn't match anywhere -- neither the first nor the last name appear in the C column.

  4. #4
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    This should get you started. I am sure some of the steps can be consolidated.

    Your Column H data appears in Column H of First Workbook.
    Your Column C data appears in Column C of Second Workbook.
    I used Text To Columns to parse your C & H data.
    I made another column on each workbook to contain in order the First Name and Last Name.
    You see the combined names in Col. P of First Workbook and in Col. G of Second Workbook.

    In Col. H of Second Workbook it looks up it's names in First Workbook and if it finds a match, it will return the row number of First Workbook where the name was found. If no match is found in First Workbook, it returns #NA.

    Note: Michael is not the same as Mike for Getz.

    Hope that gets you started.
    Attached Files Attached Files

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Thanks. That jogged my mind a bit and I might have an approach to get what I need.

Posting Permissions

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