Summarize Survey into Pivot Table

[rdanella]

Hello!

I have a 5 question survey that I need to summarize by count and create into a pivot table. The first 4 questions have the same answers (exceeds/meets/below expectations/not observed), while the 5th question is a yes or no.

I need the output to look something like the below image.

Thank you!

[DataNath]

@rdanella can you provide some data so we can see the starting structure? Fairly simple request unless there's any unexpected transformation/cleansing you haven't mentioned.

[rdanella]

Of course! Below is how the file is outlined.

Team Member	Team	Q1	Q2	Q3	Q4	Q5
Person 1	Team A	Exceeds Expectations	Exceeds Expectations	Meets Expectations	Meets Expectations	Yes
Person 2	Team A	Meets Expectations	Exceeds Expectations	Meets Expectations	Exceeds Expectations	Yes
Person 3	Team A	Exceeds Expectations	Not Observed	Meets Expectations	Meets Expectations	Yes
Person 4	Team B	Exceeds Expectations	Below Expectations	Exceeds Expectations	Meets Expectations	No
Person 5	Team B	Exceeds Expectations	Meets Expectations	Below Expectations	Not Observed	No
Person 6	Team B	Meets Expectations	Meets Expectations	Below Expectations	Exceeds Expectations	Yes
Person 7	Team C	Meets Expectations	Below Expectations	Not Observed	Exceeds Expectations	No
Person 8	Team C	Meets Expectations	Exceeds Expectations	Not Observed	Below Expectations	No
Person 9	Team C	Meets Expectations	Meets Expectations	Meets Expectations	Below Expectations	No
Person 10	Team D	Below Expectations	Meets Expectations	Exceeds Expectations	Not Observed	Yes
Person 11	Team D	Below Expectations	Exceeds Expectations	Exceeds Expectations	Exceeds Expectations	Yes
Person 12	Team D	Not Observed	Exceeds Expectations	Exceeds Expectations	Exceeds Expectations	Yes

[DataNath · Accepted solution]

Hey @rdanella, here's one way you can do it.

1) Transpose everything into Name/Value where we have the answers and questions numbers next to each other

2) Assign a dummy value of 1 next to each record that we can sum

3) Group on the answers, using question numbers as headers and sum as the aggregation when doing the final Cross Tab

[rdanella]

this was perfect thank you!