Help on Selecting Random Teams

[VinceF]

Thanks for any assistance..
I'm trying to make 8 four person teams by random selection. I'm able to randomly set a team assignment but need help to bring the individuals into the corresponding teams.

Excel 2016

VinceF

Loon Master Golf Program 3-27-23.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2						PLAYERS PER TEAM		4
3
4											TEAM	TEAM	TEAM	TEAM	TEAM	TEAM	TEAM	TEAM
5		PLAYER	INDEX	ABILITY		RANDOM	RANK	GROUPING	TEAM		1	2	3	4	5	6	7	8
6		TY	2.8	A		0.665031	7	1.75	2		#N/A
7		KELLY	3.1	A		0.680029	6	1.5	2		#N/A
8		MARTIN	3.5	A		0.054448	24	6	6		#N/A
9		MONEY	4.0	A		0.239187	18	4.5	5		#N/A
10		RON	4.1	A		0.056927	23	5.75	6
11		MUELS	4.6	A		0.838302	4	1	1
12		ANTHONY	5.0	B		0.692151	5	1.25	2
13		SCOOTER	5.0	B		0.965787	1	0.25	1
14		TREVOR	5.0	B		0.542113	10	2.5	3
15		WALT	5.4	B		0.876263	3	0.75	1
16		JAKE	5.8	B		0.410103	11	2.75	3
17		BRET	6.0	B		0.320427	16	4	4
18		WOODY	6.4	C		0.205866	19	4.75	5
19		DREW	6.5	C		0.563971	9	2.25	3
20		JUSTIN	7.0	C		0.147886	21	5.25	6
21		VINNY	7.5	C		0.323723	14	3.5	4
22		UPS MIKE	7.6	C		0.878195	2	0.5	1
23		BONES	8.0	C		0.336491	12	3	3
24		CIP	9.0	D		0.323042	15	3.75	4
25		DAVE	11.0	D		0.568208	8	2	2
26		BILLY	12.0	D		0.326935	13	3.25	4
27		PAUL	14.0	D		0.073678	22	5.5	6
28		PHIL	14.0	D		0.185735	20	5	5
29		MIKE S	15.3	D		0.307474	17	4.25	5
30
31
32
Sheet1

Cell Formulas
Range	Formula
H6:H29	H6	=RANK(G6,$G$6:$G$29)
I6:I29	I6	=H6/$I$2
J6:J29	J6	=CEILING(I6,1)
L6:L9	L6	=INDEX($J$6:$J$29,MATCH($L$5,$C$6:$C$29,0))

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$L$5:$S$5	L6:L9

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C6:C29	Cell Value	duplicates	text	NO
C6:C29	Cell Value	duplicates	text	NO

 

[Fluff]

How about

=IFERROR(INDEX($C$6:$C$29,AGGREGATE(15,6,(ROW($C$6:$C$29)-ROW($C$6)+1)/($J$6:$J$29=L$5),ROWS(L$6:L6))),"")

 

[VinceF]

Fluff,

Thank you very much for your assistance, it is working perfectly.

VinceF

 

[Fluff]

You're welcome & thanks for the feedback.