0

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?

1 Answer 1

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.

2
  • Thank you!! That worked very well. I had to adapt the third formula you wrote changing the commas to semicolons, but it worked well. Also, I adapted it to use them in a new sheet, so I had to put the sheet references to ranges, but that was easy. Thank you
    – ezellohar
    Dec 6, 2022 at 10:01
  • @ezellohar Please read What should I do when someon answers my question?
    – Rubén
    Jan 29 at 1:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.