Formatting Survey Results in Excel

[NickMoore]

Hello!

I'm trying to find a way to reformat results of a survey that are currently being exported to a spreadsheet in multiple rows per respondent (1 row per question) so that we can provide a version with only one row per respondent (1 column per question). In it's simplest form, the problem is changing this table:

ID	Question	Response
1	1	yes
1	2	no
2	1	yes
2	2	yes

into this one:

ID	Question 1	Question 2
1	yes	no
2	yes	yes

This would need to be extendable so as to support an arbitrary number of IDs and questions, but the ID's are all unique, and the questions are always labeled the same way, and the response is always in the same column. Building the columns manually is not an issue, assuming I can just copy the same basic method and replace the question value.

Any ideas? Thanks!

[#2]

@NickMoore The most flexible solution would be to use Get&Transform Date (a.k.a. PowerQuery), provided you are on a Windows version of Excel. Though, if you have never used it before, it may be a bit overwhelming. In the attached example, I take your data table and pivot the "Question" column with "Response" as the 'non-aggregated' value.

Another solution would be to use the new FILTER and UNIQUE functions, provided your version of Excel supports it.

A third option would be to use 'good old' INDEX and MATCH. I inserted a helper column to the data table, combining the RespondentID and the Question number, in order to keep it fairly simple.

The latter two, however, are less dynamic and need some coping/pasting to display the results.