Sorting players into groups - need help

[Vintage79]

Hello all,

I have a rather complicated problem to solve! Here is how the raw data will appear, although there are many more rows of course:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
3	Name	ID	Team	Time	Avail.	Notes	1	2	3	4	5
4	Name A	186	Team 4	E	Y		x		x
5	Name B	528	Team 4	E	Y				x	x
6	Name C	331	Team 4	E	Y			x		x	x
7	Name D	204	Team 4	L	N						x
8	Name E	701	Team 4	L	Y		x			x
9	Name F	611	Team 3	E	N				x	x
12	Name G	227	Team 3	E	M					x
13	Name H	141	Team 3	E	Y			x	x	x
Sheet1

This is a list of players who need to be individually assigned to a specific location. Column D shows whether the person can play in the early game or the late one, column E shows whether they are available at all (Yes, No, or Maybe). Columns G to P show groups from 1 to 5 (there are many more on the full sheet). An 'x' marks which groups a player is eligible to play in.

Excel 2010
	B	C	D
5	Groups
6	Group 1	Early	Late
7	Loc. A	2	2
8	Loc. B	6	4
9	Loc. C	0	3
10	Loc. D	0	0
11	Loc. E	2	0
12
13	Group 2
14	Loc. F	1	0
15	Loc. G	3	3
16	Loc. H	0	0
17
18	Group 3
19	Loc. I	2	2
20
21	Group 4
22	Loc. J	2	4
23	Loc. K	8	8
24	Loc. L	0	4
25	Loc. M	2	1
26	Loc. N	3	3
27	Loc. O	4	0
28
29	Group 5
30	Loc. P	2	5
31	Loc. Q	1	0
Locations

Each group contains unique locations, and each location has a quota that must be filled for the early game and the late game. The goal is to assign players to locations randomly. For example, 'Name A' is eligible to play in groups 1 and 3. So, 'Name A' needs to be placed into one of these groups, and then be assigned a specific location, randomly, from the list of locations available in that group. I suppose a more simple way to attack this would just be to say that 'Name A' needs to be assigned randomly to either Location A, B, C, D, E or I. I would like to end up with a list the same as A to F shown in the first page, but with the assigned location for each player in the next column.

Players who are marked as 'N' or 'M' in column E do not need to be counted. The Ns will not play, and the Ms can be manually assigned later.

I'm sure I'll need to write a code for this, but if you could point me in the right direction, I would really appreciate it!

Thank you!!

 

[Vintage79]

Someone here must know how to do this!

 

[Vintage79]

This is the first time I've posted a problem that nobody can solve. Any advice is really appreciated.

 

[Vintage79]

Help!!!