Split a List Evenly into 5 Groups

[lucolem]

Hello,
I am frustrated and need help! I need to split a list of 100+ rows that contain names into 5 groups evenly, or as close to even as I can get. It is a list of names, (last name, first name) and I need to split them out into groups based on the last name such as A-G, H-L, M-O, etc. The alpha split doesn't matter, it just needs to be as evenly distributed as possible. I have been doing it manually with a vlook-up formula, but I have to keep adjusting it and can't seem to get that even number. Does anyone have any advice? No VBA please.

Thanks!

 

[Eric W]

Without VBA, the only tool left that might work is the Solver. Here is my sample sheet:

Excel 2010
	A	B	C	D	E	F	G
1	A		A-B	1	2		19
2	A		C-K	3	11		29
3	A		L-L	12	12		34
4	A		M-R	13	18		35
5	A		S-Z	19	26		36
6	A
7	A						17
8	A
9	A
10	A
11	A
12	A
13	A
14	A
15	A
16	B
17	B
18	B
19	B
20	C
21	C
22	C
Sheet2

Cell Formulas
Range	Formula
C1	=CHAR(D1+64)&"-"&CHAR(E1+64)
D2	=E1+1
G7	=MAX(G1:G5)-MIN(G1:G5)
G1	{=SUM(IF(--(CODE($A$1:$A$153)>=D1+64)*(--(CODE($A$1:$A$153)<=E1+64)),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Put the formula in C1, and copy it down to C5. Put a 1 in D1, and the formula in D2, and copy that down to D5. Put a 26 in E5. Put the formula in G1, confirm with Control-Shift-Enter, then copy to G5. Make sure you change the range to match your sheet. Then put the formula in G7. The list of names is in column A (I just used letters, but it will work with names). The first letter must be capitalized.

Once you have the page set up as shown, run the Solver:

1) Make sure you have it available. It should be on the far right of the Data tab. If it's not there, click File --> Options --> Add-ins --> From the dropdown at the bottom, choose Excel Add-ins --> Go --> check the Solver box and click OK.
2) Go to the data tab and click Solver.
3) Enter these options:

Set Objective: $G$7
To: Min
By Changing Variable Cells: $E$1:$E$4
Subject to the constraints:
$E$1<=22
$E$1>=1
$E$1:$E$4 = integer
$E$2<=23
$E$2>=$E$1+1
$E$3<=24
$E$3>=$E$2+1
$E$4<=25
$E$4>=$E$3+1
Solving Method: Evolutionary

Then click Solve.

The letters in column C are the ranges, and the numbers in column G are the number of names in each range. The G17 formula is the range between the biggest range and smallest range, and the Solver is trying to make that as small as possible. There might be a better formula, but that's a good start.

Let me know if this works for you.

 

[lucolem]

Thank you Eric! I would never have gotten this far on my own. Haha

I did this step-by-step with the names, and also again the first letter of each last name. Got the same results with both.

I did get an alpha split, but the G columns didn't return numbers, the formula is still shown there. The G7 is 0.

I don't think the alpha split is right though because it split A-H, I-I, J-J, K-P, & Q-Z. That wouldn't be an even split...

 

[Eric W]

My first thought is to check the G formulas. They should show something. It's possible that since they are array formulas, that's causing a problem. I managed to rewrite them as ordinary formulas. Put this formula in G1, and copy it down to G5.

=SUMPRODUCT(--(CODE($A$1:$A$153)>=D1+64),--(CODE($A$1:$A$153)<=E1+64))

Then try again. This may seem convoluted, but once it's set up, it'll be easy to maintain.

 

[lucolem]

Worked! This was my result -

A-D 22304
E-J 19792
K-N 23556
O-S 23133
T-Z 11892

I may need to look at splitting some of the alpha into 2 characters because T-Z is so low compared to the others. Maybe O-Sh and Si-Z or something along those lines. Nothing is ever easy!

Thank you again for your help! This give me an idea of where I need to go!

 

[Eric W]

Glad to help!