Situation:
I have a back order register which acts as a backup dispatch record, balanced against an actual dispatch register when discrepancies occur. Every action performed in the creation and partial/total fill of a back order is recorded on a new row, currently totalling 600+. Once an ORDER has no remaining open ITEM back orders its STATUS is marked as COMPLETE, which automatically marks all previous rows containing that ORDER as COMPLETE*.
*ie.
- Cell F4 =IFERROR(VLOOKUP([@[ORDER]],B5:F50,5,FALSE),"")
- 4/04/2023 Order02 is filled entirely, STATUS marked as COMPLETE.
- Cell F4 returns COMPLETE.
Dilemma:
In order to view only current open back orders, I haven't found use in a PivotTable due to the many rows with duplicate ORDER/ITEMs. I would however like to find a way to automate open back order information in a separate sheet.
Current manual solution:
- Filter on STATUS = blank
(only ORDERs containing open back orders, ie. rows 2,3,5,7) - Copy & paste list to new sheet
(avoiding accidental edits to records) - Insert column, =CONCATENATE(B2,C2), repeat down
- Highlight duplicates in concatenation column, delete highlighted rows from top to bottom
(only most recent ITEM entries for ORDERs containing open back orders remain, ie. rows 3,7) - Filter off QTY BO = 0 (removes filled ITEMs within open back orders, ie. only row 3 remains)
- Create PivotTable, displaying ITEM and QTY BO, providing accurate information on current open back ordered items and quantities.
Advice appreciated on how I might automate this, gurus.
=LET(rng,A2:F13,status_empty,
FILTER(rng,TAKE(DROP(rng,,5),,1)=""),
concatorderitem,CONCATENATE(DROP(DROP(status_empty,,-4),,1),DROP(DROP(status_empty,,-3),,2)),
maxperdate,BYROW(concatorderitem,LAMBDA(row,MAX(IF(row=DROP(DROP(status_empty,,-4),,1)&DROP(DROP(status_empty,,-3),,2),TAKE(status_empty,,1))))),
VSTACK({"date"."order"."item"."qty fill"."qty bo"."status"},FILTER(status_empty,(maxperdate=TAKE(status_empty,,1))*(concatorderitem=DROP(DROP(status_empty,,-4),,1)&DROP(DROP(status_empty,,-3),,2))*(DROP(DROP(status_empty,,-1),,4)>0))))
Does this return the expected result? The result range can be used to dynamically set up a Pivot Table.
