# 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

##### New Member
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!

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### DRSteele

##### Well-known Member
Welcome to the forum.

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

#### Juraexcel

##### New Member
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
ABCDEFGHIJKL
1MATERIALQUANTITYORDER NO 1ORDER NO 2ORDER NO 3ORDER NO 4ORDER NO 5ORDER NO 6ORDER NO 7ORDER NO 8ORDER NO Nrest
240600566131236320392
3406006218
440600905
540601101
640601616
7406017321100000000
84060176701000
9406019427151
1040602075140000000
1140602781105215105375165rest=1
1240603093120000000
13406032810141211000
1440604181100000000
15406042220141217130
1640604559000429171442985742930004291286427
1749804548141200000
1849906363120000000
1949906932110000000
2021PRODUCTS QUANTITY141217131
21ORDER NUMBER19103411910341191019219103411910153191030919103411913091910341
22
2320 16 15 13 12 5 4 1 0
24
251/21*quantitiroundup (4/20*quantity-previous left field)
26
Sheet5
Cell Formulas
RangeFormula
C7C7=ROUNDUP(\$C\$20/A20*[@QUANTITY],0)
F7F7=ROUNDUP(\$C\$20/D20*[@[ORDER NO 3]],0)
G7G7=ROUNDUP(\$C\$20/E20*[@[ORDER NO 4]],0)
H7H7=ROUNDUP(\$C\$20/F20*[@[ORDER NO 5]],0)
I7I7=ROUNDUP(\$C\$20/G20*[@[ORDER NO 6]],0)
J7J7=ROUNDUP(\$C\$20/H20*[@[ORDER NO 7]],0)
K7K7=ROUNDUP(\$C\$20/I20*[@[ORDER NO 8]],0)
L7L7=I7

Replies
4
Views
677

1,127,334
Messages
5,624,089
Members
416,010
Latest member
NJT

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back