Jul 19 2019 04:53 PM
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.
Jul 20 2019 05:44 PM
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!
Jul 20 2019 06:32 PM
Thanks very much! I appreciate the quick reply. I'll give this shot and see how it works out.
-Mike
Jul 20 2019 11:46 PM
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 |
Jul 21 2019 06:35 AM
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...