Consolidate text/strings of employee info from multiple data sources

[michaxcore]

So I was given 3 different files to create a single employee list. While some of the columns on all 3 files are similar (like employee number, first name, last name), most of them are different or omitted.

For Example:

File A
[TABLE="width: 500"]
<tbody>[TR]
[TD]Employee Number[/TD]
[TD]Employee Last Name
[/TD]
[TD]Employee First Name
[/TD]
[TD]Date of Birth
[/TD]
[TD]Phone Number
[/TD]
[/TR]
[TR]
[TD]1999[/TD]
[TD]Everton[/TD]
[TD]Rupert[/TD]
[TD]6/7/1967[/TD]
[TD](818)402-8521[/TD]
[/TR]
[TR]
[TD]6532[/TD]
[TD]Walker[/TD]
[TD]Ruby[/TD]
[TD]4/18/1989[/TD]
[TD](213)658-1050
[/TD]
[/TR]
</tbody>[/TABLE]

File B
[TABLE="width: 500"]
<tbody>[TR]
[TD]Employee Number[/TD]
[TD]Employee Last Name
[/TD]
[TD]Employee First Name[/TD]
[TD]Address[/TD]
[TD]City
[/TD]
[TD]Zip
[/TD]
[/TR]
[TR]
[TD]1999[/TD]
[TD]Everton[/TD]
[TD]Rupert[/TD]
[TD]123 Main St.[/TD]
[TD]Scranton[/TD]
[TD]65262[/TD]
[/TR]
[TR]
[TD]6532[/TD]
[TD]Walker-Smith[/TD]
[TD]Ruby[/TD]
[TD]8547 Sweet Blvd.[/TD]
[TD]Burbank[/TD]
[TD]91504[/TD]
[/TR]
</tbody>[/TABLE]


File C
[TABLE="width: 500"]
<tbody>[TR]
[TD]Emp#[/TD]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]Address
[/TD]
[TD]City
[/TD]
[TD]Zip[/TD]
[TD]Phone Number
[/TD]
[TD]Hire Date
[/TD]
[TD]Job Title[/TD]
[TD]Division[/TD]
[/TR]
[TR]
[TD]1999[/TD]
[TD]Everton[/TD]
[TD]Rupert[/TD]
[TD]123 Main St.[/TD]
[TD]Scranton[/TD]
[TD]65262[/TD]
[TD](818)652-4545[/TD]
[TD]06/13/2011[/TD]
[TD]Cashier[/TD]
[TD]LA MAIN[/TD]
[/TR]
[TR]
[TD]6532[/TD]
[TD]Walker-Smith[/TD]
[TD]Ruby[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](213)658-1050[/TD]
[TD]5/25/2012[/TD]
[TD]Manager[/TD]
[TD]Hollywood[/TD]
[/TR]
</tbody>[/TABLE]

Ideally I would like a consolidation of all the headers, and if the data in the corresponding cells are the same or if info is missing then I would like that to be copied over and any differences be highlighted or omitted. I have mocked up what I would ideally like:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Emp#[/TD]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]Address
[/TD]
[TD]City
[/TD]
[TD]Zip[/TD]
[TD]Phone Number
[/TD]
[TD]Hire Date
[/TD]
[TD]Job Title[/TD]
[TD]Division[/TD]
[TD]Date of Birth[/TD]
[/TR]
[TR]
[TD]1999[/TD]
[TD]Everton[/TD]
[TD]Rupert[/TD]
[TD]123 Main St.[/TD]
[TD]Scranton[/TD]
[TD]65262[/TD]
[TD][/TD]
[TD]06/13/2011[/TD]
[TD]Cashier[/TD]
[TD]LA MAIN[/TD]
[TD]6/7/1967[/TD]
[/TR]
[TR]
[TD]6532[/TD]
[TD][/TD]
[TD]Ruby[/TD]
[TD] 8547 Sweet Blvd.
[/TD]
[TD]Burbank[/TD]
[TD]91504[/TD]
[TD](213)658-1050[/TD]
[TD]5/25/2012[/TD]
[TD]Manager[/TD]
[TD]Hollywood

[/TD]
[TD]4/18/1989[/TD]
[/TR]
</tbody>[/TABLE]

I know this is a lot to ask. I made a vlookup table, with exact and if statements but I have to go through about 900 employees so any way to expedite this would be great.

Thank you guys in advance

 

[Rijnsent]

Hi Michaxcore,

what I would do in this case:
-copy your 3 files into one file, every list on a different sheet (give the meaningfull names like "date" "address" and "phone")
-next, create a new sheet and copy all information from all columns A into that sheet, in column A, all under eachother
-use the button "remove duplicates" (in the menu "data"), now you should have a list with unique employee IDs in that sheet
-add the column names in your example and use vlookups to pull the data from the 3 different sheets into your "master sheet"

Hope that gets your started,

Koen