Dividing a list into equal groups determined by two values

[orangesoda88]

Hi All,

I have a quick excel question.

I need to distribute a list of data into 10 even groups of data. Here is the scenario.

I have 10 salespeople on my team with a list of accounts that need to be evenly distributed based on two criteria

- Each sales person needs to have the same number of zipcodes , with the closest TOTAL # of accounts and TOTAL $ value as possible

spreadsheet is included. Thanks so much for your help!

Employee_Accounts.xls

[orangesoda88]

Here is some more information as I put it all into pivots and did some basic formulas

Total Zip Codes 359
Average per Sales Person 35.9

Total # of Accounts 114275
Average per Sales Person 11472.5

Total $ of Zip Codes 5511
Average per Sales Person 551.10

So using these means, I would like to distribute the whole set of data to 10 equal groups by sales person so that they all have as close to the same # of zip codes, # of accounts, and total value.

Any suggestions?

[orangesoda88]

Sorry, I left out the totals per zip code, accounting for each account within the zip codes. Use this sheet as it has more data.

Employee_Accounts.xls

[Kaper]

Pretty simple, while quite effective way could be that you sort the data decreasing on both number of accounts and value and then assign to staff in zigzag order
101
102
103
104
105
106
107
108
109
110
110
109
108
107
106
105
104
103
102
101
101
102
103
104
105
etc

either write "semi manually" or use formula in D2:

=MIN(MOD(ROW()-2,20),MOD(-ROW()+1,20))+101

in case of your sample data the division is:
accounts value
10975 min 528 min
12250 max 563 max
with equal number of zipcodes

better fit would require (in my opinion) either more sophisticated formula or (probably) macro.

[Kaper]

as for new file - the only difference is sort on other column and add empty one to fit the assignment formula.

[orangesoda88]

Very sound formula, and great advise, Kaper. Could you explain the formula that you used to assign the accounts to each sales person? I see it in the formula bar, I just don't quite fully understand it and would like to. You did a great job, thank you very much.

[Kaper]

the formula for column D is not really "assigning" salespersons

it is really listing them in specific order.

Assigning is done by sorting

If you have amounts (let's leave number for a moment) sorted descending and assign people in standard order, then 1st will have always most and last person least
look at 12 values and 3 persons.
typical order would be
12 1
11 2
10 3
9 1
8 2
7 3
6 1
5 2
4 3
3 1
2 2
1 3

thus person 1 would have 30, because systematically would be assigned to highest of each 3some of values person 2 26 and person 3 only 22

while with zigzag arrangement
12 1
11 2
10 3
9 3
8 2
7 1
6 1
5 2
4 3
3 3
2 2
1 1

which lead to sum of 26 for each of 3 persons


OK. Now how this:
=MIN(MOD(ROW()-2,20),MOD(-ROW()+1,20))+101
produces sequence of
101
102
...
109
110
110
109
108
..
102
101
101
102
etc.

we have MIN of two values:
MOD(ROW()-2,20)
and
MOD(-ROW()+1,20)

MOD is a rest value in integer division
ROW returns number of current row
in row2 we have rest from (2-2)/10 = 0
and
(-2+1)/20 =... one would say ... 0 and rest -1 but here it is -1 and rest = 19
of course min of 0 and 19 is 0

in row 3
we have 1 and 18

in row 11: 10 and 9 so min is 9
in row 12: 9 and 10 MIN again 9 !
in row 13: 8 and 11 and so on.

...


Hope it helped a bit with understanding how to create zigzag sort

Of course if you have 8 persons use Mod(xxx;16) etc.