# 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]) {i++;}
var newColumns = i;
var row = [];
for (k=0; k<newColumns; k++) {
row.push(v[k]);
}
var values = [row];
for (i=0; i<v.length-newColumns+1; i++) {
if (v[i] == '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.)