Forum Discussion
Automation: Excluding duplicate previous entries prior to PivotTable
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 part...
=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.
