# Inventory Allocation

#### fruff

##### New Member
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.

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### mrshl9898

##### Well-known Member
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

##### New Member
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
Ignore this. Have a solution but need to restart my pc

#### mrshl9898

##### Well-known Member
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)))

Replies
2
Views
123
Replies
2
Views
111
Replies
3
Views
130
Replies
4
Views
84
Replies
2
Views
122