rank / sort / split

[domdc1]

Helo all,
ill try to make this as painless as possible. I have 10000 names each with a ranking of either a,b,c or d. I have to equally split these individuals into groups so in the end i will have the same number of a,b,c and d in each group. I could take the long way and simply sort by ranking & divide accordingly. Any easy assistance will be greatly appreciated.

 

[Ralajer]

For the purpose of this solution I will assume that Column A = Name, Column B = Rank, and all other columns are free. Also there are column headers

One solution without using vba is to sort by rank then in C2 place this formula =IF(B2=B1,1+C1,1) fill it down. This will produce a 1,2,3..,N that restarts for each rank. Then copy column C and paste special values. Then sort column C ascending (A-Z) this will produce something along the lines of

Excel Workbook
	A	B	C
1	Name	Rank	Sort
2	Name 0001	a	1
3	Name 0006	b	1
4	Name 0003	c	1
5	Name 0002	d	1
6	Name 0004	a	2
7	Name 0008	b	2
8	Name 0019	c	2
9	Name 0005	d	2
10	Name 0009	a	3
11	Name 0010	b	3
12	Name 0031	c	3
13	Name 0007	d	3
...

Then the just select number of sets to form your group size and deal with the in balance at the bottom.

This could also be done in VBA but it might not worth it if this something that you don't have to do very often.

 

[domdc1]

That was perfect, thanks for not confusing me with VBA. Greatly appreciated

 

[domdc1]

I will add one more twist. I want to cap the number or groups where the sort appears. For example if i want to sort only within groups 1-5 and gropus 6,7,8 or 9 need to be split within groups 1-5. As stated on the example below there are 2 names within group A which are to be sorted to sort 6 and 7 but i want them to be sorted within 1-5.

thanks again

Name Rank Sort
Name 0001 a 1
Name 0004 a 2
Name 0009 a 3
Name 00311 a 4
Name 00319 a 5 <---- should be 1
Name 00323 a 6 <---- should be 2
Name 00327 a 7 <---- should be 3
Name 0006 b 1
Name 0008 b 2
Name 00312 b 3
Name 00315 b 4
Name 00324 b 5
Name 0003 c 1
Name 0010 c 2
Name 0019 c 3
Name 0031 c 4
Name 00321 c 5
Name 00325 c 6 <---- should be 1
Name 003169 c 7 <---- should be 2
Name 00326 C 8 <---- should be 3
Name 00318 c 9 <---- should be 4
Name 0002 d 1
Name 0005 d 2
Name 0007 d 3
Name 00320 d 4
Name 00322 d 5

 

[Ralajer]

Try the below solution instead of my previous solution. Should be easier as you don't have to sort and copy paste. If you use a pivot table you can to view the groups more easily.

Excel Workbook
	A	B	C	D	E	F	G
1	Name	Rank	Group		Total Names	Names per Group	Names per Rank per Group
2	Name 0002	C	Group 1		10000	100	25
3	Name 0003	C	Group 1
4	Name 0004	A	Group 1
5	Name 0005	A	Group 1
6	Name 0007	C	Group 1
7	Name 0008	D	Group 1
8	Name 0009	A	Group 1
...

Excel 2010

Cell Formulas
Range	Formula
E2	=COUNTA(A:A)-1
G2	=F2/4
C2	="Group "&INT((COUNTIF(B$2:B2,B2)-1)/$G$2+1)