I am creating a cycle count sheet. Sheet 1 will be user input where the material and quantity found will be put. Sheet 2 is a snapshot of inventory at the time of the count. I want the quantity of the material found to be distributed among the quantities on sheet 2 until the sheet 1 quantity is exhausted. It would also be helpful to distribute the quantity in order of the newest batch(day code) to the oldest batch(FIFO).

I can make this work when only looking at one material at a time with MIN MAX functions. The trouble I am having is when I add criteria. I have attached a screen shot of the desired results. As you can see, an ideal formula in D11 Found that 30,000 of P9919617 was available on 'sheet 1' and distributed that amount through inventory on 'sheet 2' until the 30,000 was exhausted leaving an adjustment of 10,584 to the 2278US9602 Batch. I have attached a link to the sheet below as well.