Allocation/Distribution

[SeaBearz]

Hello All,

After much researching, experimenting, and commensurate failure, I'm reaching out to see if anyone else can provide a solution to apparently a complex request.

1. Proportionate distribution of warehouse inventory by Item # amongst stores in that location that request a quantity of that item (ex. only Houston warehouses supply Houston Stores).
2. Prioritize a variable minimum number of items at certain stores (i.e. if a store needs only 1 item, make sure it gets 1 before another store gets 8).
3. Identify any underutilized warehouse inventory (can be a separate table).
4. It shouldn't make a difference, but I am working with two tables.

	A	B	C	D	E	F	G	H	I
1	Warehouse Inventory				Store Orders
2	Storage Location	Item #	Quantity		Store Location	Store #	Item #	Quantity Needed	Allocation
3	Houston	E1142	9​		Houston	Store 1	E1142	8​	8​
4	Houston	E4651	3​		Houston	Store 1	E4651	1​	1​
5	Houston	E12F2D	1​		Houston	Store 1	E12F2D	2​	1​
6	Monterey	D7332	2​		Houston	Store 2	E1142	2​	1​
7	Austin	E12F2D	1​		Monterey	Store 1	D7332	2​	2​
8	Austin	E3779	6​		Austin	Store 1	E12F2D	1​	1​
9					Austin	Store 1	E3779	6​	5​
10					Austin	Store 2	E12F2D	2​	0​
11					Austin	Store 2	E3779	1​	1​

 

[kvsrinivasamurthy]

In I3 copied down to last Row.

=IF($H3=1,1,MAX(0,MIN(SUMIF($B$3:$B$8,$G3,$C$3:$C$8)-SUMIF($G$2:$G2,$G3,$H$2:$H2)-COUNTIFS($G4:$G$11,$G3,$H4:$H$11,1),$H3)))

 

[kvsrinivasamurthy]

Modified formula in I3

=IF($H3=1,MAX(0,MIN(1,SUMIF($B$3:$B$8,$G3,$C$3:$C$8)-SUMIF($G$2:$G2,$G3,$I$2:$I2))),MAX(0,MIN(SUMIF($B$3:$B$8,$G3,$C$3:$C$8)-SUMIF($G$2:$G2,$G3,$H$2:$H2)-COUNTIFS($G4:$G$11,$G3,$H4:$H$11,1),$H3)))

 

[SeaBearz]

kvsrinivasamurthy,

This is exactly what I was I detailed in the initial post. However, one element I realized I failed to include is a minimum supply - I.e. I would like to have at least 1 in each store if warehouse inventory allows. Row 5 (Houston, Store 2, item # E1142) would allocate at least 1 before Houston Store 1 filled all 8.

 

[kvsrinivasamurthy]

I am not clear. Try this in I3. If not Ok Pl upload file with expected result with some more rows.

=IF($H3=1,MAX(0,MIN(1,SUMIF($B$3:$B$8,$G3,$C$3:$C$8)-SUMIF($G$2:$G2,$G3,$I$2:$I2))),MAX(0,MIN(SUMIF($B$3:$B$8,$G3,$C$3:$C$8)-SUMIF($G$2:$G2,$G3,$I$2:$I2)-COUNTIFS($G4:$G$11,$G3,$H4:$H$11,1)- COUNTIFS($G4:$G$11,$G3,$F4:$F$11,"<>"&$F3),$H3)))