Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Find part of name within full name field (Excel 2003 / SP2)

    I need to perform a lookup based on a find, rather than an exact cell match. In the example attached, I've taken a small sample of what I'm working with. I was give an excel sheet with a list of names from one system (sheet1). It does not have any employee ID # (EMP_ID) associated with it. I need to find that person in another system (sheet2). I have problems because some of the names contain middle initials or other statuses in the name field. It is not a clean, one-for-one match. I realize that I may have to do some manual validation, but I wanted to do most of it electronically. I've searched through the archives here for about 1/2 hour and can't find exactly what I need. Can you help?
    Attached Files Attached Files
    thanks
    christine

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts

    Re: Find part of name within full name field (Excel 2003 / SP2)

    Attached is the beginning of a solution.

    To make formulas simple I have not combined the various formulas into the one cell, which can be done but will make the formula very long, complex, and fragil to further modifications.

    In effect by using Text functions you can have Excel get you the first and last names from the second system
    Then using concatenate combine names (LAST & FIRST) for both systems.
    Sort Ascending the list names for the Vlookup table.
    Now just use Vlookup to get find and return the employee number.
    The attached has the Sort completed.

    I hope that inactive employees are not part of the issue. If so you will need to do more text functions to extract the last name only. Similar to the completed work.

    Good luck.

    Tom Duthie
    Attached Files Attached Files

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Find part of name within full name field (Excel 2003 / SP2)

    <P ID="edit" class=small>(Edited by wdwells on 07-Nov-08 20:41. Found an "OOPS" in the code.)</P>Hi Christine
    The attached should solve your problem. Note that I have placed two User Defined Functions in a standard VBA module; formulae have been added to columns J:L of Sheet2 and G:I of Sheet1. There are restrictions though:

    <UL><LI>Any notes before or after the name must be enclosed in round brackets "()"
    <LI>There is a maximum of one note before and one note after the name.
    <LI>No single character names are allowed.[/list]H.T.H.
    Attached Files Attached Files
    Regards
    Don

  4. #4
    Lounger
    Join Date
    Sep 2008
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find part of name within full name field (Exce

    Put this formula in Sheet1 G2 and drag down
    =INDEX(Sheet2!D,MATCH("*"&SUBSTITUTE($B2," ","*")&"*",Sheet2!G:G,0),1)

    It searches Sheet2's Name column for the first entry that has both the first and last name from Sheet1's User Full Name Column.

    Another approach would be to create a Custom List from Sheet1 column B and use that list to sort Sheet2 (sort on G).
    This should bring the matching rows of Sheet2 to the top of the sheet.

    I notice that only one of the names on Sheet1 have a match on Sheet2.

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Find part of name within full name field (Exce

    <hr>=INDEX(Sheet2!D,MATCH("*"&SUBSTITUTE($B2," ","*")&"*",Sheet2!G:G,0),1)
    <hr>
    Elegant formula; but note that if searching for "Marie Jones", it will accept "Marietta Jones".
    Regards
    Don

Posting Permissions

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