Google-sheets – How to transpose multiple rows into a column with multiple matched outcomes

google sheets

I have this data set:

http://i.stack.imgur.com/6ilTZ.jpg

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

http://i.stack.imgur.com/PHHEq.jpg

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

http://i.stack.imgur.com/UIrjE.jpg

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

Best Answer

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

Related Topic