Creating exception to random Team generator

[paulgunther]

I have a spreadsheet that assigns players to random holes (Start) based on a chosen number of players per card (I3). It's fairly simple and I also have a macro assigned to a button that copies the "random" column and pastes values to the "static" column to re-randomize at will. The "hole_assignment" table is to assign the holes in a specific, non-sequential order. This works fine as designed.

Question: I would like to occasionally assign certain players to specific holes (column A) and have the Start column account for the pre-assigned starting hole and randomize the rest.

Example: Player 2, pre-assigned hole 8, Player 6 pre-assigned hole 8. I now need the randomizer to only assign 2 additional players to hole 8 and automatically place those players on their respective pre-assigned holes.

Random Card.xlsm
	A	B	C	D	E	F	G	H	I
1
2	Non RandomCard	Player	Start	Group	Random	Static
3		Player 1	2	7	0.226304354	0.262627438		Card Size	4
4	8	Player 2	8	2	0.387937938	0.796961803
5		Player 3	10	3	0.670163485	0.68759244		Card	Assigned Hole
6		Player 4	4	6	0.538938097	0.351944739		1	1
7		Player 5	10	3	0.640843928	0.746840218		2	8
8	8	Player 6	10	3	0.873793276	0.75425269		3	10
9		Player 7	12	4	0.565404705	0.627886504		4	12
10		Player 8	1	1	0.498849178	0.879518872		5	6
11		Player 9	9	10	0.093233421	0.049763293		6	4
12		Player 10	13	9	0.79731487	0.098552824		7	2
13		Player 11	1	1	0.681900928	0.889456719		8	11
14		Player 12	6	5	0.740893852	0.569148347		9	13
15		Player 13	1	1	0.34068696	0.865395664		10	9
16		Player 14	4	6	0.923313534	0.369695062		11	3
17		Player 15	13	9	0.753195094	0.13534933		12	7
18		Player 16	11	8	0.887033833	0.211141552		13	18
19		Player 17	4	6	0.660317525	0.517424354		14	5
20		Player 18	6	5	0.753272601	0.554959501
21		Player 19	6	5	0.515377527	0.524942895
22		Player 20	11	8	0.274246457	0.256438207
23		Player 21	13	9	0.425123745	0.149393574
24		Player 22	9	10	0.63186222	0.026967303
25		Player 23	9	10	0.34139667	0.096754073
26		Player 24	4	6	0.805169222	0.388082165
27		Player 25	12	4	0.499807002	0.615633054
28		Player 26	11	8	0.772128409	0.217260568
29		Player 27	2	7	0.72363209	0.292360797
30		Player 28	1	1	0.16761252	0.905538699
31		Player 29	2	7	0.496709159	0.270637506
32		Player 30	6	5	0.172285652	0.564299634
33		Player 31	2	7	0.187167301	0.261168275
34		Player 32	8	2	0.463677877	0.784721414
35		Player 33	8	2	0.339954398	0.782527076
36		Player 34	8	2	0.547129477	0.796425025
37		Player 35	11	8	0.330512737	0.201663754
38		Player 36	10	3	0.704270874	0.764741023
39		Player 37	12	4	0.120979336	0.597508326
40		Player 38	12	4	0.62031941	0.639348663
41		Player 39	13	9	0.944448148	0.195836014
42		Player 40	9	10	0.729146253	0.047685875
WORKING

Cell Formulas
Range	Formula
C3:C42	C3	=VLOOKUP([@Group],Hole_Assignment,2)
D3:D42	D3	=CEILING(RANK([@Static],[Static])/$I$3,1)
E3:F42	E3	=RAND()

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:A42	Expression	=COUNTIF($A$3:$A$42, A3) = 2	text	NO
A3:A42	Expression	=COUNTIF($A$3:$A$42, A3) = 3	text	NO
A3:A42	Expression	=COUNTIF($A$3:$A$42, A3) = 4	text	NO
C3:C42	Expression	=COUNTIF($C$3:$C$42, C3) = 2	text	NO
C3:C42	Expression	=COUNTIF($C$3:$C$42, C3) = 3	text	NO
C3:C42	Expression	=COUNTIF($C$3:$C$42, C3) = 4	text	NO
C3:C42	Expression	=COUNTIF($C$3:$C$42, C3) = 5	text	NO
C3:C42	Expression	=COUNTIF($C$3:$C$42, C3) > 5	text	NO
B3:B42	Cell Value	duplicates	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B2	Any value
C3:D42	Any value

 

[Fluff]

No idea if this will work in Sheets, but it will work with Excel online. I've re-done it with some of the newer functions.

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2	Non RandomCard	Player	Start	Group	Random	Static
3		Player 1	4	6	0.45642663	0.5058647			Card Size	4
4	8	Player 2	8	0	0.98121836	0.47407113
5		Player 3	9	10	0.55034696	0.01100784			Card	Assigned Hole
6		Player 4	4	6	0.57114923	0.44786082			1	1
7		Player 5	11	8	0.29975018	0.14991548			2	8
8	8	Player 6	8	0	0.31710246	0.23506583			3	10
9		Player 7	12	4	0.31030169	0.56262691			4	12
10		Player 8	10	3	0.3080271	0.68880502			5	6
11		Player 9	8	2	0.52754988	0.76699758			6	4
12		Player 10	6	5	0.55559147	0.54427856			7	2
13	13	Player 11	13	0	0.61690189	0.3125385			8	11
14		Player 12	10	3	0.32859315	0.74561371			9	13
15		Player 13	11	8	0.84902781	0.12112482			10	9
16		Player 14	13	9	0.59550147	0.09456506			11	3
17		Player 15	1	1	0.11002376	0.89588577			12	7
18		Player 16	2	7	0.78039123	0.26097715			13	18
19	13	Player 17	13	0	0.36542263	0.02196248			14	5
20		Player 18	11	8	0.34965553	0.14466525
21		Player 19	8	2	0.86115991	0.77906214
22		Player 20	12	4	0.89402076	0.56387949
23		Player 21	1	1	0.29117498	0.80168604
24		Player 22	11	8	0.97574263	0.15135902
25		Player 23	10	3	0.71071064	0.60526428
26		Player 24	4	6	0.00225196	0.53305447
27		Player 25	6	5	0.55140398	0.54464767
28		Player 26	13	9	0.42879572	0.05742175
29		Player 27	2	7	0.18322232	0.17572865
30		Player 28	1	1	0.72160522	0.78870732
31		Player 29	9	10	0.03631104	0.02545128
32		Player 30	9	10	0.70085794	0.05301655
33		Player 31	6	5	0.27229627	0.53647952
34		Player 32	1	1	0.24372605	0.86435832
35		Player 33	12	4	0.30999804	0.56042211
36		Player 34	2	7	0.46035518	0.2867701
37		Player 35	9	10	0.66186714	0.02889428
38		Player 36	2	7	0.43953529	0.30523173
39		Player 37	12	4	0.72535775	0.591905
40		Player 38	10	3	0.17858491	0.61814166
41		Player 39	6	5	0.98715757	0.54035428
42		Player 40	4	6	0.58743488	0.40099069
Home

Cell Formulas
Range	Formula
C3:C42	C3	=IF([@[Non RandomCard]]<>"",[@[Non RandomCard]],VLOOKUP([@Group],Hole_Assignment,2,0))
D3:D42	D3	=LET(nrc,[Non RandomCard],y,XLOOKUP(nrc,Hole_Assignment[Assigned Hole],Hole_Assignment[Card],0),tr,ROWS(nrc),c,SEQUENCE(CEILING(tr/$J$3,1)),XLOOKUP([@Static],SORT(FILTER([Static],nrc=""),1,-1),TAKE(TOCOL(IF(SEQUENCE(,$J$3)>BYROW(c,LAMBDA(br,SUM(IF(y=br,1,0)))),c,1/0),2),tr-COUNT(nrc)),0))
E3:E42	E3	=RAND()

 

[paulgunther]

No idea if this will work in Sheets, but it will work with Excel online. I've re-done it with some of the newer functions.

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2	Non RandomCard	Player	Start	Group	Random	Static
3		Player 1	4	6	0.45642663	0.5058647			Card Size	4
4	8	Player 2	8	0	0.98121836	0.47407113
5		Player 3	9	10	0.55034696	0.01100784			Card	Assigned Hole
6		Player 4	4	6	0.57114923	0.44786082			1	1
7		Player 5	11	8	0.29975018	0.14991548			2	8
8	8	Player 6	8	0	0.31710246	0.23506583			3	10
9		Player 7	12	4	0.31030169	0.56262691			4	12
10		Player 8	10	3	0.3080271	0.68880502			5	6
11		Player 9	8	2	0.52754988	0.76699758			6	4
12		Player 10	6	5	0.55559147	0.54427856			7	2
13	13	Player 11	13	0	0.61690189	0.3125385			8	11
14		Player 12	10	3	0.32859315	0.74561371			9	13
15		Player 13	11	8	0.84902781	0.12112482			10	9
16		Player 14	13	9	0.59550147	0.09456506			11	3
17		Player 15	1	1	0.11002376	0.89588577			12	7
18		Player 16	2	7	0.78039123	0.26097715			13	18
19	13	Player 17	13	0	0.36542263	0.02196248			14	5
20		Player 18	11	8	0.34965553	0.14466525
21		Player 19	8	2	0.86115991	0.77906214
22		Player 20	12	4	0.89402076	0.56387949
23		Player 21	1	1	0.29117498	0.80168604
24		Player 22	11	8	0.97574263	0.15135902
25		Player 23	10	3	0.71071064	0.60526428
26		Player 24	4	6	0.00225196	0.53305447
27		Player 25	6	5	0.55140398	0.54464767
28		Player 26	13	9	0.42879572	0.05742175
29		Player 27	2	7	0.18322232	0.17572865
30		Player 28	1	1	0.72160522	0.78870732
31		Player 29	9	10	0.03631104	0.02545128
32		Player 30	9	10	0.70085794	0.05301655
33		Player 31	6	5	0.27229627	0.53647952
34		Player 32	1	1	0.24372605	0.86435832
35		Player 33	12	4	0.30999804	0.56042211
36		Player 34	2	7	0.46035518	0.2867701
37		Player 35	9	10	0.66186714	0.02889428
38		Player 36	2	7	0.43953529	0.30523173
39		Player 37	12	4	0.72535775	0.591905
40		Player 38	10	3	0.17858491	0.61814166
41		Player 39	6	5	0.98715757	0.54035428
42		Player 40	4	6	0.58743488	0.40099069
Home

Cell Formulas
Range	Formula
C3:C42	C3	=IF([@[Non RandomCard]]<>"",[@[Non RandomCard]],VLOOKUP([@Group],Hole_Assignment,2,0))
D3:D42	D3	=LET(nrc,[Non RandomCard],y,XLOOKUP(nrc,Hole_Assignment[Assigned Hole],Hole_Assignment[Card],0),tr,ROWS(nrc),c,SEQUENCE(CEILING(tr/$J$3,1)),XLOOKUP([@Static],SORT(FILTER([Static],nrc=""),1,-1),TAKE(TOCOL(IF(SEQUENCE(,$J$3)>BYROW(c,LAMBDA(br,SUM(IF(y=br,1,0)))),c,1/0),2),tr-COUNT(nrc)),0))
E3:E42	E3	=RAND()

I need some clarification. What is "Hole_Assignment" referencing? I have the range name "Assigned_Hole" referencing cells J5:J19 in your example, but I do not reference "Hole_Assignment" anywhere.

 

[Fluff]

I thought that was a structured Table in I5:J19

 

[paulgunther]

It is now...

 

[paulgunther]

Am I understanding this is a recursive LAMBDA formula? (I know just enough to get me in trouble)

Also, what is tr = in the LET function?

 

[Fluff]

No it's not recursive, it just counts how many times the group/card appears in col A (after converting the hole number to the group)