1

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!

enter image description here

Peanuts
  • 2,641
  • 6
  • 29
  • 34
  • 1
    Won't work with [tag:google-spreadsheet] but have a look at [this](https://stackoverflow.com/questions/31472816/expanding-column-cells-for-each-column-cell/31594569#31594569). –  Jan 01 '18 at 22:09

3 Answers3

2

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

  • This worked wonders, thanks! It generates the single numbers, as well as all couples and all trios, plus the 4 characters combinations. Which is even better for my purpose, that was to spread all the combinations. For some reason, it also repeats 4 times each result -but this is fine and easy to correct-. – Peanuts Jan 01 '18 at 23:40
  • 1
    The repetition was because I put A1:D9 instead of A2:D9, fixed –  Jan 01 '18 at 23:57
1

Try

=arrayformula(if(row(A:A)>4096,"",int((row(A:A)-1)/512)+1&mod(int((row(A:A)-1)/64),8)+1&mod(int((row(A:A)-1)/8),8)+1&mod(int((row(A:A)-1)/1),8)+1))

(needs at least 4096 rows in the sheet).

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
0

Update May 2023

Sequences can be generated quite effectively with sequence(), filter() and regexmatch(), like this:

=let( 
  numbers, sequence(8888 - 1111 + 1, 1, 1111), 
  filter(numbers, not(regexmatch(to_text(numbers), "0|9"))) 
)
doubleunary
  • 13,842
  • 3
  • 18
  • 51