Excel formula for Random Teams and Assignments 1

[jkupov]

How would I go about having excel randomly divide a list of tasks (abt 20) into randomly selected groups (as even as possible)comprised of a long list of randomly selected people (abt 400?

So I want the worksheet to create random groups from my roster and assign the tasks randomly to the groups.

Is this possible?

 

[Gruuuu]

If the number of people in a group doesn't have to be random, List your people, put the formula =RAND() next to each cell with names. Sort by the random column. (You can resort a few times, if you like. it's not necessary).

Then, the first X People are in your first group. Make the group numbers up.

 

[jkupov]

Thank you,the number of people in a group doesn't have to be random- but as close to even as possible. We have a number of volunteer cleanup activities that we're participating in and would like to mix up the employees so they can get to know different people, but also assign tasks randomly. Daunting with so many employees.

So once the teams are generated, how would I randomly assign the tasks? Is there a way to do all of this in once fell swoop rather than first creating the teams and then having the tasks randomly assigned to them?

 

[jkupov]

Found this online from microsoft:

http://office(dot)microsoft(dot)com/en-us/templates/random-teams-generator-TC030007705(dot)aspx

Something like this would be perfect if it had expanded capacities (more employees) and added a third variable, which is assignments or tasks.

I'm not asking anyone to put hours into this like the template in this link shows, but maybe something simpler that can replicate the results? UNless it's easy of course.

 

[jkupov]

Would this bets be done in VB rather than using excel formulas?

 

[Gruuuu]

Is there 1 task per person? And what tasks can be assigned randomly?

 

[jkupov]

No, probably about 3 or 4 tasks per group. All should be randomly assigned.

 

[Gruuuu]

So this is actually still quite simple. You only need to randomize the people's names and the task list.

Start by defining the numbers of slots on teams. You want them roughly equal, team sizes of 3 or 4. Easy enough, Team 1 has 3 slots, Team 2 has 4, so on & so forth.

Randomize the task list as I suggested above, Randomize the names as I suggested above, Once these are both randomized and sorted, you can delete the RAND() column and move the names and the tasks over next to the group breakdown.

Was there anything we missed there?

 

[jkupov]

Oh... That was simple. No, that works great. Thanks

 

[Gruuuu]

No problem!