Sorting players into groups - need help

Vintage79

Board Regular
Joined
May 29, 2007
Messages
185
Office Version
  1. 2019
Platform
  1. 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:


Excel 2010
ABCDEFGHIJK
3NameIDTeamTimeAvail.Notes12345
4Name A186Team 4EYxx
5Name B528Team 4EYxx
6Name C331Team 4EYxxx
7Name D204Team 4LNx
8Name E701Team 4LYxx
9Name F611Team 3ENxx
12Name G227Team 3EMx
13Name H141Team 3EYxxx
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
BCD
5Groups
6Group 1EarlyLate
7Loc. A22
8Loc. B64
9Loc. C03
10Loc. D00
11Loc. E20
12
13Group 2
14Loc. F10
15Loc. G33
16Loc. H00
17
18Group 3
19Loc. I22
20
21Group 4
22Loc. J24
23Loc. K88
24Loc. L04
25Loc. M21
26Loc. N33
27Loc. O40
28
29Group 5
30Loc. P25
31Loc. Q10
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:

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
This is the first time I've posted a problem that nobody can solve. Any advice is really appreciated.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top