Join two spreadsheets on a common column in Excel or OpenOffice

Question

I have two CSV files with a common column and I want to "Join" the tables together on the common column.

For example: Join 'A' with 'B' equals 'Result'. If a one table has a key value that does not exist on in the other table its just left as blank.

== Table A ==        == Table B ==        == Table result ==
Name  ,Age           Name  ,Sex           Name ,Age ,Sex
Bob   ,37     +      Bob   ,Male     =>   Bob  ,37  ,Male
Steve ,12            Steve ,Male          Steve,12  ,Male
Kate  , 7                                 Kate , 7  , 
                     Sara  ,Female        Sara ,    ,Female 

I know how to do this with an SQL database but I have never done it with "Excel" or "OpenOffice.org Calc"

Suggestions?

Pinochle

Answer 1

In Excel, vlookup can do part of what you're asking. Specifically, you can use vlookup to do a left or right outer join, but not a full outer join (like your table result).

To do an outer join for your example above, add the following to the C2 of "Table B" (or copy "Table B" and then do this):

=vlookup(
    a2, # the cell value from the current table to look up in the other table
    table_a!$1:$174832718, # the other table
                           # don't manually type this--select the entire 
                           # other table while the cursor is editing this
                           # cell, then add the "$"s--Excel doesn't
                           # automatically add them
                           # (the syntax here is for different sheets in
                           # the same file, but Excel will fill this in 
                           # correctly for different files as well)
    2, # the column to get from the other table (A=1, B=2, etc.)
    FALSE) # FALSE=only get exact matches TRUE=find approx. matches if no exact match

You should then be able to expand it to deal with multiple rows and multiple imported columns.

Trueblue

to do a full outer join you could do the VLOOKUP operation in both directions, so merge table 1 in table 2 and table 2 in table 1. Then with the help of a macro or manually (copy paste) merge both tables into a single table and use the remove duplicates function on the resulting table. – Sowers

just to mention that first variable(a2 in his case) has to be in first column in order for vlookup to work. – Mathre

Answer 2

In Excel, you use VLOOKUP for that.
Assume you have the data in Table A listed in columns A and B in Excel.
And the data in Table B list in columns E and F.
Now, go to the first row in column C and enter:

=VLOOKUP(A:A,E:F,2,FALSE) 

This tells it to try to match column A with column E, and grab whatever is in the 2nd column near where we found it and place it in column C.
Now autofill the rest of the rows in column C to match the rest of the data.

Jar

Answer 3

If you can use Excel, there is a Query from Excel Files function:

• Define name for primary table - Table A (Formulas tab -> Define name)
• Define name for secondary table - Table B
• Go to Data tab, select "From Other Sources", and from the dropdown, select "From Microsoft Query"
• Select your CSV file and confirm that you want to merge the columns manually
• In the following window "Query from Excel Files", drag&drop the Name column of Table A into the Name column of Table B - 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 from both tables

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

Hope this helps.

Multiped