How do I create a spreadsheet that randomizes a list of names within groups?

[mgpnextgen]

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.

[#2]

@mgpnextgen 

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!

www.sumproduct.com and www.sumproduct.com/thought

[#3]

@Liam Bastick 

Thanks very much! I appreciate the quick reply. I'll give this shot and see how it works out.

-Mike

[#4]

@mgpnextgen 

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

[#5]

@Kodipady 

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...