# Stock allocation of an item as per FIFO

#### sunderlalrwr

##### New Member
I need an excel formula to allocate stock of an item based on column"FIFO_Rank" in Table-1 from stock Table-2.

TABLE-1
ITEM SO Qty FIFO_Rank Stock_Allocation
Chair 11 72
Table 50 14
Chair 14 99
Chair 20 36
Table 25 20
Table 20 49

TABLE-2

Item Stock
Chair 30
Tables 50

#### Attachments

• 8.5 KB Views: 71

#### vletm

##### Excel Ninja
sunderlalrwr
Did You explain clear, how would rank effect to allocation?
This would be one sample ...

#### Attachments

• 9.6 KB Views: 70

#### bosco_yip

##### Excel Ninja
Another formula option in FIFO allocation

In J7, copied down :

=MAX(0,MIN(H7,LOOKUP(G7,L\$7:M\$8)-SUMIF(G6:G\$6,G7,H\$6:H6)))

Regards
Bosco

#### Haz

##### Active Member
In case you need the lower rank to be supplied first

=MIN(H7,MAX(0,LOOKUP(G7,\$L\$7:\$L\$8,\$M\$7:\$M\$8)-SUMIFS(\$H\$7:\$H\$12,\$G\$7:\$G\$12,G7,\$I\$7:\$I\$12,"<"&I7)))

#### sunderlalrwr

##### New Member
Dear, need some more clarity
I want to allocate qty based on FIFO_Rank column "I" (order how much old) from stock table

In case of Item Chair : 1st stock should allocate to row no. 8 then row no. 7 & then row no. 9.
In case of Item Tables : 1st stock should allocate to row no. 12 then row no. 10 & then row no. 11

Check attached sheet

#### Attachments

• 12.8 KB Views: 84

#### vletm

##### Excel Ninja
sunderlalrwr
(no mixed with 'Sales Order Qty')

#### sunderlalrwr

##### New Member
sunderlalrwr
Did You explain clear, how would rank effect to allocation?
This would be one sample ...
I wants to stock allocation to Sales Orders

I have some open sales orders "Item" , "SO Qty" & "FIFO Rank/Aging in Days"

1st I want to allocate quantity to such orders who are old (in term of days)

#### sunderlalrwr

##### New Member
sunderlalrwr
Did You explain clear, how would rank effect to allocation?
This would be one sample ...
I want to allocate old orders first nothing else

#### sunderlalrwr

##### New Member
In case you need the lower rank to be supplied first

=MIN(H7,MAX(0,LOOKUP(G7,\$L\$7:\$L\$8,\$M\$7:\$M\$8)-SUMIFS(\$H\$7:\$H\$12,\$G\$7:\$G\$12,G7,\$I\$7:\$I\$12,"<"&I7)))
sir, u r right but I need higher rank to be supply first

#### vletm

##### Excel Ninja
sunderlalrwr ... I want to allocate old orders first nothing else ... hmm?
with original values...
Chair: rank order is 99 - 72 - 36
Tables: rank order is 49 - 20 - 14
BUT
You haven't name which part of Chairs
would give to the oldest (biggest or smallest).
And
How would You see which order would allocate in which order?
So far, You could see only results!

#### sunderlalrwr

##### New Member
Chair: rank order is 99 - 72 - 36
Tables: rank order is 49 - 20 - 14

Order should allocate same as you wrote

Last edited by a moderator:

#### vletm

##### Excel Ninja
sunderlalrwr
So, what is missing?
... what would be Your original sample files needed results?

#### sunderlalrwr

##### New Member
pls help..... still not resolved

#### vletm

##### Excel Ninja
sunderlalrwr
Without proper answers, it's challenge to help!

#### Haz

##### Active Member
sir, u r right but I need higher rank to be supply first
=MIN(H7,MAX(0,LOOKUP(G7,\$L\$7:\$L\$8,\$M\$7:\$M\$8)-SUMIFS(\$H\$7:\$H\$12,\$G\$7:\$G\$12,G7,\$I\$7:\$I\$12,">"&I7)))

#### sunderlalrwr

##### New Member
Dear HAZ, resolved my problem

thanks a lot dear

#### SUNCIOUS

##### New Member
Hi,

I was wondering how to modify above formula (=MIN(H7,MAX(0,LOOKUP(G7,\$L\$7:\$L\$8,\$M\$7:\$M\$8)-SUMIFS(\$H\$7:\$H\$12,\$G\$7:\$G\$12,G7,\$I\$7:\$I\$12,">"&I7)))) if I have one more criteria - which is to limit the returning value to return once only
if there is duplicate under FIFO_Rank 's column?

For example using above question :
There is duplicate under "Chair" - with "72" appearing twice.

TABLE-1
ITEM SO Qty FIFO_Rank Stock_Allocation
Chair 11 72
Table 50 14
Chair 14 99
Chair 20 72
Table 25 20
Table 20 49

TABLE-2
Item Stock
Chair 30
Tables 50

Regards,
Suncious

#### rjrd1004

##### New Member
Hi.. I have same question as SUNCIOUS. I impremented the formula from HAZ into my open requirements allocation and all works fine until I show duplicate ranks, in my case, same days old for same item.

#### Doe

##### New Member
Hi, I have something similar but in a horizontal template... i need to allocate stocks based on the rank and the limit stock. could you please help me here, TIA

#### Attachments

• 11.4 KB Views: 6

#### vletm

##### Excel Ninja
Doe
As You have just read Forum Rules from