3

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

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.