compare formula and add data in excel

April 5, 2010 at 21:22:41
Specs: Windows XP, P4- 2.8 quad core 2gb
In MS Excel, 2003, I have two columns of data.
One Column is Employees first and last name and some names will have a middle initial. It is the list of employees that have logged into the system in the past 30 days.

The second column is the same thing of employee first and last name, but this list is exported from SAP and shows the potential number of total employees.

The same person could show up in each list differently based on their first name, example, Dave Jones, or David Jones.

The second list shows the employee ID number.

I need to make a comparison between the two columns and if there is a match, add the employee id to an adjoining column in the first list.

So, I need to get past first name differences, like Dave / David, Amy, Amanda, Barb/Barbara and if they are a match, add the Employee ID to the name in the first list.

I think I need to compare starting from the back of the last name, but I also need to copy the emp ID.

Any help with this formula is greatly appreciated

See More: compare formula and add data in excel

Report •

April 6, 2010 at 06:07:51
I think it would be best if you created separate columns for the last names and then compared those lists.

To strip off the last names from entries like these, try the formula below.

Davey Jones
Davey P. Jones

=IF(ISERROR(MID(A5,FIND(" ",A5,FIND(" ",A5)+1)+1,256)),
MID(A5,FIND(" ",A5)+1,256),
MID(A5,FIND(" ",A5,FIND(" ",A5)+1)+1,256))

You can then use VLOOKUP to look up each last name from the short list in the longer list and pull the ID from the appropriate column.

If you need help with the VLOOKUP, let us know.

BTW...this won't work if you have 2 people with the same last name since VLOOKUP will only find the first one in the list. I'm sure we could come up with a way to compare both first and last names, but if one list uses Davey and the other uses David, things could get very complicated very fast.

Report •
Related Solutions

Ask Question