The Union All Query

// The Union All Query

The Union All Query

Combining two tables of data into one without Macros

By Wyn Hopkins


Pic 1 - combining 2 tables

You may have regular reports that you collate and then manually copy and paste together or you may use macros to combine them together.

But there is another way that, once set up, is very easy to update – a simple right-click and Refresh does all the work for you.

You can then link pivot reports or SUMIFS formula etc to this combined table of data and hey-presto your reports are ready

Here’s a step by step guide on how to set this up using a file I’ve called Data Source.xlsx.  In this illustration there’s only 2 tables being combined – year 1 and year 2 but you could add multiple years if required.

Importantly I named my ranges of data first using the Name Manager and then converted them to tables using the Ctrl+T shortcut (creating a table first and then naming the range can make life difficult).

Once you have your source data set up then save the file and close it before proceeding.

Now open a new workbook and follow these steps:
Using Microsoft Query

Go to Data, From Other Sources, From Microsoft Query

Pic 2 - combining 2 tables


Select New Data Source and click OK

Pic 3 - combining 2 tables


Give it a name and then select Microsoft Excel Driver and click Connect

Pic 4 - combining 2 tables


Click on Select workbook

Pic 5 - combining 2 tables


And locate your source data file, click on it and click OK

Pic 6 - combining 2 tables


Select the drop down DataYear1 and click OK

Pic 7 - combining 2 tables


Click on Combined Year Query and select OK

Pic 8 - combining 2 tables


Add both DataYear1 and DataYear2 to the “Columns in your query:” box

Pic 9 - combining 2 tables
pic 10 - combining 2 tables


Click OK

Pic 11 - combining 2 tables


Select View > SQL

Pic 12 - combining 2 tables


You should then see something like this…

Pic 13 - combining 2 tables


Re-arrange the query to look like this (use Ctrl X and Ctrl V to cut and paste)

pic 14 - combining 2 tables
Add the line UNION ALL between the 2 queries

Pic 15 - combining 2 tables


Click on the door with the arrow to return the data to Excel

pic 14 - combining 2 tables


Hey Presto!

Pic 16 - combining 2 tables


You can then refresh this table at any time by right clicking on it and select Refresh  (or go  to the data menu and select Refresh All).
Any formula columns you add to the table will automatically populate as the data expands (or contracts).

2017-03-08T03:54:04+00:00Data Handling|