Inventory Allocation

[fruff]

I am managing a file with multiple orders. Each order contains some basic information like customer number, order number, SKU, and quantity ordered. I want to allocate available inventory to these orders, but have been running into some trouble. I have Sheet 1 with all my order data, and Sheet 2 is my inventory data. The issue is I have multiple orders for different customers for the same SKU so I want to allocate it down where it deducts from open inventory.

For example:

I have 10 orders for SKU 6789. 50 units total with 10 each on each order. Several customers.
I have 40 units of SKU 6789 in stock.

I want the file to show something like:

ORDER SKU QTY ORDER CAN SHIP
12345 6789 10 10
34565 6789 10 10
89475 6789 10 10
54678 6789 10 10
45265 6789 10 0

I would consider myself an intermediate excel user and I have been trying everything, and nothing is getting the desired result. Any help would be appreciated.

 

[mrshl9898]

D2 =IF(VLOOKUP(B2,G:H,2,0)-SUM(C$2:C2)>=0,C2,0)

Question is what to do if you can part fulfill an order as per the below?

Would order 5 get 5 units or 0?

 

[fruff]

D2 =IF(VLOOKUP(B2,G:H,2,0)-SUM(C$2:C2)>=0,C2,0)

Question is what to do if you can part fulfill an order as per the below?

Would order 5 get 5 units or 0?

View attachment 25562

Yes, I would want to fill a partial. So if we have 5 remaining I would want to allocate 5 to row 5. Thanks so much for your help

 

[mrshl9898]

Ignore this. Have a solution but need to restart my pc

 

[mrshl9898]

Try this, not certain I have done enough testing

=IF(VLOOKUP(B2,F:G,2,0)-SUMIF(B$2:B2,B2,C$2:C2)>=C2,C2,IF(VLOOKUP(B2,F:G,2,0)-SUMIF(B$2:B2,B2,C$2:C2)<0,C2--VLOOKUP(B2,F:G,2,0)-SUMIF(B$2:B2,B2,C$2:C2)))