Inventory Allocation

fruff

New Member
Joined
Nov 5, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,549
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?

1604610866813.png
 

fruff

New Member
Joined
Nov 5, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 6, 2012
Messages
1,549
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)))

1604621643678.png
 

Watch MrExcel Video

Forum statistics

Threads
1,118,713
Messages
5,573,753
Members
412,550
Latest member
soking
Top