Managing poll answer in a spreadsheet: grouping same questions

Question

I have a spreadsheet created from a poll with 3 columns: unique id, question, answer (a numeric value)

Data repeats several times for each user like this:

User ID	Questions text	Answers
ID1	question 1	AnswerID1
ID1	question 2	AnswerID1
ID1	question 3	AnswerID1
ID2	question 1	AnswerID2
ID2	question 2	AnswerID2
ID2	question 3	AnswerID2

Ideally I'd like to group all question 1, with all different answers on the same row or column, so it's easier to make a graph to show results. Like this:

Question 1	Question 2	Question 3
AnwerID1	AnwerID1	AnwerID1
AnwerID2	AnwerID2	AnwerID2

Any suggestion how it's the most efficient way to do this?

Answer 1

Welcome!

Such a table transformation is not very complicated, it can be done with three simple formulas.

Put the formula ={"User ID";UNIQUE(A2:A)} in cell E1 and you will get a column with the IDs of all users who completed the survey.

Now put the formula =TRANSPOSE(UNIQUE(B2:B)) in cell F1 and you will get a row with a list of all the questions - headers of columns for result table.

In cell F2 write the formula

=ARRAYFORMULA(TEXTJOIN(CHAR(10),1,IF(($E2=$A$2:$A)*(F$1=$B$2:$B),$C$2:$C,"")))

This will return all answers from the first user to the first question. Yes, usually there is only one answer for each question in your original table. But believe me, this will not always be the case - someday you will encounter a situation where the user will have two or more answers to one question. This formula will allow you to see them all.

Now just drag this F2 cell to the right to the end of the question list, and then the entire formula row down to the end of the user list.