4

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?

  • 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:

  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 '15 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. – ttarchala Aug 18 '17 at 14:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.