Count Yes/No in Pivot Table

[ConfusedMum]

Hi all,

I am hoping someone can advise me on how to set up my pivot table please?

This isn't the best way of setting up feedback analysis I am very sure, but I need to work with what I've got! I have survey results from students who have done a variety of modules for their courses and submit a feedback survey at the end of each module - the tricky bit to analysis is the free text question and that's what I am stuck on here. I have been going through reading each entry and if it mentions something about one of the key categories I put Yes in the column for that category and if the entry doesn't then I type No. Here is a shortened example (mine is obviously much bigger than this and has nearly 1,800 survey responses and 10 different categories):

Course	Module	Comment	Sentiment	Navigation/Design	Activities/Content	Live Sessions/Videos	Groupwork	Reading List
Cats101	abc1	Blah blah	Positive	No	Yes	No	No	No
Cats102	def1	Blah blah	Mixed	No	No	Yes	Yes	Yes
Cats103	ghi1	Blah blah	Positive	Yes	No	Yes	Yes	No
Cats102	def1	Blah blah	Mixed	No	Yes	Yes	Yes	Yes
Cats101	abc1	Blah blah	Mixed	No	No	No	No	No
Cats102	def1	Blah blah	Negative	No	No	Yes	No	Yes
Cats101	abc4	Blah blah	Positive	Yes	Yes	Yes	No	Yes
Cats101	abc7	Blah blah	Mixed	No	Yes	Yes	Yes	No

I then have a pivot table connected to it. As you can from the attached images I can't get the combinations right on the pivot table!

What I am TRYING (and failing so far!) to show is to count the number of Yes's and No's for each category for each module/course. So, ideally, I would have Course and Module in the Rows, then the categories (Navigation/Design, Activities/Content, etc...) along the top and any of the following:

• each category column broken down into two - Yes and No, with a count of each ) (my preferred option)
• each category column showing just the Yes count (if above option isn't possible!)

Any ideas you wonderful people - PLEASE!!!!

Thanks SO much,
Cathy ?

 

[Alex Blakenburg]

You might want to update your profile to say what version of Excel you are using.
If you have Power Query and are interested in using that, then it is only a couple of steps to transform what you have into a format that works for pivot tables.

eg.
These steps

Produce this M Code

[CODE=pq]
let
    Source = Excel.CurrentWorkbook(){[Name="tblData"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Course", "Module", "Comment", "Sentiment"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Course", type text}, {"Module", type text}, {"Comment", type text}, {"Sentiment", type text}, {"Attribute", type text}, {"Value", type text}})
in
    #"Changed Type"

[/CODE]

And this table:

20211124 PQ to Pivot Yes No.xlsm
	L	M	N	O	P	Q
1	Course	Module	Comment	Sentiment	Attribute	Value
2	Cats101	abc1	Blah blah	Positive	Navigation/Design	No
3	Cats101	abc1	Blah blah	Positive	Activities/Content	Yes
4	Cats101	abc1	Blah blah	Positive	Live Sessions/Videos	No
5	Cats101	abc1	Blah blah	Positive	Groupwork	No
6	Cats101	abc1	Blah blah	Positive	Reading List	No
7	Cats102	def1	Blah blah	Mixed	Navigation/Design	No
8	Cats102	def1	Blah blah	Mixed	Activities/Content	No
9	Cats102	def1	Blah blah	Mixed	Live Sessions/Videos	Yes
10	Cats102	def1	Blah blah	Mixed	Groupwork	Yes
11	Cats102	def1	Blah blah	Mixed	Reading List	Yes
12	Cats103	ghi1	Blah blah	Positive	Navigation/Design	Yes
13	Cats103	ghi1	Blah blah	Positive	Activities/Content	No
14	Cats103	ghi1	Blah blah	Positive	Live Sessions/Videos	Yes
15	Cats103	ghi1	Blah blah	Positive	Groupwork	Yes
16	Cats103	ghi1	Blah blah	Positive	Reading List	No
17	Cats102	def1	Blah blah	Mixed	Navigation/Design	No
18	Cats102	def1	Blah blah	Mixed	Activities/Content	Yes
19	Cats102	def1	Blah blah	Mixed	Live Sessions/Videos	Yes
20	Cats102	def1	Blah blah	Mixed	Groupwork	Yes
21	Cats102	def1	Blah blah	Mixed	Reading List	Yes
22	Cats101	abc1	Blah blah	Mixed	Navigation/Design	No
23	Cats101	abc1	Blah blah	Mixed	Activities/Content	No
24	Cats101	abc1	Blah blah	Mixed	Live Sessions/Videos	No
25	Cats101	abc1	Blah blah	Mixed	Groupwork	No
26	Cats101	abc1	Blah blah	Mixed	Reading List	No
27	Cats102	def1	Blah blah	Negative	Navigation/Design	No
28	Cats102	def1	Blah blah	Negative	Activities/Content	No
29	Cats102	def1	Blah blah	Negative	Live Sessions/Videos	Yes
30	Cats102	def1	Blah blah	Negative	Groupwork	No
31	Cats102	def1	Blah blah	Negative	Reading List	Yes
32	Cats101	abc4	Blah blah	Positive	Navigation/Design	Yes
33	Cats101	abc4	Blah blah	Positive	Activities/Content	Yes
34	Cats101	abc4	Blah blah	Positive	Live Sessions/Videos	Yes
35	Cats101	abc4	Blah blah	Positive	Groupwork	No
36	Cats101	abc4	Blah blah	Positive	Reading List	Yes
37	Cats101	abc7	Blah blah	Mixed	Navigation/Design	No
38	Cats101	abc7	Blah blah	Mixed	Activities/Content	Yes
39	Cats101	abc7	Blah blah	Mixed	Live Sessions/Videos	Yes
40	Cats101	abc7	Blah blah	Mixed	Groupwork	Yes
41	Cats101	abc7	Blah blah	Mixed	Reading List	No
Sheet1