Suppose I have a list of strings:
apple, pear, banana, orange
How can I have each cell in a column of cells have a random value from that list?
Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this communitySuppose I have a list of strings:
apple, pear, banana, orange
How can I have each cell in a column of cells have a random value from that list?
Please try:
=index(A:A,randbetween(1,4))
where your list of fruits is assumed to be in ColumnA (and consists of four items).
Or without a separate list:
=choose(randbetween(1,4),"apple","pear","banana","orange")
Another way, which I prefer, is:
=INDIRECT("A"&RANDBETWEEN(3,7))
Where "A" is the column, and 3,7 is the start and end rows for your list. This way you don't have to figure out how many elements are in your list, you just look at the start and end row numbers, and you don't have to use the whole column for your list. You can have headers and such.
You also can modify this to make it more maintainable, by substituting the last number with the COUNTA function and adding the offset:
=INDIRECT("A"&RANDBETWEEN(4,COUNTA(A4:A99)+3))
The 4 is the first row of the list. The +3 should be 1 less than that number to skip rows not in the range of the list. Give COUNTA a large enough range that you won't have to worry about it for a while, then you can just add items to your list and they'll already be included in your random selection without needing to do anything else. Just ensure the range only includes your list and empty cells.
The other answers are fine but an easier solution is our add-on: https://gsuite.google.com/marketplace/app/random_data_generator/626703404158
You can choose from your own data, strings, numbers, user agents, and many other random datatypes.