Google-sheets – How to split data across multiple rows when submitting from Google Form

google sheetsgoogle-forms

I have a google form that acts as an event registration form. I would like to add the ability for multiple people to be registered on the same response submission but then have a report with only the information for one person per row instead of having multiple people in the same row as occurs on the Form Responses sheet.

I found the Forms Data Manipulation In Google Sheets question and I believe that the solution should solve my problem but I can't seem to get the formulas to work. However, I'm having immense trouble editing the formulas that are mentioned in the above question.

I copy and paste them into the formula bar and when ever I try edit anything other than cell reference in the offset function my changes and rejected and reverted back to the original formula. I might be editing the row part of the formula because I don't quite think I understand how it works.

Here is a link to a simplified version of the form's spreadsheet. I should be able to modify the actual spreadsheet if I can understand how to modify the solution to the question mentioned above to work for the simplified spreadsheet. Any help an advice you can give me so I can do that is greatly appreciated.

Best Answer

The "Raw Responces" sheet of the OP has

  1. One column, timestamp. Its role is to be the "headers column"
  2. Three persons, two columns by person, name and price. Its role is to be the "data columns"

On the report sheet

  1. On M1 add the number of persons, 3.
  2. For the "header columns", on A2 add the following formula:
    =OFFSET('Raw Responces'!$A$2,ceiling((row()-row($A$1))/$M$1,1)-1,column()-column($A$1),1,COUNTA($A$1)).
  3. For the "data columns", on B2 add the following formula:
    =OFFSET('Raw Responces'!$A$2,ceiling((row()-row($A$1))/$M$1,1)-1,mod(row()-(row($A$1)+1),$M$1)*COUNTA($B$1:$C$1)+COUNTA($A$1),1,COUNTA($B$1:$C$1)).
Related Topic