Combine two columns into a list of all possible combinations of entries

Question

I have two columns such as:

a   1 
b   2 
c 

and I need to combine them like:

a   1
a   2
b   1
b   2
c   1
c   2

Is it possible with a formula?

Answer 1

Although this is a special case of In a Google Spreadsheet, show all combinations for a selection of columns I think it's good to have a simpler answer specifically for the case of two columns. The technical term is "Cartesian product of two sets".

I use the same method as Rubén, which requires a character that does not appear in the column entries. Rubén used comma in his example. I prefer something more exotic, e.g. char(9999), which is a pencil: ✏.

Here are the formulas for joining columns A and B in a Cartesian product:

In cell C1:

=transpose(split(join("", arrayformula(rept(filter(A1:A, len(A1:A))&char(9999), counta(B1:B)))), char(9999)))  

In cell D1:

=transpose(split(rept(join(char(9999), filter(B1:B, len(B1:B)))&char(9999), counta(A1:A)), char(9999)))

Explanation

The formula in C:

1. takes nonempty entries in A
2. puts ✏ next to each
3. repeats each such combo as many times as there are entries in B
4. joins them into a✏a✏b✏b✏c✏c✏
5. splits by pencil character into a row a a b b c c
6. transposes the row so that it becomes a column

The formula in D:

1. takes nonempty entries in B
2. joins them, separated by ✏
3. repeats the entire string as many times as there are entries in A, getting 1✏2✏1✏2✏1✏2✏
4. splits by pencil character into a row 1 2 1 2 1 2
5. transposes the row so that it becomes a column

Answer 2

Update May 2021

Google Sheets nowadays has a flatten() function that lets you avoid the 50,000 character limitation that bugs the previous answer. Use this pattern:

=arrayformula( split( flatten( A2:A4 & "µ" & transpose(B2:B3) ), "µ" ) )

In the event you do not know the number of rows in the source data in advance, and need to use open-ended range references, use a query() wrapper like this:

=arrayformula( 
  query( 
    split( 
      flatten( 
        A2:A & "µ" & transpose(B2:B) 
      ), 
      "µ" 
    ), 
    "where Col1 is not null and Col2 is not null", 
    0 
  ) 
)

Update April 2023

The recently introduced lambda functions let you implement a true n-ary Cartesian product without resorting to text string manipulation where split() may cause side effects such as converting the text string 1 2 3 to the date 2 January 2003. The formula below takes a range of N columns and gives all combinations of their non-blank values, column-wise:

=let( 
  table, A2:B, 
  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, 1), 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)) 
    ) 
  ), 

  iterate_(iterate_, first_(table), rest_(1)) 
)

The formula uses recursion and will work with any number of columns:

source data
a	1	X
b	2	Y
c

× Cartesian
a	1	X
a	1	Y
a	2	X
a	2	Y
b	1	X
b	1	Y
b	2	X
b	2	Y
c	1	X
c	1	Y
c	2	X
c	2	Y

Answer 3

Update November 2022

I tried to apply the solutions above, both had their own drawbacks. The first solution has a 50.000 character limitation, and the second solution had performance issues when using the Query function at scale.

So I've updated the last solution to be open-ended through the OFFSET and COUNTA formulas which should be performing better.

=arrayformula( split( flatten( offset(A2,0,0,COUNTA(A2:A)) & "µ" & transpose(offset(B2,0,0,COUNTA(B2:B)) )), "µ" ) )

Please note: this solution will create problems if you have empty cells in your list. For example this will not work with a data set that looks like:

a   1 
b   2
    3 
c   4

Hopefully this will be useful for others as well.

Answer 4

This is known as the outer product. You may do so fairly straightforwardly, without resorting to weird SPLIT special characters, nor limits, nor performance issues, as follows:

Quick, inelegant solution:

Assuming your data is in A3:A5 and B3:B7... (see elegant solution for a better way)

={
  FLATTEN(

    MAP(A3:A5, LAMBDA(x,
      MAP(TRANSPOSE(B3:B7), LAMBDA(y,
        x
      ))
    ))

  ), 
  FLATTEN(

    MAP(A3:A5, LAMBDA(x,
      MAP(TRANSPOSE(B3:B7), LAMBDA(y,
        y
      ))
    ))

  )
}

Explanation: This uses the fact that FLATTEN's order (row-major order) is the same, so you can flatten the x's and the y's separately, and be confident that when you zipper them back together with {x_i,y_i} they will be matched correctly.

Of course you should not use a range like A3:A, since you'd get 10000 blank entries, which would then get multiplied by 10000 B3:B entries, to give you 100,000,000 entries and make your sheet slow...

Elegant solution:

Assuming your data is in A3:A and B3:B...

Define a Named Function flatouter2d(as,bs,f):

=LAMBDA(bsT,

  FLATTEN(
    MYMAP1(as, LAMBDA(a,
      MYMAP1(bsT, LAMBDA(b,
        f(a,b)
      ))
    ))
  )

)(TRANSPOSE(bs))

tip: The builtin function MAP may SOMETIMES work fine, but will fail if your matrix is small (i.e. if you only had a or only had 1 in OP's example, they'd get an error). To workaround this bug with Google Sheets, you can define a Named Function MYMAP1(xs,f)=IF( (ROWS(xs)<>1)+(COLUMNS(xs)<>1), MAP(xs, f), f(xs) ).

Then define four variables, write a one-liner expression, and you're done; this is what you paste into your cell:

=LAMBDA(as,bs,takeA,takeB,

   { flatouter(as,bs,takeA) , flatouter(as,bs,takeB) }

)(
  NONBLANKS(A3:A),
  NONBLANKS(B3:B),
  LAMBDA(a,b, a),
  LAMBDA(a,b, b)
)

(you can tell how this would be written like as = NONBLANKS(A3:A); bs=NONBLANKS(...); takeA=LAMBDA(...) in a more imperative programming language)

where by NONBLANKS(xs) we mean FILTER(xs, xs<>"") (you can define another Named Function, or type it out substituting your range for each occurrence of xs).

In conclusion, the following one-liner { flatOuter2d(as,bs,takeA) , flatOuter2d(as,bs,takeB) } will do the trick in a performant way. It is a bit verbose unless you add a few Named Functions.

Addendum:

If you don't want to add some Named Functions, you can still squeeze it into a single cell if you really wanted to:

=LAMBDA(nonblanks,MYMAP1,
LAMBDA(as,bs,takeA,takeB,flatOuter2d,

   { flatOuter2d(as,bs,takeA) , flatOuter2d(as,bs,takeB) }

)(
  NONBLANKS(A3:A),
  NONBLANKS(B3:B),
  LAMBDA(a,b, 
    a
  ),
  LAMBDA(a,b, 
    b
  ),
  LAMBDA(as,bs, f,
    LAMBDA(bsT,

      FLATTEN(
        MYMAP1(as, LAMBDA(a,
          MYMAP1(bsT, LAMBDA(b,
            f(a,b)
          ))
        ))
      )

    )(TRANSPOSE(bs))
  )
)
)(
  LAMBDA(xs, 
    FILTER(xs, xs<>"")
  ),
  LAMBDA(xs,f, 
    IF( (ROWS(xs)<>1)+(COLUMNS(xs)<>1),
      MAP(xs, f),
      f(xs)
    )
  )
)

Then if you really only cared about this problem, you could shorten it a bit, but I wouldn't recommend it. I'd define MYMAP1 on principle as a Named Function, so I omit it below:

=LAMBDA(nonblanks,
LAMBDA(as,bs,flatOuterCol,

   MAP({1,2}, LAMBDA(col, 
     flatOuterCol(as,bs, col)) )

)(
  NONBLANKS(A3:A),
  NONBLANKS(B3:B),
  LAMBDA(as,bs, col,
    LAMBDA(bsT,

      FLATTEN(
        MYMAP1(as, LAMBDA(a,
          MYMAP1(bsT, LAMBDA(b,
            IF(col=1,a,b)
          ))
        ))
      )

    )(TRANSPOSE(bs))
  )
)
)(
  LAMBDA(xs, FILTER(xs, xs<>"")),
)

---

(I defined flatouter2d recently as a helper function to do convolutions with spreadsheet formulas.)