Generating names for groups

[Tigerexcel]

Group allocator.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1							How many people?	21
2	Name	Rand	Rank	Grouping	Team		Team size	7		Team no.	Count
3	Vic	0.843724	4	0.571429	1					1	8
4	Bob	0.551336	9	1.285714	2					2	6
5	Kev	0.785574	6	0.857143	1					3	7
6	Mary	0.529755	10	1.428571	2					4	0
7	Fred	0.974474	1	0.142857	1					5	0
8	Steve	0.522386	11	1.571429	2					6	0
9	Jack	0.671763	8	1.142857	2					7	0
10	Jake	0.040347	19	2.714286	3					8	0
11	Mick	0.926923	2	0.285714	1
12	Tom	0.203266	16	2.285714	3			Team 1	Team 2	Team 3	Team 4	Team 5	Team 6	Team 7
13	Jess	0.232937	15	2.142857	3
14	Jo	0.057397	18	2.571429	3
15	Joe	0.905269	3	0.428571	1
16	Sue	0.751883	7	1	1
17	Sharon	0.818322	5	0.714286	1
18	Noel	0.183788	17	2.428571	3
19	Jack S	0.521619	12	1.714286	2
20	Alan	0.329118	15	2.142857	3
21	Mike	0.356218	13	1.857143	2
22	****	0.864098	4	0.571429	1
23	ken	0.21202	17	2.428571	3
Sheet1

Cell Formulas
Range	Formula
H1	H1	=ROWS(Table1[Team])
C21,C3:C19	C3	=RANK(B3,$B$3:$B$21)
D3:D23	D3	=C3/$H$2
E3:E23	E3	=CEILING(D3,1)
C20,C23	C20	=RANK(B20,$B$3:$B$23)
C22	C22	=RANK(B22,$B$3:$B$22)
K3:K10	K3	=COUNTIF(Table1[Team],J3)

The idea here is to produce lists of names that belong to each allocated group once the main spreadsheet has randomly allocated the names to the groups.
Desired output under Team 1 would have Vic, Kev, Fred, Mick etc. so anyone who records a 1 under Team. Under Team 2 would be Bob, Mary, Steve etc.

This output would be listed under Team 1, Team 2 etc. which are H12, I12 etc.

Column B has a formula for randomising the data, RAND(), I've converted this column to values for the sake of this example but I do have another problem with this spreadsheet. The groups don't always come out evenly and in this instance with 21 people I get groups of 8,7 and 6 instead of 3 groups of 7 which is what I need. I find that I need to recalculate a few times before the groups are even, perhaps my formulae could be improved upon. Looking for a formula/function solution to both the output and if possible the even group creation.

 

[GraH]

Hi,

What about this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1					How many people?	21
2	Name	Rand	Rank		Team size	7		Team 1	Team 2	Team 3	Team 4	Team 5	Team 6	Team 7
3	Vic	0,977464	1					Tom	Jo	Fred
4	Bob	0,375229	16					Mike	Mary	Noel
5	Kev	0,930121	3					****	Sue	Steve
6	Mary	0,548501	13					Jack S	Jake	Joe
7	Fred	0,022852	21					Kev	Mick	Jack
8	Steve	0,142003	19					Sharon	Jess	Bob
9	Jack	0,36989	17					Vic	Alan	ken
10	Jake	0,634246	11
11	Mick	0,659369	10
12	Tom	0,871318	7
13	Jess	0,660376	9
14	Jo	0,478442	14
15	Joe	0,243584	18
16	Sue	0,556796	12
17	Sharon	0,956967	2
18	Noel	0,022876	20
19	Jack S	0,907771	4
20	Alan	0,759401	8
21	Mike	0,872437	6
22	****	0,876342	5
23	ken	0,470034	15
24
Sheet1 (2)

Cell Formulas
Range	Formula
F1	F1	=ROWS(Table13[Name])
B3:B23	B3	=RAND()
C3:C23	C3	=RANK(B3,$B$3:$B$23)
H3:N14	H3	=IFERROR(IF(ROW(H1)>$F$2,"",INDEX(Table13[[Name]:[Name]],MATCH(($F$2*COLUMNS($H$2:H$2))-ROWS(H$2:H2)+1,Table13[[Rank ]:[Rank ]],0))),"")

For reference (and because I need to practice), with XLOOKUP:
=IFERROR(IF(ROW(H1)>$F$2,"",XLOOKUP(($F$2*COLUMNS($H$2:H$2))-ROWS(H$2:H2)+1,Table13[[Rank ]:[Rank ]],Table13[[Name]:[Name]],,0,1)),"")

 

[Tigerexcel]

Hi Gra,

Thanks I'm working through it. Should there be anything in H1? Also when I download your spreadsheet the formulae under Team 1, Team 2 etc have the formulae as text rather than values.

 

[GraH]

Hi Tigerexcel,

Sometimes I've the same issue when I paste xl2bb in my workbooks. I'm on EU settings. Probably ";" needs to be replaced by ",". Though it seems they have been replaced by the add-on just fine.
Press F2 and confirm the formula again. I suspect that will work.

H1 is only used as a reference for row. Does not matter whether it is empty or not.

 

[Tigerexcel]

Hi again Gra,

I've tried replacing and re-typing some of the formulae to no avail. I suspect it's the table references that are throwing the formulae out. What I can see is that your formulae are very clever and streamline quite a few steps. I'll have to try and remove the table formatting so that it's just a normal range to try and understand this better.

 

[Tigerexcel]

I've removed the Table syntax in the formulae and now it all works. It's so clever, I particularly like how you don't have to keep randomising until you get even numbers in the groups.
I don't quite understand what the formula is doing in the MATCH component, could you explain that please?

 

[GraH]

Good question.
Match returns a position from the rank range, index then returns the name of that position.

You were indeed making it more complicaties then needed ?. I guess we all go through such a state.

Maybe the table reference to the rank column was a problem. There was a trailing space in the sample. It is not there in your workbook?

Thanks for again positive feedback. I like clever, should do that more often ?

 

[Tigerexcel]

Thanks Gra, but how does your formula know how to set the values in a 3 x 7 matrix or 5 x 4, in other words how is the positioning decided upon via your formula?

 

[Tigerexcel]

Thanks Gra, but how does your formula know how to set the values in a 3 x 7 matrix or 5 x 4, in other words how is the positioning decided upon via your formula?

Never mind, I can see how the IF function does that now.

 

[GraH]

I was actually typing a reply:
Let's break down the formula: $F$2*COLUMNS($H$2:H$2))-ROWS(H$2:H2)+1

It's size * columns, so you get 7, 14 , 21, 28, etc. Then subtract rows 0, 1, 2, 3 until the row number exceeds the team size, indeed like you said the IF-part.