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?

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
    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 '13 at 21:24
  • @fox Sure. Combine the two Field A lists in one column and use Remove Duplicates - will work very, very nearly always. The reason for keeping the header (for one) was connected with Advanced Filter. (But if using Remove Duplicates then the cell references will need adjusting.) – pnuts Jun 19 '13 at 23:00
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
    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 '17 at 0:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.