Forum Discussion
Formatting Survey Results in Excel
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 ve...
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.