0

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!

New contributor
heartmender is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

0

You must log in to answer this question.

Browse other questions tagged .