2

Suppose I have three columns importing data from elsewhere, like so:

Name Company Division
Adam Meta Ops
Betty Nvidia Sales
Carl OpenAI Development

The number of values in each column may change from time to time as the report is run. I want to create a column that combines the data in such a way:

Combined
Adam - Meta - Ops
Adam - Meta - Sales
Adam - Meta - Development
Adam - Nvidia - Ops
Adam - Nvidia - Sales
Adam - Nvidia - Development
Adam - OpenAI - Ops
Adam - OpenAI - Sales
Adam - OpenAI - Development
Betty - Meta - Ops
Betta - Meta - Sales
Betty - Meta - Development
Betty - Nvidia - Ops

.... etc.

I've tried various setups with arrayformula concat() with no luck. I was hoping this could be solved with just formulas and not needing any scripting.

3 Answers 3

1

You are asking how to create a Cartesian product of three sets. Assuming the data is in A2:C, try something like this:

=let( 
  table, A2:C, 
  blank, iferror(ø), 
  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)) 
)

See let(), lambda(), byrow(), choosecols(), sequence(), wraprows() and reduce().

2

This formula is very efficient in case you need to use larger data sets:

LET(a,A1:A, b,B1:B, c,C1:C,
  qr,LAMBDA(q, FILTER(q,q<>"")),
  calc,LAMBDA(x,y, TOCOL(TOROW(
    BYROW(x, LAMBDA(r, TOROW(ARRAYFORMULA(r&" - "&y))))))),
  calc(calc(qr(a), qr(b)), qr(c)))
  1. a,b, & c store the three ranges containing the values. They are each single columns, and do not need to be the same height.
  2. qr is a reusable LAMBDA function to remove blank rows from the ranges.
  3. calc is a reusable LAMBDA function that expects two inputs, x and y, that are each single column arrays (or ranges) and can be the same or different heights.
  4. calc uses BYROW to pass each value in x, one-by-one, into another LAMBDA function that stores the current value (row) in r.
  5. That nested LAMBDA's ARRAYFORMULA returns a one-column array where each value is r joined to a different member of y delimited by  - . The result of that ARRAYFORMULA is transformed into a row using TOROW.
  6. Once the LAMBDA's formula has been applied to each r, the two-dimensional array it returns has as many rows as there are in x and as many columns as in y.
  7. calc then transforms that two-dimensional array to a single column using TOCOL
  8. The last step is to call calc twice. Note that a, b, and c, are wrapped in qr to strip out any empty rows.
    1. The first calc is the nested one, where x=qr(a) and y=qr(b).
    2. The second calc is the outer one, where x is the array returned by the inner calc and y=qr(c)
    • Note that additional rows and/or columns can be added nesting the outer calc within another calc, as was done for c

BYROW  FILTER  LAMBDA  LET  TOCOL  TOROW

1

You can create a cartesian product by just concatting (&) first array with the TRANSPOSEd second array and then FLATTENning the result:

=LET(
  cart_prod, 
    LAMBDA(
      a,
      b, 
      FLATTEN(
        ARRAYFORMULA(a&" - "&TRANSPOSE(b))
      )
    ), 
  ab, cart_prod(A2:A4,B2:B4),
  cart_prod(ab, C2:C4) 
)

We can use REDUCE to iterate, if you have multiple columns.

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.