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]

Also if you are happy converting your table into a normal range you could use

Fluff.xlsm
	I	J	K	L	M	N	O	P
1
2				Non RandomCard	Player	Start	Random	Static
3	Card Size	4			Player 1	4	0.033345	0.505865
4				8	Player 2	8	0.069829	0.474071
5	Card	Assigned Hole			Player 3	9	0.619176	0.011008
6	1	1			Player 4	4	0.600187	0.447861
7	2	8			Player 5	11	0.167149	0.149915
8	3	10		8	Player 6	8	0.985988	0.235066
9	4	12			Player 7	12	0.293185	0.562627
10	5	6			Player 8	10	0.779119	0.688805
11	6	4			Player 9	8	0.904664	0.766998
12	7	2			Player 10	6	0.129357	0.544279
13	8	11		13	Player 11	13	0.106937	0.312539
14	9	13			Player 12	10	0.653106	0.745614
15	10	9			Player 13	11	0.531883	0.121125
16	11	3			Player 14	13	0.124868	0.094565
17	12	7			Player 15	1	0.674405	0.895886
18	13	18			Player 16	2	0.045348	0.260977
19	14	5		13	Player 17	13	0.383567	0.021962
20					Player 18	11	0.294947	0.144665
21					Player 19	8	0.316225	0.779062
22					Player 20	12	0.119425	0.563879
23					Player 21	1	0.988649	0.801686
24					Player 22	11	0.233312	0.151359
25					Player 23	10	0.362224	0.605264
26					Player 24	4	0.76944	0.533054
27					Player 25	6	0.172892	0.544648
28					Player 26	13	0.384357	0.057422
29					Player 27	2	0.697654	0.175729
30					Player 28	1	0.690685	0.788707
31					Player 29	9	0.517679	0.025451
32					Player 30	9	0.629912	0.053017
33					Player 31	6	0.583881	0.53648
34					Player 32	1	0.579104	0.864358
35					Player 33	12	0.779837	0.560422
36					Player 34	2	0.644705	0.28677
37					Player 35	9	0.120537	0.028894
38					Player 36	2	0.114662	0.305232
39					Player 37	12	0.067591	0.591905
40					Player 38	10	0.865982	0.618142
41					Player 39	6	0.345685	0.540354
42					Player 40	4	0.924334	0.400991
Original

Cell Formulas
Range	Formula
N3:N42	N3	=LET(x,L3:L42,y,INDEX(Card,MATCH(x,Assigned_Hole,0)),f,FILTER(y*$J$3,ISNUMBER(y)),tr,ROWS(x),r,ROWS(f),s,f-MMULT((f=TRANSPOSE(f))*(SEQUENCE(r)>=SEQUENCE(,r)),SEQUENCE(r,,,0))+1,IF(x<>"",x,VLOOKUP(INDEX(FILTER(INT(SEQUENCE(tr,,,1/$J$3)),ISNA(MATCH(SEQUENCE(tr),s,0))),XMATCH(P3:P42,SORT(FILTER(P3:P42,x=""),,-1),0)),Hole_Assignment,2)))
O3:O42	O3	=RANDARRAY(40)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Assigned_Hole	=Original!$J$6:$J$19	N3
Card	=Original!$I$6:$I$19	N3
Hole_Assignment	=Original!$I$6:$J$19	N3

 

[BSALV]

VBA does only copy random values, all the rest is done with formulas.
LET-functions are above my abilities
paulgunter

paulgunther.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
2	Non RandomCard	Player	Hole	Static								44
3		Player 1	10	0,819086973		Card Size	4		Assigned Hole	non random	start	aantal
4	8	Player 2	8	1		holes	12		1		1	3
5		Player 3	10	0,800771881		product	48		2		5	3
6		Player 4	9	0,765164619					3		9	4
7		Player 5	4	0,428881136					4		13	4
8	8	Player 6	8	1					5		17	4
9		Player 7	4	0,401672163					6		21	3
10		Player 8	12	1					7		25	4
11		Player 9	8	0,725085955					8	2	29	4
12		Player 10	2	0,174379751					9		31	4
13		Player 11	7	0,722360143					10		35	3
14		Player 12	7	0,688338328					11		39	4
15		Player 13	12	1					12		43	4
16		Player 14	2	0,16617871
17		Player 15	5	0,449047086
18		Player 16	1	0,002827933
19		Player 17	12	1
Blad1

Cell Formulas
Range	Formula
L2	L2	=SUM(TBL_Holes[aantal])
J4:J15	J4	=COUNTIF(TBL_Players[Non RandomCard],[@[Assigned Hole]])
K4:K15	K4	=IF(ROW()-ROW(TBL_Holes[[#Headers],[Assigned Hole]])=1,1,SUM(K3,$G$3,-J3))
L4:L15	L4	=COUNTIF(TBL_Players[Hole],[@[Assigned Hole]])
G4	G4	=ROWS(TBL_Holes[Assigned Hole])
G5	G5	=+G3*G4
C3:C19	C3	=IF([@[Non RandomCard]]<>"",[@[Non RandomCard]],INDEX(TBL_Holes[Assigned Hole],MATCH(RANK([@Static],MyRandomArray,1),TBL_Holes[start],1)))

 

[paulgunther]

Hi Fluff,
I have to keep these as tables since this is just a small part of a larger data base I have working.
I applied the formulas in your example and I'm getting #CALC! errors. I double checked all the cell references and can't seem to find the error.

Random Card.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2	Non Random Card	Player	Start	Group	Random	Static
3		Player 01	#CALC!	#CALC!	0.999133202	0.549061703			Card Size	4
4	8	Player 02	8		0.683751952	0.701012055
5		Player 03	#CALC!	#CALC!	0.757803587	0.842910582			Card	Assigned Hole
6		Player 04	#CALC!	#CALC!	0.609899855	0.090150928			1	1
7	8	Player 05	8		0.831196815	0.554184556			2	8
8		Player 06	#CALC!	#CALC!	0.383806629	0.763549902			3	10
9		Player 07	#CALC!	#CALC!	0.53894429	0.842310844			4	12
10	8	Player 08	8		0.00211563	0.873383963			5	6
11		Player 09	#CALC!	#CALC!	0.937993235	0.638797798			6	4
12		Player 10	#CALC!	#CALC!	0.276149333	0.259137202			7	2
13		Player 11	#CALC!	#CALC!	0.647243974	0.17285007			8	11
14	12	Player 12	12		0.538482315	0.253394704			9	13
15		Player 13	#CALC!	#CALC!	0.606828553	0.286938851			10	9
16		Player 14	#CALC!	#CALC!	0.652336522	0.030636228			11	3
17	12	Player 15	12		0.674354688	0.857215132			12	7
18		Player 16	#CALC!	#CALC!	0.986646209	0.180295436			13	18
19		Player 17	#CALC!	#CALC!	0.904253853	0.914557395			14	5
20		Player 18	#CALC!	#CALC!	0.692543977	0.347481513
21	12	Player 19	12		0.151837438	0.710534183
22	12	Player 20	12		0.757229263	0.190790729
23		Player 21	#CALC!	#CALC!	0.077728838	0.416866529
24		Player 22	#CALC!	#CALC!	0.300234569	0.123704669
25		Player 23	#CALC!	#CALC!	0.155513423	0.508234893
26		Player 24	#CALC!	#CALC!	0.973114541	0.163258862
27		Player 25	#CALC!	#CALC!	0.25327151	0.310161536
28		Player 26	#CALC!	#CALC!	0.482144203	0.581927594
29		Player 27	#CALC!	#CALC!	0.512435881	0.201880639
30		Player 28	#CALC!	#CALC!	0.23868012	0.894778195
31		Player 29	#CALC!	#CALC!	0.110238947	0.311855049
32		Player 30	#CALC!	#CALC!	0.945280316	0.179164693
33		Player 31	#CALC!	#CALC!	0.533405892	0.645835207
34		Player 32	#CALC!	#CALC!	0.459465177	0.004685599
35		Player 33	#CALC!	#CALC!	0.217975076	0.151117839
36		Player 34	#CALC!	#CALC!	0.542290175	0.557757734
37		Player 35	#CALC!	#CALC!	0.25009835	0.225325764
38		Player 36	#CALC!	#CALC!	0.909900834	0.201272296
39		Player 37	#CALC!	#CALC!	0.905524727	0.199896846
40		Player 38	#CALC!	#CALC!	0.482106607	0.956558235
41		Player 39	#CALC!	#CALC!	0.545237876	0.497686092
42		Player 40	#CALC!	#CALC!	0.06316289	0.562443998
WORKING

Cell Formulas
Range	Formula
C3:C42	C3	=IF([@[Non Random Card]]<>"",[@[Non Random Card]],VLOOKUP([@Group],Hole_Assignment,2))
D3:D42	D3	=LET(x,[Non Random Card],y,INDEX(Card,MATCH(x,Assigned_Hole,0)),f,FILTER(y*$J$3,ISNUMBER(y)),tr,ROWS(x),r,ROWS(f),s,f-MMULT((f=TRANSPOSE(f))*(SEQUENCE(r)>=SEQUENCE(,r)),SEQUENCE(r,,,0))+1,IF([@[Non Random Card]]<>"","",INDEX(FILTER(INT(SEQUENCE(tr,,,1/$J$3)),ISNA(MATCH(SEQUENCE(tr),s,0))),XMATCH([@Static],SORT(FILTER([Static],x=""),,-1),0))))
E3:E42	E3	=RAND()

 

[Fluff]

Have you created the two named ranges Card & Assigned_Hole?

 

[paulgunther]

Have you created the two named ranges Card & Assigned_Hole?

They're currently table names only. Should I actually "Name" them instead?

 

[paulgunther]

Wow!! I removed the tables and named the ranges instead. Works like a charm!
Thanks so much for all your help!!
There are a lot of unfamiliar formulas in here that I can study and learn. I appreciate that the most.

 

[Fluff]

If I5:J19 is a table, you can just use table references instead.

Glad we could help & thanks for the feedback.

 

[paulgunther]

Okay, so there is one ch ink in the armor.
If ALL cells are blank in the "Non_Random_Card" column, an error is generated in the "Group" formula.

 

[Fluff]

Good point, try this slight mod

=LET(x,[Non RandomCard],y,INDEX(Card,MATCH(x,Assigned_Hole,0)),f,FILTER(y*$J$3,ISNUMBER(y),""),tr,ROWS(x),r,ROWS(f),s,f-MMULT((f=TRANSPOSE(f))*(SEQUENCE(r)>=SEQUENCE(,r)),SEQUENCE(r,,,0))+1,IF([@[Non RandomCard]]<>"","",INDEX(FILTER(INT(SEQUENCE(tr,,,1/$J$3)),ISNA(MATCH(SEQUENCE(tr),s,0))),XMATCH([@Static],SORT(FILTER([Static],x=""),,-1),0))))

 

[paulgunther]

That sorta fixed it. Now it still calculates even if the Player column is blank. Previously, it would produce an error, which I could hide with conditional formatting. Now it actually assign a number.