This might be possible with some ingenious queries, but to me it seemed easier to write a script (entered via Tools > Script editor). It updates the sheet as new data is entered. Some explanation:
- The output is set to begin at cell F1, as in your example. This can be changed by changing the row number and column number
1, 6
near the end of the script.
- The number of desired columns,
newColumns
, is inferred by going through column A until there is an empty cell in it. In your case, A4 is the first empty cell, which signifies you have 3 rows of data, so there will be 3 columns.
- The script assumes there is an empty column to the right of the input, which serves as an indication that the input ends there.
The logic is pretty straightforward: grabbing and pushing data around.
function onEdit() {
var i,j,k;
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var range = sheet.getDataRange();
var v = range.getValues();
i = 0;
while (v[i][0]) {i++;}
var newColumns = i;
var row = [];
for (k=0; k<newColumns; k++) {
row.push(v[k][0]);
}
var values = [row];
for (i=0; i<v.length-newColumns+1; i++) {
if (v[i][0] == 'Date') {
for (j=1; j<v[i].length; j++) {
if (!v[i][j]) {break;}
row = [];
for (k=0; k<newColumns; k++) {
row.push(v[i+k][j]);
}
values.push(row);
}
}
}
var newRange = sheet.getRange(1, 6, values.length, newColumns);
newRange.setValues(values);
}
If you want to put the output on another sheet, replace the command
var newRange = sheet.getRange(1, 6, values.length, newColumns);
with
var targetSheet = ss.getSheetByName('Your target sheet name');
var newRange = targetSheet.getRange(1, 6, values.length, newColumns);
(You may also want to change 1, 6 to something else like 1, 1 now that the output has its own sheet.)