7

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?

Improve this question
2
  • 5
    @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
  • Here you can see another way to achieve this by using Script Add-ons. Hope you will find it useful.
    – daniyalzade
    Nov 15 '20 at 0:09
10

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
Improve this answer
2
  • thanks for the answer and for the explanations. Works well.
    – offa
    Nov 16 '15 at 6:45
  • 1
    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
2

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 
  ) 
)
Improve this answer

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.