Forum Discussion
How do I create a spreadsheet that randomizes a list of names within groups?
How do I create a spreadsheet that takes a list of names, contained within groups and generates a random order of those names (still contained within their groups).
For example:
QA Team
Name 1
Name 2
Name 3
UX Team
Name 1
Name 2
Name 3
Then, perhaps using a user created built in function, change the order of those names. For example, using the list above after executing the function/formula, generates the following new list:
UX Team
Name 2
Name 3
Name 1
QA Team
Name 3
Name 1
Name 2
I also want to be able to add or remove names from the sheet, preferably via a form? There is a Randomizer App in the Microsoft Store but this only generates different list of individual names. I need to be able to maintain the groups.
4 Replies
You can create a sortkey column in your table, please update this with excel formula RAND(). Now sort the table by team name and sortkey. RAND() output keeps changing often with every update. you sort order will also change accordingly.
Team name Name Sortkey
QA Team Name 4 0.224421 QA Team Name 6 0.107234 QA Team Name 5 0.031831 UX Team Name 3 0.775804 UX Team Name 1 0.082853 UX Team Name 2 0.974162 Thank you. I will try this today. Please note that my Excel experience is weak at best. I can create standards spreadsheets and usually find that I'm best at taking a template and modifying it. Once I start getting into editing formulas, I am in over my head...
I'd avoid VBA and user-defined functions where you can, although admittedly my solution uses the volatile RAND() function too with a Table. If you didn't want the order to change all of the time, simply use a copy and paste special as values macro to put the numbers into my Table - hopefully, you will get the general idea.
Keep it simple!
Thanks very much! I appreciate the quick reply. I'll give this shot and see how it works out.
-Mike
