Google Spreadsheets: generate all combinations of 4 columns with 8 rows each

Question

I'd like to create a function for Google Sheets that allows me to list all possible combinations of the numbers 1 to 8, concatenated 4 times (from 1111 to 8888, I think that is 8^4 = 4096).

(I add a screenshot for clarity).

So far I tried with:

=ArrayFormula(transpose(split(concatenate(A2:A9&B2:B9&C2:C9&D2:D9& char(9)),char(9))))

...but this gives me only 8 combinations: 1111, 2222, 3333, 4444, 5555, 6666, 7777, 8888.

I'm slightly bad at programming, specially with new languages, so any help is very much appreciated!

Answer 1

Here is a little custom function that creates all combinations of rows (it's just easier to write for rows):

function combinations(arr) {
  return arr.reduce(function(prod, row) {
    var out = [];
    for (i in row) {
      out = out.concat(prod.map(function(x) {
        return x.concat(row[i]);
      }));
    }
    return out;
  }, [[]]);
}

Using it as =combinations(A2:D9) would create 4^8 combinations, each of length 8, and that is not what you want. But it's easy enough to transpose:

=combinations(transpose(A2:D9))

The above function returns combinations as a rectangular array, so in your example the output would be 4 columns wide. If you want to join the combinations in one cell (so the output is a single column), use this modified version:

function joincombinations(arr) {
  return arr.reduce(function(prod, row) {
    var out = [];
    for (i in row) {
      out = out.concat(prod.map(function(x) {
        return x.concat(row[i]);
      }));
    }
    return out;
  }, [[]]).map(function(row) {
    return row.join("");
  });
}

Usage: =joincombinations(transpose(A2:D9))