Automation: Excluding duplicate previous entries prior to PivotTable

Copper Contributor

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*.



  • Cell F4  =IFERROR(VLOOKUP([@[ORDER]],B5:F50,5,FALSE),"")
  • 4/04/2023 Order02 is filled entirely, STATUS marked as COMPLETE.
  • Cell F4 returns COMPLETE.





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:

  1. Filter on STATUS = blank
    (only ORDERs containing open back orders, ie. rows 2,3,5,7)
  2. Copy & paste list to new sheet
    (avoiding accidental edits to records)
  3. Insert column, =CONCATENATE(B2,C2), repeat down
  4. 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)
  5. Filter off QTY BO = 0 (removes filled ITEMs within open back orders, ie. only row 3 remains)
  6. 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.

1 Reply






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.

exclude previous entries.JPGpivot table.JPG