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|