4

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)
3

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

  • Ruben, Thank you for your help. I've experimented with your answer, but I have 1 question and do need a little more assistance. 1st, is it possible to 'automate' this? I'd like the data to be segmented into rows 'as' the forms are submitted. 2nd, I'm not able to get the data to fill down properly. I can get the same info to repeat over and over again, but I can't get the header data to repeat, while the student data increments row by row. Example form responses - goo.gl/fT5JhD Can you please offer additional assistance? Thanks! – Mr. B Jan 18 '16 at 18:41
  • Here is the responses tab - goo.gl/wyzu15. Here is the tab to my implementation of your instructions - goo.gl/zHPK7i. Here is an example of what I want the data in ManipulatedResponses tab to look like - goo.gl/cazGZQ. The example(mainuplated responses) tab has data from two form submissions, but the data has been manipulated to look like ten form submissions. Also, is it possible to do this automatically? Or will it need to be done by manually filling the formula down the column? Thanks! – Mr. B Jan 19 '16 at 0:08
  • Column "F" is working, but I need it displayed in rows, not columns. – Mr. B Jan 19 '16 at 0:14
  • I updated the answer. Here is a copy of your file with the corrected formulas: goo.gl/Ozs11R – Rubén Jan 19 '16 at 1:13
  • 1
    Thank you Ruben! You're help is going to save my school thousands of dollars a year in printing and man hour costs. You're help is really making a difference, THANK YOU! This process use to be done with THOUSANDS of photocopies and dozens of hours spent organizing those photocopies into folders for counselors and VP's. It can now be done paperless and in seconds! THANK YOU again! – Mr. B Jan 19 '16 at 3:33
1

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

protected by Community Dec 17 '18 at 3:41

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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