excel: merging to data sets based on a common field

Question

I have two excel .csv files that have a single numerical field in common, FIELD_A.

What I'd like to be able to do is to leverage this common field in order to merge data between the two, so that I have a single file that contains a row of data that includes data from both .csv files.

i.e.

file_1.csv:

FIELD A | FIELD B

1       | foo

merges with

file_2.csv:

FIELD A | FIELD C

1       | bar

to result in

merged_file.xls:

FIELD A | FIELD B  | FIELD C

1       | foo      | bar

Is there a way to use excel to do this sort of thing?

Solution 1

You can use Query from Excel Files :

• Define name for dataset in file_1.csv (Formulas tab -> Define name)
• Define name for dataset in file_2.csv
• Go to Data tab, select "From Other Sources", and from the dropdown, select "From Microsoft Query"
• Select your workbook file and confirm that you want to merge the columns manually
• In the following window "Query from Excel Files", drag&drop the FIELD A column of first dataset into the FIELD A column of second dataset - a link between these columns will be created
• Go to File menu, click "Return Data to MS Office Excel", an Import Data dialog will pop up
• Select the sheet into which you would like the matched data to be imported
• Click OK -> you should see matched data with columns FIELD A | FIELD B | FIELD A | FIELD C

Or if you don't mind uploading your workbook to an online service, you can use for example http://www.gridoc.com/join-tables and merge the sheets using drag&drop (Disclaimer: I am author of the tool)

Hope this helps.

Solution 2

Prepare unique list of FIELD A entries
In ColumnA of a new sheet, copy Field A details with header from file_1.csv and append to that Field A details without header from file_2.csv. Might as well sort. Advanced Filter ColumnA with Copy to another location, Copy to: B1 and Unique records only selected. OK. (Remove Duplicates is not quite as reliable as Advanced Filter.)

Lookup
Name your ranges range in each source file. In C2 enter:

=IFERROR(VLOOKUP($B2,file_1.csv!range,2,FALSE),"")

and copy across to D2. In D2 change 1 to 2. Copy C2:D2 down as required.

Tidy up
Copy ColumnsC:D and Paste/Special/Values over the top. Delete ColumnsA:B.

Comments

[fox]

Hi there, thanks for this -- can you tell me in a bit more detail what I should do to Prepare unique list of FIELD A entries? The above is a little confusing to me.

[HC_]

On step 4, I don't see any selectable .csv or .xls files under Data>From Other Sources>From Microsoft Query -- any idea how to fix?