Best approach to merge CSV files by header name

[hip2b2]

I have lost my most recent database (again, again) but all is not lost... I hope. I have found a couple of files which I hope will help me rebuild the database.

File 1 is old but contains all the records and fields
File 2 is current, but some records and fields have been deleted from File 1.

What I would like to wind up with is the records in file 2 merged with missing fields from File 1 (but not adding any additional records) to create File 3.

Any suggestions as to an approach will be greatly (as always) appreciated.

hip

 

[Alex Blakenburg]

If you have a unique ID you could just one of the lookup functions (vlookup, xlookup etc) but if you need to join on multiple fields then why not use Power Query.

 

[hip2b2]

If you have a unique ID you could just one of the lookup functions (vlookup, xlookup etc) but if you need to join on multiple fields then why not use Power Query.

Alex,

I don't know anything about Power Query. It would take at least 2 fields to provide a unique ID (though I suppose I could concatenate those into a single field for comparison). Then I can delete the obsolete records with no matches.

The question now is how to merge the missing fields (there are many, many) from File 1 into File 2?

Thanks for the 1st step

 

[Alex Blakenburg]

If you need help with the Power Query side here are some youtube videos that might help.
I would recommend converting both the File1 & File2 data sets into Excel Tables and give them a meaningful name before you start. Having Power Query name them Table1 Table 2 etc is in my view bad practice. Using Tables will simplify the process.

They all only link on one field but 2 fields is just clicking 2 fields in order.
either select both fields from Table 1 by holding down the control key and then both fields in Table 2 - clicking the fields in the same order on each table
OR click linking field 1 Table 1 then matching field 1 Table 2 and repeat for field 2 this time holding down the control key.
A number will appear on each field heading and the numbers need to align (in the sense that they are the matching columns)

TrumpExcel
10 mins - he actually does 3 tables but you simply close and load the first merge, to excel as a table to get your result and skip the last merge.

Excel Campus
9.5 mins

ExcelIsFun
7 mins

 

[hip2b2]

If you need help with the Power Query side here are some youtube videos that might help.
I would recommend converting both the File1 & File2 data sets into Excel Tables and give them a meaningful name before you start. Having Power Query name them Table1 Table 2 etc is in my view bad practice. Using Tables will simplify the process.

They all only link on one field but 2 fields is just clicking 2 fields in order.
either select both fields from Table 1 by holding down the control key and then both fields in Table 2 - clicking the fields in the same order on each table
OR click linking field 1 Table 1 then matching field 1 Table 2 and repeat for field 2 this time holding down the control key.
A number will appear on each field heading and the numbers need to align (in the sense that they are the matching columns)

TrumpExcel
10 mins - he actually does 3 tables but you simply close and load the first merge, to excel as a table to get your result and skip the last merge.

Excel Campus
9.5 mins

ExcelIsFun
7 mins

Looks like the way to proceed (though I have heard from my wife several times that the best way would have been to not lose the database in the first place). I'll let you know how it works out.

 

[Alex Blakenburg]

LOL