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]

My pleasure.

 

[paulgunther]

My pleasure.

I just edited that reply

 

[Fluff]

Surely if the non random column is blank the formula should calculate the group.

 

[paulgunther]

Surely if the non random column is blank the formula should calculate the group.

Absolutely. And in the end I delete the rows in the table that do not have a player assigned, however is there a way to check to see if the Player column is not blank before calculating the group assignment?
I may have just answered my own question. Let me try nesting the group formula in an "if statement"....

 

[paulgunther]

Yep. That worked.

 

[Fluff]

Glad you sorted it.

 

[paulgunther]

Glad you sorted it.

Hello again,
I'm now trying to incorporate this into a google doc spreadsheet and this formula may be too sophisticated to run. I've adapted all the references to match the "dumbed down" spreadsheet in Google Docs and I'm now getting this error: "Function SORT parameter 2 expects number values. But '' is a empty and cannot be coerced to a number." Also, I'm not sure how to show this spreadsheet here.

 

[Fluff]

I know very little about Sheets & so cannot help with that.

 

[paulgunther]

I know very little about Sheets & so cannot help with that.

Same here. I just found that I need to let others use this sheet and it was the easiest way to share... or so I thought. Thanks for your prompt reply!!

 

[Fluff]

Rather than using Sheets to share the file, why not use Excel Online? That way the formula will still work.