Advanced Pivot Table - Multiple Value Search

[Keeper4826]

I don’t know how to create effective pivot tables. I have a very large table and I need to identify totals for various groups.

Rules:

• A person with multiple certifications for a category only counts as one. E.g., A person with two CAT 3 certifications only counts as one).
• A person will be counted at the highest category level only. E.g., A person with CAT 2 and CAT 3 certifications will only be recorded as CAT 2 (the higher of the two certs).

The first table needs to show totals across the board but cannot include duplicates (personnel with multiple certifications in the same category, or certifications in a lower category - e.g. CAT 1 certified cannot be double counted in the CAT 2 certified numbers). I want it to show the categories in the first column, totals the second column, and in the third column totals only for those who have a “Yes” in the Mission column.

Category	Total	Mission
CAT 1	5	4
CAT 2	10	7
CAT 3	3	2

The second table needs to have units in the left column, and six columns with the totals for the first table (CAT 1 – Total, CAT 2 – Total, CAT 3 – Total, CAT 1 – Mission, CAT 2 – Mission, CAT 3 – Mission).

Units	CAT 1 - Total	CAT 2 - Total	CAT 3 - Total	CAT 1 - Mission	CAT 2 - Mission	CAT 3 - Mission
Blue	0	4	0	0	3	0
Green	1	2	2	1	1	1
Red	0	4	0	0	3	0
Yellow	4	0	1	3	0	1

Here is an example dataset. I have no idea how to create the pivot tables described above.

Test_Pivot Table.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Name	Unit	School Name 1	School Name 2	School Name 3	School Name 4	School Name 5	School Name 6	School Name 7	School Name 8	School Name 9	School Name 10	School Name 11	School Name 12	School Name 13	School Name 14	School Name 15	Mission
2			Full Title	Full Title	Full Title	Full Title	Full Title	Full Title	Full Title	Full Title	Full Title	Full Title	Full Title	Full Title	Absolute Title	Full Title	Scope Absolute	YES/NO
3			CAT 1	CAT 1	CAT 1	CAT 1	CAT 2	CAT 2 & Other 1	CAT 2	CAT 3 & Other 1	CAT 3	Other 1	Other 2	Other 3	AB	AB	CD
4	Person 1	Blue						x						x				Yes
5	Person 2	Blue						x			x							Yes
6	Person 3	Blue						x										Yes
7	Person 4	Blue						x										No
8	Person 5	Green	x				x		x	x	x							Yes
9	Person 6	Green					x			x								Yes
10	Person 7	Green								x	x				x	x		Yes
11	Person 8	Green							x	x	x							No
12	Person 9	Green								x	x				x	x		No
13	Person 10	Red						x										Yes
14	Person 11	Red						x			x							Yes
15	Person 12	Red						x			x				x		x	Yes
16	Person 13	Red						x										No
17	Person 14	Yellow								x	x							Yes
18	Person 15	Yellow				x												Yes
19	Person 16	Yellow		x		x												Yes
20	Person 17	Yellow				x												Yes
21	Person 18	Yellow				x												No
Data

 

[Keeper4826]

I created a new sheet to try and calculate my totals, which will hopefully make displaying in pivot tables easier.

Test_Pivot Table.xlsx
	A	B	C	D	E	F	G	H
1	Name	Unit	CAT 1	CAT 2	CAT 3	CAT 1 Mission	CAT 2 Mission	CAT 3 Mission
2	Person 1	Blue	0	1	0	0	1	0
Formulas

Cell Formulas
Range	Formula
C2	C2	=IF(OR(Data!C4:F4="x"), 1, 0)
D2	D2	=IF(AND(C2<1, OR(Data!G4:I4="x")), 1, 0)
E2	E2	=IF(AND(C2<1, D2<1, OR(Data!J4:K4="x")), 1, 0)
F2	F2	=IF(AND(C2=1, Data!R4="Yes"), 1, 0)
G2	G2	=IF(AND(D2=1, Data!R4="Yes"), 1, 0)
H2	H2	=IF(AND(E2=1, Data!R4="Yes"), 1, 0)

 

[Keeper4826]

Here's my first attempt at the pivot table, using the above formula table as my dataset. To my original post, is possible to achieve this without the intermediate step; can I can this using the original raw dataset? I tried playing with groupings but couldn't figure out how to manually group the schools that certify to the same category level.

Test_Pivot Table.xlsx
	A	B	C	D	E	F	G
3	Row Labels	Sum of CAT 1	Sum of CAT 2	Sum of CAT 3	Sum of CAT 1 Mission	Sum of CAT 2 Mission	Sum of CAT 3 Mission
4	Blue	0	4	0	0	3	0
5	Green	1	2	2	1	1	1
6	Red	0	4	0	0	3	0
7	Yellow	4	0	1	3	0	1
8	Grand Total	5	10	3	4	7	2
Sheet4

 

[Keeper4826]

As I continue to research, it looks like custom groupings in a pivot table is the answer, but I haven't figured out how to create and use those. Also, it looks like I can use a slicer to dynamically display the different subsets I'm looking for. Again, I haven't figured out how to use slicers yet - since I haven't solved custom groupings.