Generate all combinations of 1 column

Question

I'd like to create a function for Google Sheets that allows me to list all possible combinations of the numbers in column A like the picture, but not just 4 values, I can have more then 20 in colunm A.

example

Any help is very much appreciated!

note.: I tested a few solutions, the one that seemed to be closer is here, but is giving me this: "Error: You don't have permission to call setValues (line 35)." (the original error is in portuguese).

note 2.: The sugestion on Sum of all number combinations excluding repetition Excel give me error on the formula and shows only for 4 values.

What you mean by "create a function"? Do you mean an spreadsheet formula based on built-in functions or you want to create a custom function by using Google Apps Script? Besides answering these questions please add a brief description of your search/research efforts. — Rubén, Sep 11 '18 at 20:22
Hello, can be either of the two options. I tested a few, the one that seemed to be closer is giving me error, so I thought to ask if there is another option. — Ricardo000, Sep 11 '18 at 20:35
This is one that I tested. [link](https://productforums.google.com/forum/#!msg/docs/oxaFYooEiTE/HW6ICHoHfzAJ) — Ricardo000, Sep 11 '18 at 20:42
https://stackoverflow.com/questions/tagged/combinations+google-spreadsheet — TheMaster, Sep 12 '18 at 00:31
Possible duplicate of [Sum of all number combinations excluding repetition Excel](https://stackoverflow.com/questions/50057104/sum-of-all-number-combinations-excluding-repetition-excel) — TheMaster, Sep 12 '18 at 00:32

score 0 · Answer 1 · answered Apr 28 '23 at 08:12

The number of combinations, or more precisely, unique n-tuples, grows fast as the number of values increases. With 20 values, you get about one million tuples which together contain about 20 million elements in the matrix. That is too much to handle comfortably in a spreadsheet.

On the other hand, with 15 values, you only need to generate about 30,000 tuples and about 650,000 elements which is much more manageable. To do that, first calculate the Cartesian product, then filter, sum and format the result, like this:

=let( 
  values, torow(A2:A, 1), 
  zeros, map(values, lambda(_, 0)), 
  table, { zeros; values }, 
  blank, iferror(1/0), 
  first_, lambda(array, tocol(choosecols(array, 1), true)), 
  rest_, lambda(n, choosecols(table, sequence(1, columns(table) - n, n + 1))), 
  wrap_, lambda(array, wrapCount, wraprows(tocol(array, true), wrapCount)), 

  cartesian_, lambda(a, b, wrap_( 
    byrow(a, lambda(row, 
      reduce(blank, sequence(rows(b)), lambda(acc, i, 
        { acc, row, chooserows(b, i) } 
      ) ) 
    ) ), 
    columns(a) + columns(b) 
  ) ), 

  iterate_, lambda( 
    self, a, b, if(iserror(b), a, 
      self(self, cartesian_(a, first_(b)), rest_(columns(a) + 1)) 
    ) 
  ), 

  cartesian, iterate_(iterate_, first_(table), rest_(1)), 
  sums, byrow(cartesian, lambda(row, sum(row))), 
  expressions, byrow(cartesian, lambda(row, join(" + ", filter(row, row)))), 
  sort(unique(filter({ expressions, sums }, sums))) 
)

The question leaves it unclear whether individual values should be repeatable, and if so, how many times each value should be repeated at most. If you want to repeat each value at most twice, use a values list like 1, 1, 2, 2, 3, 3, 4, 4.

Here's an example of the results with 4 values:

Values	Descriptions	Sums
1	1	1
2	1 + 2	3
3	1 + 2 + 3	6
4	1 + 2 + 3 + 4	10
	1 + 2 + 4	7
	1 + 3	4
	1 + 3 + 4	8
	1 + 4	5
	2	2
	2 + 3	5
	2 + 3 + 4	9
	2 + 4	6
	3	3
	3 + 4	7
	4	4

Generate all combinations of 1 column

1 Answers1