I'm looking to generate random groups based on certain preferences.
I've got a table that lists the names down the left hand column and several roles going across the right.
A | B | C | D | Priority | Start | |
Name 1 | 1 | 3 | 2 | |||
Name 2 | N | 1 | Y | |||
Name 3 | N | 1 | ||||
Name 4 | 3 | N | 2 | 1 | ||
Name 5 | 1 | 2 | Y |
*There could be up to 30 names and 11 roles, but I've only used five names and four roles for illustration purposes.
The number denotes the preference for certain roles (1 = primary, 2 = secondary, 3 = tertiary, N = not in this role).
The priority column is for if two people have the same preference e.g. Name 2 and 5 prefer role B, but Name 5 has priority.
The start column is for if there are more people than the number of roles available. A 'Y' in this column indicates that they must start.
Underneath, there are grids to list different group sizes and how the members of the group rotate between being involved and not e.g. 10 names and 7 roles or 11 names and 9 roles etc
In the below example, there are five names and four roles. Where there's a Y, it should be populated with a role. Where it's blank, they don't have a role in the rotation.
Rotation 1 | Rotation 2 | Rotation 3 | Rotation 4 | Rotation 5 | |
Name 1 | Y | Y | Y | Y | |
Name 2 | Y | Y | Y | Y | |
Name 3 | Y | Y | Y | Y | |
Name 4 | Y | Y | Y | Y | |
Name 5 | Y | Y | Y | Y |
I'd like to populate the available names in one column and then for excel to put the names in any order that can make a viable solution. In the above example, the order of the names do not make a difference. However, if two or more people have to miss a rotation, certain combinations can become problematic e.g. it might be helpful to have Name 2 on when Name 5 is off and vice versa due to both preferring the same role to help share the time between them. For example, I'd like to list the names in column A next to the grid, and Excel can reorder the names in column B to help make a viable solution.
A person should be put in a preferred position but can be put in any role unless it has a 'N' in the first grid.
They should also stay in one role as long as possible and should not be in more than three roles throughout. It's easier for continuity if Name 1 can be in role A for rotation 3-5 and either role D or B in rotation 1.
Many thanks!
