1

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?

0

2 Answers 2

1

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.

1
  • 1
    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.
    – fox
    Jun 19, 2013 at 21:24
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.

1
  • 1
    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?
    – HC_
    Jan 6, 2017 at 0:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .