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?
The easiest way is to have your 2 Excel sheets as Excel tables. Then in Excel, go to the Power Query ribbon tab, and click the 'From Excel' button. Once you have imported both tables into Power Query, select one and click 'Merge'.
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.
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.
If you can use Excel, there is a Query from Excel Files function:
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With