Vintage79
Board Regular
- Joined
- May 29, 2007
- Messages
- 187
- Office Version
- 2019
- Platform
- Windows
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:
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.
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!!
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!!
Last edited: