Help With Pivot - Combining Multiple Tables

[Matt McFadden]

Hello,

I am currently tracking information for a variety of locations. I have nearly 450 different locations that i need to track identical information, I am counting specific equipment and separating it by a descriptive class. For example - at a specific location i would like to count the number of circles, separated by color. I would like to be able to combine all of this data into one table, and add to this table as i collect information on additional sites. It seems pivot would be the best way to accomplish this however, it doesn't appear to be recognizing identical categories. When select the categories for all three tables, color in this case, it will show all the colors, and then list all the colors as sub categories underneath each individual color.

This is not what i would like... I would have hoped to have a table that tracks the colors, the sub categories would be the table name and on each cell belonging to the sub category would be the count associated with specific color and shape of that table - the sum would be the total sum of the tables. When i attempt to add in the counts of the shapes, distinguished by color, it appears to add in the "sum of [shape]" for each table, instead of consolidating it to 1 sum. When i expand each color, i would like to see the table name and count. Below is the resultant table, i had to collapse the drop downs to fit here.

Please let me know if this does not describe my issue well.

Book1
	B	C	D	E	F	G	H	I	J	K	L	M
2
3
4		Color	Circles	Squares	Triangles
5		Red	5	14	1
6		Green	6	15	6
7		Blue	7	16	5
8		Yellow	13	3	2
9
10		Color	Circles	Squares	Triangles
11		Red	4	3	2
12		Green	5	10	5
13		Blue	4	8	5
14		Yellow	6	7	4
15
16		Color	Circles	Squares	Triangles
17		Red	9	8	7
18		Green	10	4	8
19		Blue	8	2	3
20		Yellow	6	1	2
21
22		Row Labels	Sum of Circles	Sum of Squares	Sum of Circles	Sum of Squares	Sum of Triangles	Sum of Triangles	Sum of Circles	Sum of Squares	Sum of Triangles
23		Blue
24		Blue
25		Blue	7	16	4	8	5	5	8	2	3
26		Green	7	16	4	8	5	5	10	4	8
27		Red	7	16	4	8	5	5	9	8	7
28		Yellow	7	16	4	8	5	5	6	1	2
29		Green
30		Blue	7	16	5	10	5	5	8	2	3
31		Green	7	16	5	10	5	5	10	4	8
32		Red	7	16	5	10	5	5	9	8	7
33		Yellow	7	16	5	10	5	5	6	1	2
34		Red
35		Blue	7	16	4	3	2	5	8	2	3
36		Green	7	16	4	3	2	5	10	4	8
37		Red	7	16	4	3	2	5	9	8	7
38		Yellow	7	16	4	3	2	5	6	1	2
39		Yellow
40		Blue	7	16	6	7	4	5	8	2	3
41		Green	7	16	6	7	4	5	10	4	8
42		Red	7	16	6	7	4	5	9	8	7
43		Yellow	7	16	6	7	4	5	6	1	2
44		Green
45		Blue
46		Blue	6	15	4	8	5	6	8	2	3
47		Green	6	15	4	8	5	6	10	4	8
48		Red	6	15	4	8	5	6	9	8	7
49		Yellow	6	15	4	8	5	6	6	1	2
50		Green
51		Blue	6	15	5	10	5	6	8	2	3
52		Green	6	15	5	10	5	6	10	4	8
53		Red	6	15	5	10	5	6	9	8	7
54		Yellow	6	15	5	10	5	6	6	1	2
55		Red
56		Blue	6	15	4	3	2	6	8	2	3
57		Green	6	15	4	3	2	6	10	4	8
58		Red	6	15	4	3	2	6	9	8	7
59		Yellow	6	15	4	3	2	6	6	1	2
60		Yellow
61		Blue	6	15	6	7	4	6	8	2	3
62		Green	6	15	6	7	4	6	10	4	8
63		Red	6	15	6	7	4	6	9	8	7
64		Yellow	6	15	6	7	4	6	6	1	2
65		Red
66		Blue
67		Blue	5	14	4	8	5	1	8	2	3
68		Green	5	14	4	8	5	1	10	4	8
69		Red	5	14	4	8	5	1	9	8	7
70		Yellow	5	14	4	8	5	1	6	1	2
71		Green
72		Blue	5	14	5	10	5	1	8	2	3
73		Green	5	14	5	10	5	1	10	4	8
74		Red	5	14	5	10	5	1	9	8	7
75		Yellow	5	14	5	10	5	1	6	1	2
76		Red
77		Blue	5	14	4	3	2	1	8	2	3
78		Green	5	14	4	3	2	1	10	4	8
79		Red	5	14	4	3	2	1	9	8	7
80		Yellow	5	14	4	3	2	1	6	1	2
81		Yellow
82		Blue	5	14	6	7	4	1	8	2	3
83		Green	5	14	6	7	4	1	10	4	8
84		Red	5	14	6	7	4	1	9	8	7
85		Yellow	5	14	6	7	4	1	6	1	2
86		Yellow
87		Blue
88		Blue	13	3	4	8	5	2	8	2	3
89		Green	13	3	4	8	5	2	10	4	8
90		Red	13	3	4	8	5	2	9	8	7
91		Yellow	13	3	4	8	5	2	6	1	2
92		Green
93		Blue	13	3	5	10	5	2	8	2	3
94		Green	13	3	5	10	5	2	10	4	8
95		Red	13	3	5	10	5	2	9	8	7
96		Yellow	13	3	5	10	5	2	6	1	2
97		Red
98		Blue	13	3	4	3	2	2	8	2	3
99		Green	13	3	4	3	2	2	10	4	8
100		Red	13	3	4	3	2	2	9	8	7
101		Yellow	13	3	4	3	2	2	6	1	2
102		Yellow
103		Blue	13	3	6	7	4	2	8	2	3
104		Green	13	3	6	7	4	2	10	4	8
105		Red	13	3	6	7	4	2	9	8	7
106		Yellow	13	3	6	7	4	2	6	1	2
107		Grand Total	31	48	19	28	16	14	33	15	20
108
109
Sheet1

 

[RoryA]

I'd suggest you use Power Query to append the three tables into one, then pivot from that.

 

[Matt McFadden]

Hi Rory! Thanks for the response.
The 3 tables is a small scale example of what I'd like to accomplish. In totality, i will have 450 different tables, not all completed at once. This will be a continuously updated list - starting with about 4 - 10 tables made and continuing over the coming weeks/months to complete the rest of the tables.
With that, will I continuously need to append the tables to then pivot? What drew me to pivot in the first place was that after I finished creating the table, it would show up in the "all" tab and i could select from there the categories i would like to include

So as i create new tables, i can select the columns i would like to include from there.

 

[RoryA]

In totality, i will have 450 different tables

Can I ask why? That's not a good design for reporting.

 

[Matt McFadden]

Absolutely, and i would appreciate any suggestions as well if you have a better idea.
While we will be tracking the amounts from a high level, we will also want a bit more granularity at the site level. So if i were tracking these shapes/colors in Lexington, Kentucky for example, I could see the information presented in a table. Instead of having multiple columns - red circle, red square,

vs

 

[RoryA]

But if you added the site name as an additional column, you'd only need one table for all the sites. You can always extract data on a per site basis (or just filter it) as needed.

You can combine all the tables using PQ though if you are wedded to your current set up.