Returning matched employee number from a different sheet

[Diddy]

Hi everyone,

I've got two sheets containing data on the same group of people.
Data and Results.
The personnel info in Data is more complete than in Results
Data has Forename, Middle Name, Surname, DOB, Gender and most importantly an
unique Employee Ref.
Results contains Forename , Surname , DOB and Gender some, but not many Emp
Ref.
I would like to pull the Employee Ref from Data into results.

I think it's probably a case of doing Index Match on each set of data
because there will be mismatches due to differences in known by name and the
name held by records, people getting married and typing errors etc.

I haven't made a start yet but I'm planning to start by creating a helper
column with concatenated Surname, DOB and using Index Match and working from
there.

One problem I'm anticipating is if there are multiple matches and I am sure
there are more problems, so if anyone has any tips or helpful websites, I'd
be very grateful. I did find one website that gave a method for finding
matching text and a range of different formulas to find partially matching
text but can't find it now.

Many thanks
Diddy

 

[Dave Peterson]

Debra Dalgleish has lots of notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))
and
http://contextures.com/xlFunctions02.html#Trouble

I would concatenate the strings in a new column A:
=b1&"|"&c1&"|"&d1&"|"&....
Using a unique separation string/character that isn't found in any of the
cells. That way, I know my fields won't be combined to match any existing field
(just by random chance).

I'd ignore the date showing up as a serial number, too. But if it concerns you,
you could use:
=b1&"|"&text(c1,"yyyy-mm-dd")&"|"&...
to make things look prettier.

But before I'd use this table, I'd clean up the data so that there was exactly
one entry per employee number--and that'll be the difficult/frustrating thing
(well, if the list is any size at all).

I'd sort by emp#, then add a helper column that counts the number of times that
emp# appears in the list.

I'd use a helper column with a formula like:
=countif(E:E,E1)
and drag down.

Then I could apply an autofilter to show the values greater than 1 and start the
work.

Remember to save often--and even save as a new name every so often. It'll be
easier to go back to one of those saved versions when you realize you screwed up
something really badly!

 

[Diddy]

Thank you

That's brilliant. I'm going to get stuck in now armed with pointers and info.

Thank you again
Diddy