Aug 25, 2020

I'd like to create a pivot table using multiple tabs with significant amounts of data

I am a nurse trying to manage data for a very large contact tracing project. I am attempting to create one giant pivot table that encompasses multiple tabs of a sheet. The columns for each tab are identical. They represent different regions in the project. I'd like to be able to quickly use the pivot table to provide any new summaries of data amongst the various columns that the higher ups ask for. I am new to pivot tables in general but have a basic grasp of the concept. Here's an example of the sheet I use to track the data:

I've seen some examples of code but really having trouble deciphering it.

Thanks in advance -

Matt
Locked
This question is locked and replying has been disabled.
Community content may not be verified or up-to-date. Learn more.
Recommended Answer
Aug 25, 2020
So, this depends on exactly what type of Pivot Table you are trying to get. 
 
If you use the Data menu > Pivot Table option, there is no direct way to get a combined pivot table. You can do it in an indirect way by having a single tab that consolidates things for you though. See the two tabs highlighted in orange. The data tab uses this formula in A1:
=QUERY({'Region 1'!A1:J;'Region 2'!A2:J;'Region 3'!A2:J}, "Where Col1 is not null",1)
 
To add other tabs you would add them before the closing curly bracket and separated with a semi-colon. ...'Region 3'!A2:J; 'Region 4'!A2:J }....
 
The other way to do this is by using some of the other options in QUERY. You can use the same QUERY as above but add more options such as pivot or group, etc. 
 
In the "QUERY Pivot" tab (highlighted magenta), I put this function:
=QUERY({'Region 1'!A1:J;'Region 2'!A2:J;'Region 3'!A2:J}, "Select Col1, Count(Col1) where Col1 is not null group by Col1 pivot Col6",1)
 
This will provide the count of items by date ("group by Col1") and then by zipcode ("pivot Col6"). There are not as many options with the QUERY pivot table option as there are for a plain pivot table though. 
 
Note that since your data does not specifically have a column for "Region" that information is NOT carried over to the consolidated data tab. It might be a good idea to add that column and include it in your data. 
Original Poster Matt Mendelow marked this as an answer
Helpful?
All Replies (5)
Recommended Answer
Aug 25, 2020
So, this depends on exactly what type of Pivot Table you are trying to get. 
 
If you use the Data menu > Pivot Table option, there is no direct way to get a combined pivot table. You can do it in an indirect way by having a single tab that consolidates things for you though. See the two tabs highlighted in orange. The data tab uses this formula in A1:
=QUERY({'Region 1'!A1:J;'Region 2'!A2:J;'Region 3'!A2:J}, "Where Col1 is not null",1)
 
To add other tabs you would add them before the closing curly bracket and separated with a semi-colon. ...'Region 3'!A2:J; 'Region 4'!A2:J }....
 
The other way to do this is by using some of the other options in QUERY. You can use the same QUERY as above but add more options such as pivot or group, etc. 
 
In the "QUERY Pivot" tab (highlighted magenta), I put this function:
=QUERY({'Region 1'!A1:J;'Region 2'!A2:J;'Region 3'!A2:J}, "Select Col1, Count(Col1) where Col1 is not null group by Col1 pivot Col6",1)
 
This will provide the count of items by date ("group by Col1") and then by zipcode ("pivot Col6"). There are not as many options with the QUERY pivot table option as there are for a plain pivot table though. 
 
Note that since your data does not specifically have a column for "Region" that information is NOT carried over to the consolidated data tab. It might be a good idea to add that column and include it in your data. 
Original Poster Matt Mendelow marked this as an answer
Aug 26, 2020
This is fantastic, thanks! Gives me tons to work with moving forward. Thanks again. Very grateful for the assistance!

Matt
Aug 26, 2020
You're welcome. I hope it helps. 
 
Thank you for all you've done to help your community, wherever that is. 
Aug 26, 2020
One last question. The query itself is very straightforward. Is there a parameter I can add that will sort the data by date? Currently when I try to sort, it sorts each region in the query by date but not the entire dataset as one.
Aug 26, 2020
In the "consolidated data" tab, I changed the formula to this:
=QUERY({'Region 1'!A1:J;'Region 2'!A2:J;'Region 3'!A2:J}, "Where Col1 is not null order by Col1 asc",1)
 
That should do it for that version.
 
For the other version, using QUERY with Pivot, I made a copy of the tab since you were editing the formula and changed it to this:
=QUERY({'Region 1'!A1:J;'Region 2'!A2:J;'Region 3'!A2:J}, "Select Col1, Count(Col1) Where Col1 is not null group by Col1 pivot Col6 order by Col1 desc",1)
 
I used "asc" for the first one and "desc" for the second one, you can switch them to whichever you want. I used "desc" for the second mainly because "group by" automatically sorts them by date in ascending order. This way you can compare the original with the copy to see what it has done.
false
15079195017741124228
true
Search Help Center
true
true
true
true
true
35
Search
Clear search
Close search
Main menu
false