Combining 4 worksheets into one Pivot Table

[jaspalsd]

Hi,

I am having to do 4 separate downloads into CSV files because of the number of rows reaches the limit. E.g. data from 2013/2014 through to 2020/2021. Please note the range of dates (e..g months) differ as I set it to download before the row limit was reached. Some tabs have 24 months worth, others less but there is no possibility to paste the data onto one Excel tab as the number of total rows will be about 4 million.

The only main differences between the four tabs is the Year. name Fiscal and Month Name fiscal

By looking at the guide for consolidating pivot tables it appears the data for rows and columns must be the same, and it must already be in a table format with rows and columns.

I tried consolidating the data but the rows and columns grouped the fields together as one item so that didn't work.

If I was to try to format the data as a table beforehand (with rows and columns) then I know if I was to consolidate then the number of columns will differ.

I have been advised that Power query may work but I am not so sure given the fact that each tab will have differences in the month names.


Thank you for your help

 

[jaspalsd]

sorry to clarify,

data in all tabs have the same format:

A ) Year (will differ on each tab) Optional - Pivot Table Row
B) Month No
C) Month Name Fiscal (will differ on each tab) - Required Pivot table column heading
D) Channel - Required - Required Pivot Table Row
D) Group Name - Required Pivot Table Row
E) Asset - Required Pivot Table Row
F) Actual/Projected - Optional Pivot Table Row
G) Items - Various financial items - Required Pivot Table Row
H) Amount - Required Pivot table values

I think the issue here will be the required Month-Names if I was to consolidate as I definitely need a monthly breakdown. As a workaround I could create tables for all and add empty columns for the month names but it seems a long-winded approach...

 

[MStaddon]

You may be able to pull the full data-set into a Data Model (using Power Query, as you've suggested) - so won't have to worry about splitting the data at all. I'm having loads of 'fun' with my Data Model at the moment too, but in principle it's a great tool to use.

 

[sandy666]

try Alt+D+P

 

[Fazza]

If you're interested, Google will find many good sites showing how to make a pivot table from multiple worksheets.

I think the oldest VBA solution is this 2008 thread which was after some code: and then in the second half of post #21 includes description of a manual approach that suits this 4 worksheet question.
https://www.mrexcel.com/forum/excel-questions/315768-creating-pivot-table-multiple-sheets.html

The approach works in all versions of pivot tables.

Post #26 of that thread links to earlier Non-VBA examples, too.