Shuffle/Randomize from list of names

[geoff1544]

Hello,

Im trying to have a cell populate if conditions are met in 2 others cells (ive been using an If statement). I would like the cell that will populate to pull from a list of names and the results to evenly randomize or just choose from the next name on the list (and start from the beginning once it gets to the bottom of the list). Example


Cell A1 Header is "EcoSystems"
Cell B1 is "Species"
Cell C1 is "Subspecies"

If A2 and B2 = a condition, I want C2 to pull from a group of names that are in column D. I cant ranomdize it appears because it will duplicate a response from time to time. So the only way is to create a forumula that wont duplicate or just chooses the next name on the list.

Any help would be greatly appreciated!

 

[Boswell]

You might be able to accomplish the 'easily randomize' option by using a combination of RAND, RANK and INDEX. If names are in column D then define two helper columns: for example, column E containing a random number (=RAND()) corresponding to each name, and column F containing a RANK formula that ranks each random number in column E with respect to all random numbers in column E. This will produce a randomized ordering of the index of each name within the list of names. Then in column C, instead of referring directly to the list of names - use an INDEX function and the random ordering of indices in column F to refer to a random name in the list.