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
If by "add data labels for each data point" you mean "adding labels from A column to each individual point" - then that is not possible. you can add only values as a label for each data point.
As for "two independent axis" - this can be easily achieved by adding new series from another column. All the above would look like:
From there you can add trendlines or customize each individual data point by changing color, size and shape:
If you will re-shape your table a bit you could do this:
Of course, if you really want so you could add custom label per each data point manually by inserting a drawing:
demo sheets: one, two
Best Answer
The formulas that you use add a row for each "learner" question even if they haven't answers.
The easier solution is to use the filter feature to hide the rows having blank "learner" data blank.
Other alternatives are the use of built-in functions like QUERY and FILTER.