Excel help please

[Techie-Tech]

Hi,

 

Not sure how to word this, but I have 2 spreadsheets.

 

One is a data dump from our MIS which includes a STAFF ID. The other is a HR data dump, but the STAFF ID is completely different.

 

Is there a formula that can look at the HR sheet, match the first name and surname on both the HR sheet and the MIS sheet, and then copy the STAFF ID on the MIS sheet into a column on the HR sheet?

 

I hope that makes sense?

 

Thanks

[john]

Sounds like maybe a VLookup or two maybe could help?

[JRA]

Might have the wrong end of the stick here but would it be a case of organising both by surname alphabetically then just copying the column from the HR one into the MIS one?

[howartp]

See attached mockup for you.

 

Create a "Joined" column in MIS.

 

VLookup() that column in HR, to pull column 4 which contains HR ID.

MIS_HR_Lookup.xlsx

[Techie-Tech]

Might have the wrong end of the stick here but would it be a case of organising both by surname alphabetically then just copying the column from the HR one into the MIS one?

Not as simple as sort and then copy and paste. I wish it was lol

 

One sheet has 1 staff member per line, the other has multiple lines per staff member as they have their roles split out line by line.

 

ie: MIS

 

Joe Bloggs

Eric Wise

James Bond

 

HR

 

Joe Bloggs Cleaner

Joe Bloggs Midday Supervisor

Eric Wise Comedian

Eric Wise Chef

Eric Wise Bus Driver

[sideone]

Are there any unique identifiers on both sheets? You could chop out the role then remove duplicates, but it'll fail if you have more than one staff member with the same first and surname.

[Techie-Tech]

See attached mockup for you.

Create a "Joined" column in MIS.

VLookup() that column in HR, to pull column 4 which contains HR ID.

 

That has worked an absolute treat!

 

Thank you so much!

[howartp]

That has worked an absolute treat!

 

Thank you so much!

You're welcome

 

I do it several times a year for combining SIMS, ParentPay and others so it's second nature to me now.