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!