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

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?

• @TomWoodward No, it's a different one. offa wants a Cartesian product of two sets, not row-by-row concatenation.
– user79865
Nov 15, 2015 at 20:43
• Here you can see another way to achieve this by using Script Add-ons. Hope you will find it useful. Nov 15, 2020 at 0:09

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
• thanks for the answer and for the explanations. Works well.
– offa
Nov 16, 2015 at 6:45
• It's a good trick but you're likely to run into a cumbersome limitation if you apply it to a large enough data set: the concatenation result cannot exceed 50.000 characters. Faced with the same problem, I ended up creating a custom function to perform a proper SQL JOIN-like operation on two arbitrary ranges/arrays. Aug 18, 2017 at 14:26

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

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.

• Please cite the answers, as what is "above" or "before" may change over time or the answers may disappear altogether Nov 14, 2022 at 10:53

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 `{`xi`,`yi`}` 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.

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<>"")),
)
``````