Forms Data Manipulation In Google Sheets

Question

I'm trying to develop a solution using Google Forms with Google Sheets that will collect Google Forms data and then automatically divide that one form submission's data into multiple rows of data in another sheet.

I've learned how to QUERY data (thanks to many of you on here) from one sheet to another, but I don't know how to automatically divide 1 row of data into multiple rows.

Here is a YouTube video outlining the problem in a visual manner (I will not delete it).

Basically, I have one Google form that collects the following.

1. Teacher Information (header data)
2. Report Name (header data)
3. Class Information (header data)
4. Student#1 Data (record data: Multiple Form Fields)
5. Student#2 Data (record data: Multiple Form Fields)
6. Student#3 Data (record data: Multiple Form Fields)
7. Student#4 Data (record data: Multiple Form Fields)
8. Student#5 Data (record data: Multiple Form Fields)

I need an automated way to convert that single form response (1 row) into multiple rows (5 rows) each with the same 'header data' but with unique record date.

EXAMPLE Form Data

Header Data  -  Record Date (Student #1)  -  Record Date (Student #2)  -  Record Date (Student #3)  -  Record Date (Student #4)  -  Record Date (Student #5)

EXAMPLE Desired Manipulated Data

    Header Data  -  Record Date (Student #1)
    Header Data  -  Record Date (Student #2)
    Header Data  -  Record Date (Student #3)
    Header Data  -  Record Date (Student #4)
    Header Data  -  Record Date (Student #5)

Answer 1

Short answer

1. Add a new sheet.
2. Add the following formulas to the new sheet

Cell A2 -> Headers

=OFFSET('Form responses 1'!$A$2,ceiling((row()-1)/5,1)-1,0)  

This could be repeated several times, one for each header column, just change the last parameter accordingly.

Cell B2 -> data

=OFFSET('Form responses 1'!$A$2,ceiling((row()-1)/5,1)-1,mod(row()-2,5)*2+1,1,2)

Fill down as necessary

Explanation

• OFFSET() is used to get the values from the source data.
• 'Form responses 1'!$A$2 is the start point to count columns and rows.
• row() is used to increment the row and column iterators.
• ceiling((row()-1)/5,1), row iterator, is used to set the row from the source data.
• mod(row()-2,5)*2+1, column iterator, is used to set the column from the source data.
• In the data formula the last parameter, indicates the number of data columns.

In case that are several "header data" columns, add 1 for each of them. I.E. if there are five "header data" columns, and six "data columns" the column iterator should be

mod(row()-2,5)*6+5

The final data formula to be added to the F2 cell is:

=OFFSET('Form responses 1'!$A$2,ceiling((row()-1)/5,1)-1,mod(row()-2,5)*6+5,1,6)

In the row and column iterators, 5 is used because there are 5 columns for "record data".

Demo

Spreadsheet

Answer 2

This answer has refactored versions of the formulas on my previous answer

Short answer

1. Add a new sheet.
2. Add the following formulas to the new sheet

Cell A2 -> Headers

=OFFSET(responses!$A$2,ceiling((row()-row($A$1))/$M$1,1)-1,column()-column($A$1),1,COUNTA($A$1:$D$1))

Cell E2 -> data

=OFFSET(responses!$A$2,ceiling((row()-row($A$1))/$M$1,1)-1,mod(row()-(row($A$1)+1),$M$1)*COUNTA($E$1:$J$1)+COUNTA($A$1:$D$1),1,COUNTA($E$1:$J$1))

Fill down as necessary

Explanation

• OFFSET() is used to get the values from the source data.
• 'Form responses 1'!$A$2 is the start point to count columns and rows.
• row() is used to increment the row and column iterators.
• ceiling((row()-row($A$1))/$M$1,1), row iterator, is used to set the row from the source data.
• In the row and column iterators, $M$1 is used to hold the number of "record data sets".
• COUNTA($A$1:$D$1) counts the number of column headers
• mod(row()-(row($A$1)+1),$M$1)*COUNTA($E$1:$J$1)+COUNTA($A$1:$D$1), column iterator, is used to set the column from the source data.
• In the data formula COUNTA($E$1:$J$1), indicates the number of data columns.

Demo

Spreadsheet

Answer 3

Using a single, simple formula

You can achieve what you want in a very "clean way" using (as indicated by your efforts in both your question and your video) a single, simple QUERY formula where the Header Data are in columns A-D and the Records Data are sectioned in columns E-AH.

=query({responses!A:J;responses!A2:D,responses!K2:P;responses!A2:D,responses!Q2:V;responses!A2:D,responses!W2:AB;responses!A2:D,responses!AC2:AH},"select * where Not Col5='' order by Col1")

Here is a working SAMPLE SPREADSHEET.

Formula sections:

• responses!A:J setting the headers (A-D + first set of answers E-J)
• responses!A2:D,responses!K2:P,responses!A2:D,responses!Q2:V and so forth, looping through answers
• "select * where Not Col5='' order by Col1" the actual query

Please pay attention to the , and ; which swap places depending on your locale.

PS: I know it is an old question but in your YouTube comment you mention you haven't found an answer yet. If the answer works accept it so as to help others as well. (Why vote?)

EDIT: by mistake the link to my sheet was private. It is now fixed. Thank you for letting me know.