Hi all!
I have a list of 71 names that I need to dispatch in 12 teams.
In the attached file:
Table1 is the list of names (I've replaced them with numbers). Column "H/F" is "Man/Woman". Column "team" (name is poorly chosen) is the service they work in. We're trying to have as few persons from the same service in the same team. Column 'num' is a sequential number I used for convenience' sake.
I created a macro that does the following:
Take items one by one in the table starting from top to bottom. For each item, pick a random number between 1-12 (the future team in which the person will be assigned). If the team is full, pick another random number.
The teams are filled in the range I4:T9
In the range I12:T17, I use index/match to check if male or female
In I18:T18, I count the number of women
In G5, I check if the number of women is between 2 and 3 (I have ~30 females and ~40 males in total)
In the range I28:T43, I check how many people from the same service are in the same team.
In G6, I check if there's no more than 2 people from the same service in the same team.
The macro, when finished filling the teams, checks if the two conditions in G5 and G6 are met. If not, it starts over.
G7 is kind of a 'score' marker: I check how many teams have 2 people from the same service. Low score = better. But I don't use it in formulas or in the code.
Is there a more elegant way to do that?
I tried reducing the max number of people from the same service to 1, but haven't let the macro run long enough to find a solution that works. How could I do that with more reliability?
Thanks !
[Note: due to not paying enough attention when I added "application.calculation = xlcalculationmanual", you need two button presses to make the code work as intended]
I have a list of 71 names that I need to dispatch in 12 teams.
In the attached file:
Table1 is the list of names (I've replaced them with numbers). Column "H/F" is "Man/Woman". Column "team" (name is poorly chosen) is the service they work in. We're trying to have as few persons from the same service in the same team. Column 'num' is a sequential number I used for convenience' sake.
I created a macro that does the following:
Take items one by one in the table starting from top to bottom. For each item, pick a random number between 1-12 (the future team in which the person will be assigned). If the team is full, pick another random number.
The teams are filled in the range I4:T9
In the range I12:T17, I use index/match to check if male or female
In I18:T18, I count the number of women
In G5, I check if the number of women is between 2 and 3 (I have ~30 females and ~40 males in total)
In the range I28:T43, I check how many people from the same service are in the same team.
In G6, I check if there's no more than 2 people from the same service in the same team.
The macro, when finished filling the teams, checks if the two conditions in G5 and G6 are met. If not, it starts over.
G7 is kind of a 'score' marker: I check how many teams have 2 people from the same service. Low score = better. But I don't use it in formulas or in the code.
Is there a more elegant way to do that?
I tried reducing the max number of people from the same service to 1, but haven't let the macro run long enough to find a solution that works. How could I do that with more reliability?
Thanks !
[Note: due to not paying enough attention when I added "application.calculation = xlcalculationmanual", you need two button presses to make the code work as intended]
Last edited: