How to Apply Formulas Across the Values of Dynamic Arrays?

I am not the best Excel user in the world, so sorry if this is a basic question.

I'm trying to semi-automate expanding data received from a supplier to input into a receiving program. I have a solution that mostly works when handling data for a single line item, but am struggling to scale up the formulas to account for variable list lengths and dyanmic array sizes.

The data I receive will be in roughly this format:

RO	ROID	ITEM	CONT	RP	TALLY
P03865	46264	121012s	620243	2	1/10, 1/11
P03865	46265	121014s	620244	2	1/10, 1/11, 1/12

Where each row is going to contain information about a SKU and the "TALLY" field is going to contain a variable length of tallied items (i.e. line 1 contains 1 10ft piece and 1 11ft piece). The format I'm trying to manipulate the data into is roughly this (only including one line for brevity):

Received_qty	RO	ROID	ITEM	CONT	RP
21	P03865	46264	121012s	620243 1	2
10	P03865	46264	121012s .10	620243	2
11	P03865	46264	121012s .11	620243	2

RO, ROID, CONT, and RP columns will be static fields. The ITEM column will be repeated for every tallied length + 1 for a cumulative column. The received_qty is the product of each tally.

For repeating the product info I believe I can use a formula like =LEN(F2)-LEN(SUBSTITUTE(F2,"/","")) to parse the TALLY column, then use helper columns for repetition. I can use LEFT, RIGHT, and FIND formulas to parse the piece counts and tally lengths, and use math and concats for the items. I am comfortable with applying this to a sheet with one line item but not adapting the formulas to scale for additional line items that may have anywhere from 1 to 30 values in the TALLY column.

Solution

Assuming you have Microsoft 365, enter the following where you want the result (with enough empty rows below). The example shown uses a dynamics range (B6#) for the data parameter.

Step	Line
Data range is specified in parameter `data`, either as a regular range or a dynamic one	LAMBDA(data,
To process rows, get an array indices	row_indices, SEQUENCE(ROWS(data)),
Build the result in `result` using `REDUCE` by stacking built rows for each row	result, REDUCE(
Set the header as the initial value.	`TEXTSPLIT("Received_qty,RO,ROID,ITEM,CONT,RP", ",")`
For each row, accessing row by index	row_indices, LAMBDA(acc_res, cur_row,
using `INDEX` collect the columns in variables	LET( ro, INDEX(data, cur_row, 1), roid, INDEX(data, cur_row, 2), item, INDEX(data, cur_row, 3), cont, INDEX(data, cur_row, 4), rp, INDEX(data, cur_row, 5), tally, INDEX(data, cur_row, 6),
get quantity lengths by splitting the tally column	qty_lengths, `TEXTSPLIT(TRIM(tally), ",")`]
In `result_0`, build the rows for the current row using these lengths (`qty_lengths`), using `REDUCE` again.	result_0, REDUCE(
for each value in `qty_lengths` (for example `1/10`)	qty_lengths, LAMBDA(acc, cur,
get quantity and length by splitting	LET(qty_len, TEXTSPLIT(cur, "/"), qty, INDEX(qty_len, , 1), len, INDEX(qty_len, , 2)
build the row according your need	row_, HSTACK(qty * len, ro, roid, CONCAT(item, ".", len), "" & cont, rp)
stack the built rows	VSTACK(acc, row_)
after handling the tally for the current row, add the cumulative row	cumulative_row, HSTACK(SUM(INDEX(result_0, , 1)), ro, roid, item, CONCAT(cont, " ", 1), rp)
accumulate the results by stacking the rows , dropping the the initial "cumulative row" as we don't need it.	result, VSTACK(cumulative_row, DROP(result_0, 1))

=LAMBDA(data,
    LET(
        row_indices, SEQUENCE(ROWS(data)),
        result, REDUCE(
            TEXTSPLIT("Received_qty,RO,ROID,ITEM,CONT,RP", ","),
            row_indices,
            LAMBDA(acc_res, cur_row,
                LET(
                    ro, INDEX(data, cur_row, 1),
                    roid, INDEX(data, cur_row, 2),
                    item, INDEX(data, cur_row, 3),
                    cont, INDEX(data, cur_row, 4),
                    rp, INDEX(data, cur_row, 5),
                    tally, INDEX(data, cur_row, 6),
                    qty_lengths, TEXTSPLIT(TRIM(tally), ","),
                    result_0, REDUCE(
                        "cumulative_row",
                        qty_lengths,
                        LAMBDA(acc, cur,
                            LET(
                                qty_len, TEXTSPLIT(cur, "/"),
                                qty, INDEX(qty_len, , 1),
                                len, INDEX(qty_len, , 2),
                                row_, HSTACK(
                                    qty * len,
                                    ro,
                                    roid,
                                    CONCAT(item, ".", len),
                                    "" & cont,
                                    rp
                                ),
                                VSTACK(acc, row_)
                            )
                        )
                    ),
                    cumulative_row, HSTACK(
                        SUM(INDEX(result_0, , 1)),
                        ro,
                        roid,
                        item,
                        CONCAT(cont, " ", 1),
                        rp
                    ),
                    result, VSTACK(
                        cumulative_row,
                        DROP(result_0, 1)
                    ),
                    VSTACK(acc_res, result)
                )
            )
        ),
        result
    )
)(B6#)