Order fulfilment and inventory availability

[Niklasie]

Hello there!

Hopefully everybody is doing fine.

I'm currently looking into making a little order fulfilment sheet for a small assembly workshop, as a favor/hobby project.

Please find attached a sample file of what I'm working on.

The situation:
I get data directly from the ERP-system of the shop, so it should always be up to date.
The data I'm getting is consumption per order number. So there could be loads of different products (lines) per order (ordernumber).
Since the final product is an assembled product, all parts of this product need to be in stock.

I already did an availibility check to see if the individual items are in stock (per order).

My question is:
How would I be able to see if an entire order can be made? So, if all items are in stock for the order number, how would I be able to show this?
I tried a pivot table but all that did was give me a summary let's say. I still was not able to mark an order to see if it can be assembled.


The idea is to in the end feed the data of orders that can be fully fullfilled into my assembly order planning sheet.

Thanks in advance, hopefully I'm clear enough in my description!

Greetings,
Niklas

[Niklasie]

Hi there,

small update to the sample file.
I added a column to hopefully better show what I mean.

As you can see, order P19073 can be assembled because there is enough inventory for all of the parts.
Order P19096 can not be assembled because for two of the items there is not enough inventory.

Hopefully this clears things up a bit!

[kvsrinivasamurthy]

May be this
In H2 then copied down

=IF(SUMPRODUCT(([Assembly Order No.]=[@[Assembly Order No.]])*([[Inventory Availability ]]="NO"))>0,"NO","YES")

OR

=IF(SUMPRODUCT(([Assembly Order No.]=[@[Assembly Order No.]])*([[Inventory Availability ]]="YES"))=SUMPRODUCT(--([Assembly Order No.]=[@[Assembly Order No.]])),"YES","NO")

[Niklasie]

Hi kvsrinivasamurthy!

Your solutions both work very well!

Thank you!

I'll mark the thread as solved and add rep!