allocating a total amount based on conditions

Hi all,

I have the following situation with which I need your help. I have a list with purchased quantities which is split per product (product ID) and purchase orders (one product can be purchased several times, each time it has assigned a new purchase order number; the purchase order number is higher for newer purchase orders). In another file I have a list of products (product ID) with their quantity on stock. I need to allocate the quantity on stock for each product ID to the respective purchase order. The logic would be that of FIFO, so then for the newest purchase number if the purchased quantity is lower then the stock quantity, the amount allocated would be the purchased qty, then it searches for the second newest purchase order and checks again if the purchase qty is lower or higher then the stock qty left; if the remaining stock qty is lower then it allocates all of it, if it's higher it allocates the purchase qty and goes to the next oldest purchase order, and so on. I have prepared a file with an example where I included also the result which should be achieved. The number of purchase orders is different depending on the product; it can be one or several depending how many times the product has been reordered. Also the original data files are very large, as they contain a lot of products.
If additional clarifications are needed let me know.

Thank you so much for your help!

Thank you. This works only if the products are sorted, but if they're in random order it doesn't work anymore.

What formulas can I use if the products are sorted in random order?

Attached file with products in random order.

Thank you!

Any ideas on what formulas I need to use?