Insert rows based on sum

[Cimpcrro]

Hello,

I am trying to build a list by copying in logical order the rows from 3 tables containing:
Existing stock,
Sales orders
Purchase orders

for each item in inventory.

Since there is no other available sorting criteria (no dates) the VBA should get the available inventory, then a number of lines from Sales orders until the sum of above lines <0, then the next Purchase Order Line.
- If there is no stock available, first line should be first PO
- If there is no more PO lines to add the SO lines continue under the value of 0.

Thank you!

 

[bebo021999]

What does the destination table look like?

 

[Cimpcrro]

What does the destination table look like?

The table in screenshot is intended as destination table

 

[Cimpcrro]

The table in screenshot is intended as destination table

The data would be in three other separate tables

 

[bebo021999]

So we need the data, along with destination table.

 

[Cimpcrro]

So we need the data, along with destination table.

I am only interested in item no, and qty. We need to get the rows based on calculated value of the rows above it, for each item. It's not necessary to actually have the Sumif column

 

[Cimpcrro]

So we need the data, along with destination table.

Destination Table:

test.xlsx
	A	B	C	D
1	Type	Item no.	QTY	SUM
2	Stock	111	1000
3	Sales order	111	-112	888
4	Sales order	111	-657	231
5	Sales order	111	-220	11
6	Purchase Order	111	1000	1011
7	Sales order	111	-159	852
8	Sales order	111	-778	74
9	Sales order	222	-543	-469
10	Stock	222	800
11	Sales order	222	-203	597
12	Sales order	222	-532	65
13	Purchase Order	222	1000	1065
14	Sales order	222	-322	743
15	Sales order	222	-499	244
16	Sales order	222	-543	-299
17	Sales order	222	-323	-622
18	Sales order	222	-133	-755
19	Sales order	222	-543	-1298
Destination table

Cell Formulas
Range	Formula
D3,D11	D3	=C2+C3
D4:D9,D12:D19	D4	=D3+C4

Sales Orders:

test.xlsx
	A	B	C
1	Type	Item no.	QTY
2	Sales order	111	-112
3	Sales order	111	-657
4	Sales order	111	-220
5	Sales order	111	-159
6	Sales order	111	-778
7	Sales order	222	-543
8	Sales order	222	-203
9	Sales order	222	-532
10	Sales order	222	-322
11	Sales order	222	-499
12	Sales order	222	-543
13	Sales order	222	-323
14	Sales order	222	-133
15	Sales order	222	-543
Sales Order

Purchase Orders:

test.xlsx
	A	B	C
1	Type	Item no.	QTY
2	Purchase Order	111	1000
3	Purchase Order	222	1000
Purchase orders

Inventory:

test.xlsx
	A	B	C
1	Type	Item no.	QTY
2	Stock	111	1000
3	Stock	222	800
Inventory

Thanks!