Google-sheets – How to calculate a value based on the row/col number of another cell value

google sheetsgoogle-apps-script

I am trying to build a spreadsheet where one sheet holds a list of names divided into groups and a second sheet should list all the names, sorted, with a number next to each name according to a calculation based on the first sheet.

Please see demo spreadsheet for a demo spreadsheet with the desired state.

Best Answer

I would go about solving it a bit differently. Advantage is that this little code will prepare the table at once.

Code

function tournament(range) {
  // create output array
  var output = [];

  // add header names
  output.push(["Name", "Number"]);

  // create table
  for(var i=2, iLen=range.length; i<iLen; i++) {
    for(var j=0, jLen=range[0].length; j<jLen; j++) {
      // push letter, ring number * 100 + tournament into array
      output.push([range[i][j], (range[0][j] * 100) + (i-1)]);
    }
  }

  // return table
  return output;
}

Screenshot

data
enter image description here

result
enter image description here

Example

I've created an example file for you: Tournament

Related Topic