This seems like a simple concept, yet I can't figure out a way to do it without writing some code. Does Excel have a function that will let me join columns from 2 different csv files (say Orders and OrderDetails) based on a shared ID column and create a composite csv with columns from both?
Yes, there is. It's a little hacky, but a lot better than having to write code.
Put the two sets in separate columns, with a column in between.
In cell B2, enter this formula
The first part $A$2:$A$8 is the range of cells from Set1. The $ symbols keep it a static range, so you can copy and paste into new cells and the range stays the same. The C3 is the cell immediately to the right of the current cell that you are joining on.
Copy and paste this all the way down, and (showing formulas for demostration) you will have this:
which will actually look like this:
A few more steps:
You now have a simple right outer join
You have the inner join
I leave it as an exercise to the reader to handle multiple columns tables.
Ok, I'll give you simplest and most inefficient method that occurs to me: do the inner join in both directions, sort each set on the join field, and put the results side-by-side. They should line up. (Note: this would only work if the join column contains unique values)
I would not use Excel to accomplish this task. Instead, I would use a command line program called
The syntax is straight forward:
Usage: join [OPTION]... FILE1 FILE2 For each pair of input lines with identical join fields, write a line to standard output. The default join field is the first, delimited by whitespace. When FILE1 or FILE2 (not both) is -, read standard input. -a FILENUM print unpairable lines coming from file FILENUM, where FILENUM is 1 or 2, corresponding to FILE1 or FILE2 -e EMPTY replace missing input fields with EMPTY -i, --ignore-case ignore differences in case when comparing fields -j FIELD equivalent to `-1 FIELD -2 FIELD' -o FORMAT obey FORMAT while constructing output line -t CHAR use CHAR as input and output field separator -v FILENUM like -a FILENUM, but suppress joined output lines -1 FIELD join on this FIELD of file 1 -2 FIELD join on this FIELD of file 2 --help display this help and exit --version output version information and exit Unless -t CHAR is given, leading blanks separate fields and are ignored, else fields are separated by CHAR. Any FIELD is a field number counted from 1. FORMAT is one or more comma or blank separated specifications, each being `FILENUM.FIELD' or `0'. Default FORMAT outputs the join field, the remaining fields from FILE1, the remaining fields from FILE2, all separated by CHAR. Important: FILE1 and FILE2 must be sorted on the join fields.
The last line is very important! Inside of this same zip file, includes
This is possible in MSQuery although you will have to setup a datasource in ODBC
This isn't a function, but you should find it relatively simple to implement. Help does provide documentation on accomplishing different joins as well
The resultant SQL msquery creates is: