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

Topic

New Reply

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?

Reply | Quote

Replies

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

Reply | Quote

(Edited by wdwells on 07-Nov-08 20:41. Found an “OOPS” in the code.)

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:

• Any notes before or after the name must be enclosed in round brackets “()”
• There is a maximum of one note before and one note after the name.
• No single character names are allowed.
  [/list]H.T.H.

Reply | Quote

Put this formula in Sheet1 G2 and drag down
=INDEX(Sheet2!D: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.

Reply | Quote

[indent]

---

=INDEX(Sheet2!D:D,MATCH(“*”&SUBSTITUTE($B2,” “,”*”)&”*”,Sheet2!G:G,0),1)

---

[/indent] Elegant formula; but note that if searching for “Marie Jones”, it will accept “Marietta Jones”.

Reply | Quote