Auto distribute items between divisions

Occasional Visitor

Hello

-----------Background of question------------
Every so often a random amount of items needs to be distributed between different departments. (the amount of items is between 1 and 2.500)
In the past this happened randomly and manually and due to that, certain departments got a lot of items and other departments got none.
I tried making an excel for mathematically allocating items to departments.
The logic behind the excel works and if I manually follow the file, I get an even distribution.
Since this time, around 1.100 items needed to be divided between approximately 50 departments this took a lot of time.
I have a tiny bit of programming experience and started tinkering with while loops but I got nowhere.
----------------The File-------------------

RobRom_0-1653295578962.png

- The green cells are cells I fill in when I start using the file
- The yellow cells are calculations
- The orange cells are the items distributed between the departments.

At the top right, the row with "urgency of division" is the cell I use to manually fill in the orange cells.
If I were to fill in the line for I33 I would look at the first lowest number in the "urgency of division" row.
This time, that would be D3.
The I33 row would look the following after filling it in:

RobRom_1-1653295860097.png

 

------------------Question----------------------
Is there a way to turn the manual distribution of items between departments I made into a Macro?

The amount of departments, the amount of unique items and the amount of items each specific item has needs to be variable.
The distribution needs to be as even/equal as possible.

Thank you in advance!

 

----------Link to the document in google drive---------

https://docs.google.com/spreadsheets/d/1hY0wDDvXYdnTwmyhJgV78EleNPESPyXl/edit?usp=sharing&ouid=10777...

 

 

0 Replies