Excel Formula on FIFO

Hi,

Need help on formula which will allot stock from stock table (Stock_Rank Basis, Smallest To Largest) to Orders Table (FIFO _Rank basis Smallest To Largest)
In case of Item Chair :

1st Stock allocation
(In Order Table) 1st stock should allocate to row no. 10 , (From Stock table)1st stock should allocate from row no. 7
Stock required on (In Order Table) row no. 10 = 20, but stock available in (From Stock table) in row no. 7 = 5
(In Order Table) so 15 yet to fulfilled, then it can be taken (From Stock table) in row no. 8 = 10
(In Order Table) now 5 yet to fulfilled, then it can be taken (From Stock table) in row no. 9 = 10
Now stock required in (In Order Table) row no. 10 is fulfilled, and (From Stock table) in row no. 9 = 5, balance
It kind of cycle, which keeps on allocating stock based on Stock rank.

2nd Stock Allocation
(In Order Table) 2nd stock should allocate to row no. 7 = 11, (From Stock table) balance stock in row no. 9, balance can be allocated
(In Order Table) so 6 yet to fulfilled, then it can be taken (From Stock table) in row no. 10 = 5
(In Order Table) so 1 yet to fulfilled, now there is no stock left for Chair (From Stock table)

Same function is required for table, which will allocate stock, based on stock rank.

Hi AliGW,

Will follow the forum rule.
Thanks.