13

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?

2
  • 5
    @TomWoodward No, it's a different one. offa wants a Cartesian product of two sets, not row-by-row concatenation.
    – user79865
    Commented 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. Commented Nov 15, 2020 at 0:09

5 Answers 5

13

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
2
  • thanks for the answer and for the explanations. Works well.
    – offa
    Commented Nov 16, 2015 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
    Commented Aug 18, 2017 at 14:26
8

Update March 2024

Google Sheets nowadays has tocol() and torow() functions that lets you avoid the 50,000 character limitation that bugs solutions that use join(). Use this pattern:

=arrayformula(split(tocol(A2:A4 & "→" & torow(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 an ignore parameter of 1 with the functions, like this:

=arrayformula(split(tocol(tocol(A2:A, 1) & "→" & torow(B2:B, 1)), "→"))

This pattern, however, still hard-codes the number of columns, and starts to become unwieldy already when there are three columns to combine:

=arrayformula(split(tocol(tocol(A2:A, 1) & "→" & torow(B2:B, 1)) & "→" & torow(C2:C, 1), "→"))

Further, these formulas use text string manipulation where split() may cause side effects such as converting the text string 1 2 3 to the date 2 January 2003 and other such unwanted transformations.

To avoid those side effects and implement a true n-ary Cartesian product, use a more dynamic formula such as the one below. It takes a range of N columns and gives all combinations of their non-blank values, column-wise:

=let( 
  table, A2:C, 
  blank, iferror(1/0), 
  first_, lambda(array, tocol(choosecols(array, 1), 1)), 
  rest_, lambda(n, choosecols(table, sequence(1, columns(table) - n, n + 1))), 
  wrap_, lambda(array, wrapCount, wraprows(tocol(array, 1), wrapCount)), 

  cartesian_, lambda(a, b, wrap_( 
    byrow(a, lambda(row, 
      reduce(blank, sequence(rows(b)), lambda(acc, i, 
        { acc, row, chooserows(b, i) } 
      ) ) 
    ) ), 
    columns(a) + columns(b) 
  ) ), 

  iterate_, lambda( 
    self, a, b, if(iserror(b), a, 
      self(self, cartesian_(a, first_(b)), rest_(columns(a) + 1)) 
    ) 
  ), 

  iterate_(iterate_, first_(table), rest_(1)) 
)

The formula uses recursion and will work with any number of columns:

source data
a 1 X
b 2 Y
c
× Cartesian
a 1 X
a 1 Y
a 2 X
a 2 Y
b 1 X
b 1 Y
b 2 X
b 2 Y
c 1 X
c 1 Y
c 2 X
c 2 Y
1
0

Update November 2022

I tried to apply the solutions above, both had their own drawbacks. The first solution has a 50.000 character limitation, and the second solution had performance issues when using the Query function at scale.

So I've updated the last solution to be open-ended through the OFFSET and COUNTA formulas which should be performing better.

=arrayformula( split( flatten( offset(A2,0,0,COUNTA(A2:A)) & "µ" & transpose(offset(B2,0,0,COUNTA(B2:B)) )), "µ" ) )

Please note: this solution will create problems if you have empty cells in your list. For example this will not work with a data set that looks like:

a   1 
b   2
    3 
c   4

Hopefully this will be useful for others as well.

1
  • 1
    Please cite the answers, as what is "above" or "before" may change over time or the answers may disappear altogether Commented Nov 14, 2022 at 10:53
0

This is known as the outer product. You may do so fairly straightforwardly, without resorting to weird SPLIT special characters, nor limits, nor performance issues, as follows:

Quick, inelegant solution:

Assuming your data is in A3:A5 and B3:B7... (see elegant solution for a better way)

={
  FLATTEN(

    MAP(A3:A5, LAMBDA(x,
      MAP(TRANSPOSE(B3:B7), LAMBDA(y,
        x
      ))
    ))

  ), 
  FLATTEN(

    MAP(A3:A5, LAMBDA(x,
      MAP(TRANSPOSE(B3:B7), LAMBDA(y,
        y
      ))
    ))

  )
}

Explanation: This uses the fact that FLATTEN's order (row-major order) is the same, so you can flatten the x's and the y's separately, and be confident that when you zipper them back together with {xi,yi} they will be matched correctly.

Of course you should not use a range like A3:A, since you'd get 10000 blank entries, which would then get multiplied by 10000 B3:B entries, to give you 100,000,000 entries and make your sheet slow...

Elegant solution:

Assuming your data is in A3:A and B3:B...

Define a Named Function flatouter2d(as,bs,f):

=LAMBDA(bsT,

  FLATTEN(
    MYMAP1(as, LAMBDA(a,
      MYMAP1(bsT, LAMBDA(b,
        f(a,b)
      ))
    ))
  )

)(TRANSPOSE(bs))

tip: The builtin function MAP may SOMETIMES work fine, but will fail if your matrix is small (i.e. if you only had a or only had 1 in OP's example, they'd get an error). To workaround this bug with Google Sheets, you can define a Named Function MYMAP1(xs,f)=IF( (ROWS(xs)<>1)+(COLUMNS(xs)<>1), MAP(xs, f), f(xs) ).

Then define four variables, write a one-liner expression, and you're done; this is what you paste into your cell:

=LAMBDA(as,bs,takeA,takeB,

   { flatouter(as,bs,takeA) , flatouter(as,bs,takeB) }

)(
  NONBLANKS(A3:A),
  NONBLANKS(B3:B),
  LAMBDA(a,b, a),
  LAMBDA(a,b, b)
)

(you can tell how this would be written like as = NONBLANKS(A3:A); bs=NONBLANKS(...); takeA=LAMBDA(...) in a more imperative programming language)

where by NONBLANKS(xs) we mean FILTER(xs, xs<>"") (you can define another Named Function, or type it out substituting your range for each occurrence of xs).

In conclusion, the following one-liner { flatOuter2d(as,bs,takeA) , flatOuter2d(as,bs,takeB) } will do the trick in a performant way. It is a bit verbose unless you add a few Named Functions.

Addendum:

If you don't want to add some Named Functions, you can still squeeze it into a single cell if you really wanted to:

=LAMBDA(nonblanks,MYMAP1,
LAMBDA(as,bs,takeA,takeB,flatOuter2d,

   { flatOuter2d(as,bs,takeA) , flatOuter2d(as,bs,takeB) }

)(
  NONBLANKS(A3:A),
  NONBLANKS(B3:B),
  LAMBDA(a,b, 
    a
  ),
  LAMBDA(a,b, 
    b
  ),
  LAMBDA(as,bs, f,
    LAMBDA(bsT,

      FLATTEN(
        MYMAP1(as, LAMBDA(a,
          MYMAP1(bsT, LAMBDA(b,
            f(a,b)
          ))
        ))
      )

    )(TRANSPOSE(bs))
  )
)
)(
  LAMBDA(xs, 
    FILTER(xs, xs<>"")
  ),
  LAMBDA(xs,f, 
    IF( (ROWS(xs)<>1)+(COLUMNS(xs)<>1),
      MAP(xs, f),
      f(xs)
    )
  )
)

Then if you really only cared about this problem, you could shorten it a bit, but I wouldn't recommend it. I'd define MYMAP1 on principle as a Named Function, so I omit it below:

=LAMBDA(nonblanks,
LAMBDA(as,bs,flatOuterCol,

   MAP({1,2}, LAMBDA(col, 
     flatOuterCol(as,bs, col)) )

)(
  NONBLANKS(A3:A),
  NONBLANKS(B3:B),
  LAMBDA(as,bs, col,
    LAMBDA(bsT,

      FLATTEN(
        MYMAP1(as, LAMBDA(a,
          MYMAP1(bsT, LAMBDA(b,
            IF(col=1,a,b)
          ))
        ))
      )

    )(TRANSPOSE(bs))
  )
)
)(
  LAMBDA(xs, FILTER(xs, xs<>"")),
)

(I defined flatouter2d recently as a helper function to do convolutions with spreadsheet formulas.)

0

This is a "one-liner".

=MAP(          {0,1},          LAMBDA(x,

  FLATTEN(MAP( {"a";"b";"c"},  LAMBDA(a,

    MAP(       {1,2},          LAMBDA(n,

      IF(x=0, a, n)

    ))
  )))
))

The idea is that you 'mappend' aka. 'flatmap'* aka. 'concatmap' i.e. FLATTEN(MAP( to keep all our things in one columns, and at the end you MAP( to splay things onto multiple rows. Keeping a loop structure is nice since it lets one refer to appropriate elements or indices as your use case may generally require.

  • (or whatever it's called in your parlance, namely, the operation of 1) taking the pairs of the cartesian outer product, then 2) considering them as a 1d set ........ rather than just simply 1) considering the pairs as a 2d set. Namely, flatmap would be like [x*y for x in X's for y in Y's] in Python, as opposed to [[x*y for x in X's...] for y in Y's])

This interestingly seems to work even if the arrays are of size 1, though I haven't proved it always works.

In general, the formula should be something like this: If you want general "block matrix"-style stuff where you can refer to everything in a closure, then you can do this pattern:

=LET(
  Xs, {"x","y","z"},
  Is, {0,1},
  
  Ns, {1,2},
  As, {"a";"b";"c"},

WRAPROWS(

FLATTEN(MAP(Xs,LAMBDA(x,
  FLATTEN(MAP(Is,LAMBDA(i,

    FLATTEN(MAP(Ns, LAMBDA(n,
      MAP(As, LAMBDA(a,

        a&n&x&i

      ))
    )))
  )))
)))

,COLUMNS(Xs)*COLUMNS(Is))
)

and use whatever IF/IFS logic in the inner 'loop'. This will give you access to every variable 'index' in the block matrix, e.g.:

a1x0    a1y0    a1z0    a1x1    a1y1    a1z1
a2x0    a2y0    a2z0    a2x1    a2y1    a2z1
b1x0    b1y0    b1z0    b1x1    b1y1    b1z1
b2x0    b2y0    b2z0    b2x1    b2y1    b2z1
c1x0    c1y0    c1z0    c1x1    c1y1    c1z1
c2x0    c2y0    c2z0    c2x1    c2y1    c2z1

Of course, you can resort to indexing, which you might need in more complicated cases. The below example should be extendable to most use cases. Here, we could (if we wanted to) access ni in the inner loop (second argument of the lambda passed into forWithIndex) to refer to the index (which in this case is the same as the element, but might not be). This design is equivalent to Python's for x,i in enumerate(...]). The below final version does away with asymmetric ; rows vs columns and is rewritten in a way that is a bit easier to understand.

Final Version

The example code below generates the block matrix [A x N] x [X x I], or said another way [ [... for a in A for n in N] for x in X for i in I ]. One may use IF statements in the inner part of the loop to reference values (use for and first argument of lambda), or alternatively, pairs of values and indices (use forWithIndex and first and second argument of lambda).

You can extend this to do something like Python's ranges by using SEQUENCE(length) or SEQUENCE(1,length). You must add the appropriate *COLUMNS(...) on the last line if adding another dimension which multiplies the cardinality of the columns (another for in the outer set of fors). You can of course also delete the "for loops" (delete the corresponding )) and, if a column, the corresponding term of *COLUMNS(...))

=LET(
  Xs, {"x","y","z"},
  Is, {0,1},
  
  Ns, {1,2},
  As, {"a","b","c"},
  
  for,          LAMBDA(list,f, FLATTEN(MAP(list, f))),
  forWithIndex, LAMBDA(list,f, FLATTEN(MAP(list,SEQUENCE(1,COLUMNS(list)), f))),
  
WRAPROWS(

for(Xs,LAMBDA(x,
  for(Is,LAMBDA(i,

    forWithIndex(Ns, LAMBDA(n,ni,
      for(As, LAMBDA(a,


        "("&a&n&", "&x&i&")"

      ))
    ))
  ))
))

,COLUMNS(Xs)*COLUMNS(Is))
)

output:

(a1, x0)    (a1, y0)    (a1, z0)    (a1, x1)    (a1, y1)    (a1, z1)
(a2, x0)    (a2, y0)    (a2, z0)    (a2, x1)    (a2, y1)    (a2, z1)
(b1, x0)    (b1, y0)    (b1, z0)    (b1, x1)    (b1, y1)    (b1, z1)
(b2, x0)    (b2, y0)    (b2, z0)    (b2, x1)    (b2, y1)    (b2, z1)
(c1, x0)    (c1, y0)    (c1, z0)    (c1, x1)    (c1, y1)    (c1, z1)
(c2, x0)    (c2, y0)    (c2, z0)    (c2, x1)    (c2, y1)    (c2, z1)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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