Auto distribute items between divisions

%3CLINGO-SUB%20id%3D%22lingo-sub-3413374%22%20slang%3D%22en-US%22%3EAuto%20distribute%20items%20between%20divisions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3413374%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3CBR%20%2F%3E%3CBR%20%2F%3E-----------Background%20of%20question------------%3CBR%20%2F%3EEvery%20so%20often%20a%20random%20amount%20of%20items%20needs%20to%20be%20distributed%20between%20different%20departments.%20(the%20amount%20of%20items%20is%20between%201%20and%202.500)%3CBR%20%2F%3EIn%20the%20past%20this%20happened%20randomly%20and%20manually%20and%20due%20to%20that%2C%20certain%20departments%20got%20a%20lot%20of%20items%20and%20other%20departments%20got%20none.%3CBR%20%2F%3EI%20tried%20making%20an%20excel%20for%20mathematically%20allocating%20items%20to%20departments.%3CBR%20%2F%3EThe%20logic%20behind%20the%20excel%20works%20and%20if%20I%20manually%20follow%20the%20file%2C%20I%20get%20an%20even%20distribution.%3CBR%20%2F%3ESince%20this%20time%2C%20around%201.100%20items%20needed%20to%20be%20divided%20between%20approximately%2050%20departments%20this%20took%20a%20lot%20of%20time.%3CBR%20%2F%3EI%20have%20a%20tiny%20bit%20of%20programming%20experience%20and%20started%20tinkering%20with%20while%20loops%20but%20I%20got%20nowhere.%3CBR%20%2F%3E----------------The%20File-------------------%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22RobRom_0-1653295578962.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F374132i71B9020731E576B0%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22RobRom_0-1653295578962.png%22%20alt%3D%22RobRom_0-1653295578962.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E-%20The%20green%20cells%20are%20cells%20I%20fill%20in%20when%20I%20start%20using%20the%20file%3CBR%20%2F%3E-%20The%20yellow%20cells%20are%20calculations%3CBR%20%2F%3E-%20The%20orange%20cells%20are%20the%20items%20distributed%20between%20the%20departments.%3CBR%20%2F%3E%3CBR%20%2F%3EAt%20the%20top%20right%2C%20the%20row%20with%20%22urgency%20of%20division%22%20is%20the%20cell%20I%20use%20to%20manually%20fill%20in%20the%20orange%20cells.%3CBR%20%2F%3EIf%20I%20were%20to%20fill%20in%20the%20line%20for%20I33%20I%20would%20look%20at%20the%20first%20lowest%20number%20in%20the%20%22urgency%20of%20division%22%20row.%3CBR%20%2F%3EThis%20time%2C%20that%20would%20be%20D3.%3CBR%20%2F%3EThe%20I33%20row%20would%20look%20the%20following%20after%20filling%20it%20in%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22RobRom_1-1653295860097.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F374133i89C05AA21D3A9AAD%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22RobRom_1-1653295860097.png%22%20alt%3D%22RobRom_1-1653295860097.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E------------------Question----------------------%3CBR%20%2F%3EIs%20there%20a%20way%20to%20turn%20the%20manual%20distribution%20of%20items%20between%20departments%20I%20made%20into%20a%20Macro%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20amount%20of%20departments%2C%20the%20amount%20of%20unique%20items%20and%20the%20amount%20of%20items%20each%20specific%20item%20has%20needs%20to%20be%20variable.%3CBR%20%2F%3EThe%20distribution%20needs%20to%20be%20as%20even%2Fequal%20as%20possible.%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20in%20advance!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E----------Link%20to%20the%20document%20in%20google%20drive---------%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.google.com%2Fspreadsheets%2Fd%2F1hY0wDDvXYdnTwmyhJgV78EleNPESPyXl%2Fedit%3Fusp%3Dsharing%26amp%3Bouid%3D107772585694539188158%26amp%3Brtpof%3Dtrue%26amp%3Bsd%3Dtrue%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.google.com%2Fspreadsheets%2Fd%2F1hY0wDDvXYdnTwmyhJgV78EleNPESPyXl%2Fedit%3Fusp%3Dsharing%26amp%3Bouid%3D107772585694539188158%26amp%3Brtpof%3Dtrue%26amp%3Bsd%3Dtrue%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3413374%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
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