Order analysis by supplied or not supplied

Hi Excel Users

I'm trying to create an analysis where I have a list of records with duplicate reference numbers which translates to one order.

In these orders, I am analysing the stock on hand for their orders.

Some orders do not have stock on hand (=0), with other items being available (>0). Some orders, don't have any items available (=0) and some other orders are fully available (>0)

I am hoping to extract a formula or even an enhanced pivot table that presents me with an analysis that I can report on;
a) orders that are fully supplied (SOH > 0 for all items greater in the customer's order;
b) orders that are partially supplied (one/more SOH > 0, and one / more SOH =0)
c) orders that are cannot be supplied (SOH = 0)

I have attached my spreadsheet I am working with to this thread.


Many thanks
Andrew

i'd do it with helper columns and autofilter