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