qty from multiple orders to fill multiple requests over different dates

Hi community,

I really don't know how to explain this concisely.

I have a list of order numbers, items on that order and the quantities still to be delivered.

I receive a request for a certain number of items to be delivered on a specific date.
This is usually, X next week, Y week after, Z week after.
The customer wants to know which order numbers the items being delivered are from and which date.

Ideally from my end, I need to use up the orders that are the oldest.

What I need to automate, is the back filling from the oldest orders and how many items from that order number are being delivered.

Hopefully the example will aid in describing.

A-C are the outstanding orders
E-G tracking quantities, not functional
I-M the requested dates and quantities of items
P-T Ideal output with shade colours for the first date to aid with visuals
Red / Grey colour, highlighting the order numbers used
Green / Blue, the quantities of each
Yellow, just showing the date to match request / order

Where orders don't fully cover the request, the remaining will be made up from the next order number.
E.G. As shown in cell R7 & 8. 10 are needed. 5 available from order 503 and 5 from order 500, with 2 remaining.

Question - is the higher the order number the older the order? I would have thought the other way around, but you fulfill item 755A with 5 from each order (500 and 503) when there are 7 from 500? So is 503 the older order number? You seem to be filling the orders in the opposite way I'd expect so I'm assuming the higher the order # the older the order (which doesn't normally make sense).

Hi JeteMc,

Thanks for the time and effort you have put into this.
Looks like it does exactly as required!
I'll spend some time going through and try to understand each step you have laid out and come back with more praise, or questions if I can't figure it out.

Hi Gregb11,

Sharp eyes, indeed you are correct. Higher numbers = younger order.
I didn't order that part properly when putting the sample data together. The proper order numbers are 9 digits long, but I felt that was superfluous for this example.

Hi kvsrinivasamurthy,

Macro is absolutely fine. It is staying local to me, so I am happy with a solution that works in which every way imaginable.
It's all a learning curve!

Thankyou to all solutions. I am now able to process the data as needed.