How to join two Excel tables

Joining is where we take two Excel tables and merge the rows, side-by-side, using a common (‘key’) column. For example to turn:

Into:

(You can also merge tables one on top of another)

Excel doesn’t really support joining. One option is to write some VBA script to do this for you. Another is to import the data into a database and do an SQL join. But both these are time-consuming and require technical skills. However it is really easy in Easy Data Transform:

1. Install Easy Data Transform on your PC or Mac, if you haven’t done so already. It will only take a minute. There is a fully functional free trial and you don’t have to give us your email or sign up to anything.
2. Start Easy Data Transform. Make sure the Auto Run button is pressed in.

1. Drag the Excel spreadsheets you want to merge onto Easy Data Transform. A pink input item will be added for each selected sheet with data.
2. Drag the input items into the order you want to stack them (the one with the columns you want on the left, at the top).
3. Select the two input items you wish to join using Ctrl+click (PC)/Cmd+click (Mac) (or by dragging a box around them).
4. Click the Join button in the Left pane (if it is grey, you haven’t selected 2 items to join). A blue transform item is added and selected.
5. Set the Top key column and Bottom key column to the common (‘key’) column (Easy Data transform may have already guessed them correctly). Here the key column is called OrderId in both sheets.

The Excel tables are now joined with the top input item columns on the left of the table. No need to ‘run’ anything. Easy!

Note:

• Set Include top non-matching rows and Include bottom non-matching rows depending on what you want to do with top and bottom table rows with no match.
• Change the vertical position of the inputs to the Join to change the join order.
• If you just want to join row N of one table to row N of the other you can use the Row Num transform to create a common column in each dataset before the Join.
• You can use the Concat Cols transform to join two or more columns to create a new column to use as the key.

For more details about Join options see the Join documentation.

When you are happy with the results you can output the transformed data to a new file in a range of file formats. Ensuring that the blue transform item is selected, click the To File button at the bottom of the Left pane to create a new output file (you might have to scroll down to see it). Select the file type you wish to export to.

If you need to join lots of Excel sheets you can do it in a single operation using the batch processing feature.

Join is just one of the 66 transforms that Easy Data Transform supports. Easy Data Transform allows you to combine multiple transforms to quickly and easily create complex data transformations. Easy Data Transform can process millions of rows and input and output in multiple formats.