Saturday, August 13, 2011

yogi_Extract A Random Name From Within A Subset Of Data

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

trimonth said ...
How do I query a random number from within a subset of numbers?
ok how about this? There is a list of ids, groups and names. See the example
ids are numbers 1-9.
groups are red, white and blue.
names are the names.
I want to have a cel that displays a random name from the list. Any name on the list. I think I have got that. (Although if you see room for improvement, please say so.)
Now suppose I want a random name from within the list dependent on the groups. For example, I want a random name from the blue group. So it should be either joe, jill, ben, or bob. Does anyone have any ideas on how to do that?
--------------------------------------------------------
In the following solution I used primarily the FILTER function to extract a random name in Sheet2 from data in Sheet1