DIstribute workload based on values across columns

Hi ebonham and welcome to the forum,

I call this answer a snake sort. You first sort the numbers from high to low then use the top 4 numbers and put them in different columns 1,2,3,4. Then reverse the order and put the next in 4,3,2,1. This snakes the placement of decreasing numbers down the rows. See the attached with my first answer.

Server Distribution snake sort 4 teams.xlsx

Hey ebonham,

The method is of most importance in this problem. Here is another try. Take the average of the total and use it to assign servers. Start from the largest and second until you get more than the average. Then move to the next server. See the attached. I'll think about a way to automate this. This method gets a LOT closer than the Snake method.

Server Distribution Cash Back method .xlsx

Hi ebonham,

I think I have a great solution to this fun problem. I tried to snake the filling of those servers without success. Then after doing your problem by hand many times it came to me. You need to give the next largest school to the server with the least number on it already. I used a little table in yellow to do a CountIf formula showing how many people were on a server down to any point in the rows. A third column shows how close to perfect the sort is.

You need to sort Column B from Large to Small and then run the code. The code clears Column C and starts down the column assigning each School number to servers. It will add the next (largest) to the server with the least number of people. Problem solved!!??

Please Login or Register  to view this content.

Server Distribution Fill the Least Server next.xlsm