Material Stock Allocation based on order date & distribute value evenly

Hi ,

Need your help to create a formula for allocation of available stock quantity to each material - distributes Stocks evenly in a order based on 2 conditions


First condition : Order header : stock quantity to allocate for "2" type
after completion of (2) , balance stock quantity to allocated to "7" type
finally, blanks to be allocated

Second condition : Order date : FIFO Basis allocation based on order creation date


i am currently update manually but, its more time consuming and finding mistakes

Steps i followed
I did vlookup in Raw report and added stock quantity against each material
i sorted the order creation date manually (oldest to newest)
i filtererd the mateiral each individually and then, i filter order header "2" type first,
Then , i enter the distribute the stock evenly in the allocation quantity manually and follow the same procedure for "7" type and finally for blank in A column

i have filtered one material and allocated the quantity against order manually till Inventor Stock is available and balance stock is identified
i make it in 3 different set of column and merging it manually into one column.

Output :
Material : 16018000
Inventory stock Qty : 1513
Total order Qty : 909
Balance stock Qty : 604
Pending order Qty: 0
After allocation as per above conditional sequence, i finish for 1 material. likewise i do it for 500+ material manually.

Hence i needs your help to create formula for auto Allocation of quantity and calculate balance stock for reference
if Inventory stock gets zero, the allocation quantity will also be zero


So, please help to create a formula as i am eager to learn new methods of allocation of the parts


Thanks in Advance

Regards,
RAJ