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?
Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this communityI 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?
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)))
The formula in C:
The formula in D:
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
)
)