Matt McFadden
New Member
- Joined
- Jun 29, 2022
- Messages
- 6
- Office Version
- 365
- Platform
- 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.
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.
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 |