Allocate Stock from Purchase order to Sales order

[FerryG]

Dear All

I'm having difficulty achieving the following,

We have a chronological list with Items, purchase orders and sales orders.
I want to know which purchase order is the latest one needed to cover the quantity in the sales order. (this is not always the first purchase order that comes next)

In column G I've put the desired output, could someone give me a push in the right direction?

Many thanks for your help!

Book1
	A	B	C	D	E	F	G
1	Item Code	Desired Date	Order Type	Order number/Stock	Quantity	New Stock Level	Desired output
2	Item A		Start Stock	In stock	0	0
3	Item A	1-5-2016	Purchase order	PO 1	5	5
4	Item A	16-5-2016	Sales Order	SO 1	-5	0	PO 1
5	Item A	25-5-2016	Sales Order	SO 2	-3	-3	PO 2
6	Item A	26-5-2016	Sales Order	SO 3	-3	-6	PO 2
7	Item A	31-5-2016	Sales Order	SO 4	-5	-11	PO 3
8	Item A	1-6-2016	Purchase order	PO 2	6	-5
9	Item A	5-6-2016	Purchase order	PO 3	6	1
10	Item A	20-6-2016	Sales Order	SO 5	-3	-2	PO 4
11	Item A	30-6-2016	Sales Order	SO 6	-3	-5	To be ordered
12	Item A	23-6-2016	Purchase order	PO 4	2	-3
13	Item B		Start Stock	In stock	5	5
14	Item B	1-5-2016	Purchase order	PO 5	5	10
15	Item B	16-5-2016	Sales Order	SO 7	-5	5	In stock
16	Item B	25-5-2016	Sales Order	SO 8	-3	2	PO 5
17	Item B	26-5-2016	Sales Order	SO 9	-3	-1	PO 6
18	Item B	31-5-2016	Sales Order	SO 10	-5	-6	PO 6
19	Item B	1-6-2016	Purchase order	PO 6	6	0
20	Item B	5-6-2016	Purchase order	PO 7	6	6
21	Item B	20-6-2016	Sales Order	SO 11	-3	3	PO 7
22	Item B	30-6-2016	Sales Order	SO 12	-3	0	PO 7
23	Item B	23-6-2016	Purchase order	PO 8	2	2
Sheet1