Create a Union Query Manually Union Query Macro -- Sheets in One File Union Query Macro -- Data in Multiple Excel Files In Excel, you can open the Microsoft Query tool, and write a SQL statement to create a Union query (full outer join) to combine multiple tables.
Then, use the result as the pivot table's source data.
Pivot Table - The first example works on multiple files, which must have the data in identical structures, and you can read the instructions on my blog.
To see Kirill's pivot table code, you can download the Pivot Workbooks example.
Read the details in blog post, Create a Pivot Table from Multiple Files. Download the sample pivot table tutorial file To save time when building, formatting and modifying your pivot tables, use the tools in my Pivot Power Premium add-in.
The examples will add a worksheet with the name RDBMerge Sheet to your workbook and will copy cellsfrom every worksheet in the Active Workbook to this worksheet.
The zipped folder that contains the file, and the five sample data files.
Pivot Table or Excel Table - Select two or more files which have lists in an identical structure, and the code in this workbook will automatically create a pivot table or Excel table from all the data.For example, if your sheet names are "East" and "West", change this line of code: In the code, you can also change the location where the pivot table will be added.In the sample file, the Table Destination is set for the active sheet, in range A1.by Dig DB “Use this Excel add-in to remove duplicates.Remove by list matching or key fields; do Access-like query, aggregation, join; simplify pivot table with a roll-up that outputs in simple list; enhance pivoting and subtotal to aggregate in 21 statistical functions such as median and count of unique values.