How can I transpose multiple rows into a column with multiple matched outcomes?

I have this data set:

and I want to transpose it where the Date is in a Column then A is in the next Column and B on the next

I used this formula = Transpose(Index(\$B\$1:\$D\$11,match(F\$1,\$A\$1:\$A\$11,0))) but the outcome gives only the first table

What can I do to make my data look like this?

(so that whenever I would add a new week's data, my table would also change).

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