# Multiply/Concatenate String Arrays

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

Name Company Division
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
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.

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))
)
``````

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`

You can create a `cart`esian `prod`uct by just concatting (`&`) first array with the `TRANSPOSE`d second array and then `FLATTEN`ning 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.