Dynamic Array - How to Conditionally Generate Sums?

Question

I have a formula that's meant to iterate through a dynamic range, generate cumulative rows (the #/# in "Tally" represents a number of pieces and a length in ft.) and stack them into a new sheet.

Data being processed comes in this format:

po_number	po_line_item	sku	container	tally	rp
12345	54321	PNE16s	24680	5/8, 10/16	2

Data is currently processed by the formula (below) into this format:

recieved_qty	po_number	po_item	stock_code	container_id	rec_process
200	12345	54321	PNE16s	24680	2
40	12345	54321	PNE16s.8	24680	2
160	12345	54321	PNE16s.16	24680	2

I am trying to slightly alter the way that received_qty is calculated, as below:

recieved_qty	po_number	po_item	stock_code	container_id	rec_process
200	12345	54321	PNE16s	24680	2
5	12345	54321	PNE16s.8	24680	2
10	12345	54321	PNE16s.16	24680	2

And am struggling with how I need to alter the cumulative row generation to accommodate this. I know that basically I need IF(qty = 0, return qty * len, ELSE return qty but I'm not actually sure how to pipe that in. Any tips or help would be greatly appreciated! The "po_info" is an index of another sheet that will house this raw data less the header rows, since I just define them in the formula below.

=LET(
    data, po_info,
    row_indices, SEQUENCE(ROWS(data)),
    result, REDUCE(
        TEXTSPLIT("RECEIVED_QTY,PO_NUMBER,PO_ITEM,STOCK_CODE,CONTAINER_ID,REC_PROCESS", ","),
        row_indices,
        LAMBDA(acc_res,cur_row,
            LET(
                PO_NUMBER, INDEX(data, cur_row, 1),
                PO_ITEM, INDEX(data, cur_row, 2),
                STOCK_CODE, INDEX(data, cur_row, 3),
                CONTAINER_ID, INDEX(data, cur_row, 4),
                REC_PROCESS, 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,
                                PO_NUMBER,
                                PO_ITEM,
                                CONCAT(STOCK_CODE, ".", len),
                                "" & CONTAINER_ID,
                                REC_PROCESS
                            ),
                            VSTACK(acc, row_)
                        )
                    )
                ),
                cumulative_row, HSTACK(
                    SUM(INDEX(result_0, , 1)),
                    PO_NUMBER,
                    PO_ITEM,
                    STOCK_CODE,
                    CONCAT(CONTAINER_ID,),
                    REC_PROCESS
                ),
                result, VSTACK(cumulative_row, DROP(result_0, 1)),
                VSTACK(acc_res, result)
            )
        )
    ),
    result
)

---

Also not sure why the first and second tables are breaking since they're fine in the preview.. working on them now.. thanks in advance again!

Answer 1

If efficiency is a concern, it's best to avoid iterative methods like REDUCE with INDEX and VSTACK, as they tend to perform very poorly when the number of iterations exceeds 1,000.

As an alternative, consider the following approach:

=LET(
    data, po_info,
    rowId, SEQUENCE(ROWS(data)),
    tally, CHOOSECOLS(data, 5),
    total_received, MAP(tally, LAMBDA(v, LET(a, TEXTSPLIT(v, "/", ","), SUM(TAKE(a,, 1) * DROP(a,, 1))))),
    total_rows, HSTACK(total_received, CHOOSECOLS(data, 1, 2, 3, 4, 6)),
    tally_count, LEN(tally) - LEN(SUBSTITUTE(tally, ",", )) + 1,
    tally_cols, SEQUENCE(, MAX(tally_count)),
    tallys, TOCOL(TEXTBEFORE(TEXTAFTER("," & tally & ",", ",", tally_cols), ","), 2),
    rowIds, TOCOL(IFS(tally_count >= tally_cols, rowId), 2),
    data_array, CHOOSEROWS(data, rowIds),
    stock_code, CHOOSECOLS(data_array, 3) & "." & TEXTAFTER(tallys, "/"),
    qty_received, --TEXTBEFORE(tallys, "/"),
    qty_rows, CHOOSECOLS(HSTACK(qty_received, data_array, stock_code), 1, 2, 3, 8, 5, 7),
    VSTACK(
        {"RECIEVED_QTY","PO_NUMBER","PO_ITEM","STOCK_CODE","CONTAINER_ID","REC_PROCESS"},
        SORTBY(VSTACK(total_rows, qty_rows), VSTACK(rowId, rowIds))
    )
)

NOTE: the sample table you shared seems to indicate that "tally" is column 5, but the formula you shared defines tally as column 6. Please adjust the column numbers as needed.

The total rows are produced first. This is done by calculating the total received for each record using MAP, appending the results to the original dataset using HSTACK, and removing the original tally column using CHOOSECOLS.

Next, the tally column is split into an array using TEXTBEFORE & TEXTAFTER. The width of the array is determined by the record containing the greatest number of "," delimiters. Any records with fewer delimiters than the maximum will be filled with #N/A in their excess columns. TOCOL is then used to send the results to a single column with the errors removed (by setting the optional [ignore] argument to 2).

The TOCOL & IFS method is then used to repeat the row numbers of the original dataset accordingly, which in turn are used to pull the appropriate records using CHOOSEROWS. The resulting data array is then manipulated using CHOOSECOLS, TEXTAFTER, TEXTBEFORE and HSTACK to produce the desired output for the rows containing the individual quantities.

Lastly, the quantity rows are appended below the total rows and sorted by row number using VSTACK and SORTBY. VSTACK is also used to add the headers to the final output.

To compare: when tested with 5,000 rows of data, this method returned the results in a split second, whereas REDUCE with INDEX and VSTACK took approx. 20 seconds.

Answer 2

=LET(range,A2:F3,
REDUCE({"recieved_qty","po_number","po_item","stock_code","container_id","rec_process"},
       SEQUENCE(ROWS(range)),
LAMBDA(a,b,
       LET(c,--TEXTSPLIT(INDEX(range,b,5),"/",","), 
       VSTACK(a,
              IFNA(HSTACK(
                          VSTACK(SUM(TAKE(c,,1)*DROP(c,,1)),
                                 TAKE(c,,1)),
                          INDEX(range,b,{1,2}),
                          INDEX(range,b,3)&VSTACK("","."&DROP(c,,1)),
                          INDEX(range,b,{4,6})),
                   INDEX(range,b,{0,1,2,3,4,6})))))))

c will split your tally values in two columns that you can multiply and use the first column to (v)stack underneath the multiplication. Next (h)stack the column values of referenced row. Since that's a single line array and the vstacked tally calculation is multiple lines, this creates a spill range including #N/A. This is where we make use of IFNA to lift along the indexed values.