Google Sheets – Compute Permutations Multiplication Table

google sheetsgoogle-apps-script

I have a table that is structured like this.

table1

I would like to transform the table into the permutations of the table if we treated like a multiplication table. Into a list that looks like this.

enter image description here

Please note I am using a multiplication table as an example. The actual data I am parsing is random. The reason I choose to use a multiplication table data is the lookup method for my table data is performed by the same method. e.g where you check where two different inputs on the x-axes and y-axes and where they converge gives you the value.

Best Answer

  • You want to convert the values of above image to the values of below image.

If my understanding is correct and you use Google Apps Script, how about this sample script? I think that there are several solutions for your situation. So please think of this as just one of them.

  • In this sample script, it supposes that the initial values are put to the cells of "A1:D4" in the active sheet. And the result is put to the cells of "A6:C23".
  • The point of this sample script is that all patterns are retrieved by transposing the values.

Sample script:

var sheet = SpreadsheetApp.getActiveSheet();
var values = sheet.getRange("A1:D4").getValues(); // Retrieve the initial values

var ar = [];
for (var loop = 1; loop >= 0; loop--) {
  var temp = [];
  for (var i = 1; i < values.length; i++) {
    for (var j = 1; j < values[i].length; j++) {
      temp.push([values[0][j], values[i][0], values[i][j]]);
    }
  }
  ar[loop] = temp;
  values = values[0].map(function(_, i) {return values.map(function(f, j) {return f[i]})}); // Transpose the initial values.
}
var res = Array.prototype.concat.apply([], ar);

sheet.getRange("A6:C23").setValues(res); // Put the result

Result:

enter image description here

  • "A1:D4" is the initial values.
  • "A6:C23" is the result values.

Note:

  • This is a simple sample script. So please modify it for your situation.

If I misunderstood your question, I apologize.

Related Topic