200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Greetings,
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.
Ed
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.
Thanks!
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.)
