Evenly Distribute Data through Groups

[jamesbrightwell]

Hello,

I am new to the forums and need some help. I have attached a spreadsheet to provide the example of what I am trying to do. What I have is 9 groups marked A through I and within each group is a series of "transits" and those transits have a certain amount of volume on any given month. What I need to do is write a macro that will allow for the total amount of volume in the transits to be evenly distributed through each of the 9 groups. So in the example the average of the groups is 168 total volume therefore I need each group to have as close to average (being 168 in this example) BUT in order to do this I would have to move the different transits into a new groups to achieve that balance.

The purpose of this is because I need different people processing different groups each month due to compliance and when I reshuffle the groups among people I also want to be able to re-balance the groups based on volume. I can't be the only person to have encountered this problem or wish, and I'm not a macro novice but this one has stumped me completely. Please help!

[shg]

Please take a few minutes to read the forum rules about cross-posting.

Thanks.

[jamesbrightwell]

Hello shg,

Thank you for the reminder on forum rules. I have removed the thread that causes the cross posting issue and will keep this thread as active as this is where I was able to post the spreadsheet as the example. Would you be so kind to help me with the formula you mentioned based on the spreadsheet I provided above? I understand you posted =INDEX($D$1:$L$1, MATCH(MIN(D2:L2), D2:L2, 0)) but I want to be sure it works out my problem as I'm getting some wonky results. Considering a different person will process each group each month I need to ensure that the transits within the groups being adjusted to meet the average of the total volume is being evenly spread through as well.

So as an example if I needed to adjust group A by 5 total volume I need the formula / macro to tell me which transit within the groups needs to be moved into group 5 to give it the correct and even volume.

[shg]

It does the assignment from scratch rather than transferring existing assignments.

[jamesbrightwell]

Thank you Shg, I understand the formula now and might be able to make this work. In case I cannot do you know of a way that the formula can transfer existing assignments? Or am I asking the impossible?

[shg]

It would be a different approach and different formula.