Assign 25-35 people randomly to groups of 5, 5 different times, no repeats



New to Teach Excel and Love it!  Thanks for all the great tips, tools, and more.

I deliver leadership programs that include deliberate practice sessions.  Typically, there are 25-35 participants, there are 4-6 practice sessions, and I would like to randomly assign participants to the practice groups and ideally have no overlaps/no participants in groups with the same people.  Tall order I suspect.

Suggestions will be greatly appreciated.

Thanks so much.




Selected Answer

Hello, there!

The attatched file here was created just for my personal use. See if it can meet your purpose.

Please don't forget to protect the worksheet (it is unprotected right now) before using it in order to avoid accidental unwanted changes that may disturb the system.

Here, we've to fill up only the shaded cells with:
> number of necessary groups and
> name of participants.
Then copy+paste (as text only) the result in another sheet.

I am a learner here craving for everyone's suggestions and guidance.




Chhabi, thank you SO much!  This perfect and is an incedible help.  This will save me (and others on our team) many hours.  I appreciate you and all your help.  Ed
EdN (rep: 4) Dec 10, '17 at 7:58 am
Thank you very much for your encouraging feedback.
Chhabi Acharya (rep: 109) Dec 10, '17 at 8:38 am
Back from a 3-week holiday and getting ready for a program next week requiring random groups.  Thank you again for the file.  I think I am a BDU (brain dead user) - grinning.  I worked for hours with the group spreadsheet you sent and failed to get it to do what I know you designed it to do. I will keep working, and thanks again.  Ed
EdN (rep: 4) Jan 11, '18 at 5:17 am
Hello EdN,

Sorry to find some problems in the file when checked in PC. Thank you very much for asking for a review.

I hope this 5th file may help you.
Chhabi Acharya (rep: 109) Jan 20, '18 at 5:51 am
Add to Discussion

Glad you enjoy it here :)

Here is a solution that should work (probably not the best but currently limited by my phone right now).

Use this UDF function to generate the random numbers: non-repeating list of random numbers in excel

In the function, first argument put1 and second argument put the number of people you have and then put that number again for the 3rd argument.

That functiom gives you numbers in 1 cell. Copy/paste valeues to keep only the numbers (select the cell and hit ctrl+c alt+e+s+v Enter).

Then use text-to-columns feature to split the numbers into their own cells. Transpose those numbers so they move from being in a row to being in a column.

Put a number for each participant, start at 1 and integrate up until the end.

Now, the first 5 numner that were randomely generated can make the first team and the n3xt 5 the next team where each number correspomds to a participant.

It seems like a lot of work but its really not. Using the udf mght be the trickiest part and i think we have a tutorial on here that tells you how to install amd use them - they are just macros that create new functions in the worksheet.

(Theres probably a crazy worksheet formula that will do this but like i said, im on my phone now and these steps are pretty straight forward anyway. If you dont understand anything, reply here and ill get back to you.)



Thanks so much.  When back in the office I will test it.  Really appreciate the help.  
EdN (rep: 4) Dec 8, '17 at 12:57 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login