Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-765586%22%20slang%3D%22en-US%22%3EHow%20do%20I%20create%20a%20spreadsheet%20that%20randomizes%20a%20list%20of%20names%20within%20groups%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-765586%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20do%20I%20create%20a%20spreadsheet%20that%20takes%20a%20list%20of%20names%2C%20contained%20within%20groups%20and%20generates%20a%20random%20order%20of%20those%20names%20(still%20contained%20within%20their%20groups).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EQA%20Team%3C%2FP%3E%3CP%3EName%201%3C%2FP%3E%3CP%3EName%202%3C%2FP%3E%3CP%3EName%203%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUX%20Team%3C%2FP%3E%3CP%3EName%201%3C%2FP%3E%3CP%3EName%202%3C%2FP%3E%3CP%3EName%203%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%2C%20perhaps%20using%20a%20user%20created%20built%20in%20function%2C%20change%20the%20order%20of%20those%20names.%20For%20example%2C%20using%20the%20list%20above%20after%20executing%20the%20function%2Fformula%2C%20generates%20the%20following%20new%20list%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUX%20Team%3C%2FP%3E%3CP%3EName%202%3C%2FP%3E%3CP%3EName%203%3C%2FP%3E%3CP%3EName%201%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EQA%20Team%3C%2FP%3E%3CP%3EName%203%3C%2FP%3E%3CP%3EName%201%3C%2FP%3E%3CP%3EName%202%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20want%20to%20be%20able%20to%20add%20or%20remove%20names%20from%20the%20sheet%2C%20preferably%20via%20a%20form%3F%20There%20is%20a%20Randomizer%20App%20in%20the%20Microsoft%20Store%20but%20this%20only%20generates%20different%20list%20of%20individual%20names.%20I%20need%20to%20be%20able%20to%20maintain%20the%20groups.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-765586%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-766049%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20create%20a%20spreadsheet%20that%20randomizes%20a%20list%20of%20names%20within%20groups%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-766049%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F379307%22%20target%3D%22_blank%22%3E%40mgpnextgen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20avoid%20VBA%20and%20user-defined%20functions%20where%20you%20can%2C%20although%20admittedly%20my%20solution%20uses%20the%20volatile%20RAND()%20function%20too%20with%20a%20Table.%26nbsp%3B%20If%20you%20didn't%20want%20the%20order%20to%20change%20all%20of%20the%20time%2C%20simply%20use%20a%20copy%20and%20paste%20special%20as%20values%20macro%20to%20put%20the%20numbers%20into%20my%20Table%20-%20hopefully%2C%20you%20will%20get%20the%20general%20idea.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EKeep%20it%20simple!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fwww.sumproduct.com%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.sumproduct.com%3C%2FA%3Eand%20%3CA%20href%3D%22http%3A%2F%2Fwww.sumproduct.com%2Fthought%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.sumproduct.com%2Fthought%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-766057%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20create%20a%20spreadsheet%20that%20randomizes%20a%20list%20of%20names%20within%20groups%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-766057%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F20141%22%20target%3D%22_blank%22%3E%40Liam%20Bastick%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20very%20much!%20I%20appreciate%20the%20quick%20reply.%20I'll%20give%20this%20shot%20and%20see%20how%20it%20works%20out.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-Mike%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-766157%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20create%20a%20spreadsheet%20that%20randomizes%20a%20list%20of%20names%20within%20groups%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-766157%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F379307%22%20target%3D%22_blank%22%3E%40mgpnextgen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20create%20a%20sortkey%20column%20in%20your%20table%2C%20please%20update%20this%20with%20excel%20formula%20RAND().%26nbsp%3B%20Now%20sort%20the%20table%20by%20team%20name%20and%20sortkey.%26nbsp%3B%20%26nbsp%3BRAND()%20output%20keeps%20changing%20often%20with%20every%20update.%26nbsp%3B%20you%20sort%20order%20will%20also%20change%20accordingly.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETeam%20name%26nbsp%3BName%26nbsp%3BSortkey%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EQA%20Team%3C%2FTD%3E%3CTD%3EName%204%3C%2FTD%3E%3CTD%3E0.224421%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EQA%20Team%3C%2FTD%3E%3CTD%3EName%206%3C%2FTD%3E%3CTD%3E0.107234%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EQA%20Team%3C%2FTD%3E%3CTD%3EName%205%3C%2FTD%3E%3CTD%3E0.031831%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EUX%20Team%3C%2FTD%3E%3CTD%3EName%203%3C%2FTD%3E%3CTD%3E0.775804%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EUX%20Team%3C%2FTD%3E%3CTD%3EName%201%3C%2FTD%3E%3CTD%3E0.082853%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EUX%20Team%3C%2FTD%3E%3CTD%3EName%202%3C%2FTD%3E%3CTD%3E0.974162%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-766376%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20create%20a%20spreadsheet%20that%20randomizes%20a%20list%20of%20names%20within%20groups%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-766376%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368896%22%20target%3D%22_blank%22%3E%40Kodipady%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%20I%20will%20try%20this%20today.%20Please%20note%20that%20my%20Excel%20experience%20is%20weak%20at%20best.%20I%20can%20create%20standards%20spreadsheets%20and%20usually%20find%20that%20I'm%20best%20at%20taking%20a%20template%20and%20modifying%20it.%20Once%20I%20start%20getting%20into%20editing%20formulas%2C%20I%20am%20in%20over%20my%20head...%3C%2FP%3E%3C%2FLINGO-BODY%3E
mgpnextgen
New Contributor

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

@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

@Liam Bastick 

 

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

 

-Mike

@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 TeamName 40.224421
QA TeamName 60.107234
QA TeamName 50.031831
UX TeamName 30.775804
UX TeamName 10.082853
UX TeamName 20.974162

 

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

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies