4

I found many solutions online, but they are generating combinations as combined text. I want to generate them into separate columns like on picture below.

Formula should be in C1, based on columns A and B. New rows in A and B will populate new rows (combinations) in C and D.

Manual example

Simon
  • 43
  • 5
  • See the second example in the documentation for the FLATTEN function described as a Cartesian Product: https://support.google.com/docs/answer/10307761?hl=en – MattKing Dec 25 '21 at 14:54
  • If you need to combine more than two columns, see [Generate all possible combinations for Columns(cross join or Cartesian product)](https://stackoverflow.com/a/76126924/13045193). – doubleunary May 03 '23 at 14:27

1 Answers1

2

Try in C1

=arrayformula(SPLIT(flatten(offset(A1,,,COUNTA(A:A))&"|"&transpose(offset(B1,,,COUNTA(B:B)))),"|"))

enter image description here

Mike Steelson
  • 14,650
  • 2
  • 5
  • 20