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
Joined
Apr 4, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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! :)
TABLICA AUTOMAT RAZDUŽIVANJE.png
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,354
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 4, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,253
Messages
5,623,636
Members
415,983
Latest member
MusicMan

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top