I have a Google form that asks for Name and up to 3 Score. The data is dumped into a sheet like this

enter image description here

But that data is not very convenient. I want to have the data like this...

enter image description here

Can I do that by adding a new sheet to the workbook, and some kind of LOOKUP formula? Or can I change the settings on the FORM to write up to 3 rows per submit?

  • can you share a copy of your sheet too? – user0 Apr 18 '19 at 20:29
  • @user0 sure, will do – John Henckel Apr 18 '19 at 20:30

={"Timestamp", "Name", "Score"; 
 IF('Form Responses 1'!C2:E<>"", TRANSPOSE(QUERY(TRANSPOSE({
 TO_TEXT('Form Responses 1'!A2:B)&"♦"}), , 999^99))&'Form Responses 1'!C2:E&"♠", ))
 , , 999^99)), , 999^99), "♠"))), "♦"), "where Col3 is not null", 0))}


demo spreadsheet

| improve this answer | |
  • 1
    awesome thanks! that works perfectly. – John Henckel Apr 18 '19 at 21:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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