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?
4
-
Duplicate of this one. – Tom Woodward Nov 15 '15 at 20:41
-
2@TomWoodward No, it's a different one. offa wants a Cartesian product of two sets, not row-by-row concatenation. – user79865 Nov 15 '15 at 20:43
6
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:
- takes nonempty entries in A
- puts ✏ next to each
- repeats each such combo as many times as there are entries in B
- joins them into a✏a✏b✏b✏c✏c✏
- splits by pencil character into a row a a b b c c
- transposes the row so that it becomes a column
The formula in D:
- takes nonempty entries in B
- joins them, separated by ✏
- repeats the entire string as many times as there are entries in A, getting 1✏2✏1✏2✏1✏2✏
- splits by pencil character into a row 1 2 1 2 1 2
- transposes the row so that it becomes a column
-
-
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. – ttarchala Aug 18 '17 at 14:26
