If you add the following formula in cell D2
:
=ARRAYFORMULA(FILTER(C2:C;C2:C<>"")*1.2)
then it will take on the complete column, via the array. The FILTER
function, is to make sure no empty cells are taken into account.
I've added the result into the file you shared with us.
Short answer
- Add a new sheet.
- 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
Best Answer
With the following guidance, you should be able to retrieve the info:
Open the revision history:
If you select the desired version, then you can restore that version:
Copy the content of the sheet into the newly created spreadsheet.
I hope it works out !