Help With Pivot - Combining Multiple Tables

Matt McFadden

New Member
Joined
Jun 29, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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.
1656510154090.png
1656510186557.png



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.
1656510444628.png

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

Book1
BCDEFGHIJKLM
2
3
4ColorCircles SquaresTriangles
5Red5141
6Green6156
7Blue 7165
8Yellow1332
9
10ColorCircles SquaresTriangles
11Red432
12Green5105
13Blue 485
14Yellow674
15
16ColorCircles SquaresTriangles
17Red987
18Green1048
19Blue 823
20Yellow612
21
22Row LabelsSum of CirclesSum of SquaresSum of CirclesSum of SquaresSum of TrianglesSum of TrianglesSum of CirclesSum of SquaresSum of Triangles
23Blue
24Blue
25Blue7164855823
26Green71648551048
27Red7164855987
28Yellow7164855612
29Green
30Blue71651055823
31Green716510551048
32Red71651055987
33Yellow71651055612
34Red
35Blue7164325823
36Green71643251048
37Red7164325987
38Yellow7164325612
39Yellow
40Blue7166745823
41Green71667451048
42Red7166745987
43Yellow7166745612
44Green
45Blue
46Blue6154856823
47Green61548561048
48Red6154856987
49Yellow6154856612
50Green
51Blue61551056823
52Green615510561048
53Red61551056987
54Yellow61551056612
55Red
56Blue6154326823
57Green61543261048
58Red6154326987
59Yellow6154326612
60Yellow
61Blue6156746823
62Green61567461048
63Red6156746987
64Yellow6156746612
65Red
66Blue
67Blue5144851823
68Green51448511048
69Red5144851987
70Yellow5144851612
71Green
72Blue51451051823
73Green514510511048
74Red51451051987
75Yellow51451051612
76Red
77Blue5144321823
78Green51443211048
79Red5144321987
80Yellow5144321612
81Yellow
82Blue5146741823
83Green51467411048
84Red5146741987
85Yellow5146741612
86Yellow
87Blue
88Blue1334852823
89Green13348521048
90Red1334852987
91Yellow1334852612
92Green
93Blue13351052823
94Green133510521048
95Red13351052987
96Yellow13351052612
97Red
98Blue1334322823
99Green13343221048
100Red1334322987
101Yellow1334322612
102Yellow
103Blue1336742823
104Green13367421048
105Red1336742987
106Yellow1336742612
107Grand Total314819281614331520
108
109
Sheet1
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
I'd suggest you use Power Query to append the three tables into one, then pivot from that.
 
Upvote 0
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
1656512118512.png

So as i create new tables, i can select the columns i would like to include from there.
 
Upvote 0
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,

1656513074114.png


vs

1656513051520.png
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top