help on how to automatically distribute values (defined as max ) on a list of materials across orders with variable quantities of products as integer

[Juraexcel]

Hi! I have this extremely big list of material that contains 10000-30000 positions -(COLUMN A) next to it is quantity in pieces (COLUMN B) values vary between 0 and hundred of thousands (these are also MAX VALUES). I need to automatically distribute each position of MATERIAL QUANTITIES over ORDER NUMBERS with different quantities of (various ) products so that the result is given as INTEGER number AND the SUM of all results doesnt exceed MAX value of each material quantity, there is no REST (or the rest is minimal), if the max value is reached sooner than end of orders-fields are populated with 0 , and the list of materials, quantities, product quantities vary over time. I work in a large warehouse, and need to solve this. Decimal numbers dont work as the materials are given as pieces, so there are no halves, quarters etc. I don't know where to even begin as my knowledge in Excel is pretty basic. Please help with some magic FORMULA or VBA code, I work on EXCEL 365 , thank you very much for your time and help!

 

[DRSteele]

Welcome to the forum.

Please upload your sample above by using xl2bb so that we can conduct experiments. Include expected outcomes as well.

 

[Juraexcel]

here it is, with some of my ideas, some fields are just tiped in, and I+ve tried to make some sort of formula but I don't know... doesn't seem too good :/

test 1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	MATERIAL	QUANTITY	ORDER NO 1	ORDER NO 2	ORDER NO 3	ORDER NO 4	ORDER NO 5	ORDER NO 6	ORDER NO 7	ORDER NO 8	ORDER NO N	rest
2	4060056	61	3	12	3	6	3	20	3	9	2
3	4060062	18
4	4060090	5
5	4060110	1
6	4060161	6
7	4060173	2	1	1	0	0	0	0	0	0	0	0
8	4060176	7	0	1	0	0	0
9	4060194	27	1	5	1
10	4060207	5	1	4	0	0	0	0	0	0	0
11	4060278	110	5	21	5	10	5	37	5	16	5	rest=1
12	4060309	3	1	2	0	0	0	0	0	0	0
13	4060328	10	1	4	1	2	1	1	0	0	0
14	4060418	1	1	0	0	0	0	0	0	0	0
15	4060422	20	1	4	1	2	1	7	1	3	0
16	4060455	9000	429	1714	429	857	429	3000	429	1286	427
17	4980454	8	1	4	1	2	0	0	0	0	0
18	4990636	3	1	2	0	0	0	0	0	0	0
19	4990693	2	1	1	0	0	0	0	0	0	0
20	21	PRODUCTS QUANTITY	1	4	1	2	1	7	1	3	1
21		ORDER NUMBER	1910341	1910341	1910192	1910341	1910153	1910309	1910341	191309	1910341
22
23			20	16	15	13	12	5	4	1	0
24
25			1/21*quantiti	roundup (4/20*quantity-previous left field)
26
Sheet5

Cell Formulas
Range	Formula
C7	C7	=ROUNDUP($C$20/A20*[@QUANTITY],0)
F7	F7	=ROUNDUP($C$20/D20*[@[ORDER NO 3]],0)
G7	G7	=ROUNDUP($C$20/E20*[@[ORDER NO 4]],0)
H7	H7	=ROUNDUP($C$20/F20*[@[ORDER NO 5]],0)
I7	I7	=ROUNDUP($C$20/G20*[@[ORDER NO 6]],0)
J7	J7	=ROUNDUP($C$20/H20*[@[ORDER NO 7]],0)
K7	K7	=ROUNDUP($C$20/I20*[@[ORDER NO 8]],0)
L7	L7	=I7