Managing poll answer in a spreadsheet: grouping same questions


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


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.

You may Also Like:

None found

Leave a Comment