How to generate random string from a set of available strings in Excel?

I want to generate a column which will have random string values in any order. Those string values have to be from ('New', 'Mint', 'Very Good', 'Good', 'Acceptable', 'Poor') strings? Can anyone let me know about how can I make use of RANDBETWEEN function to populate my columns with the mentioned data in brackets?

Solution 1:

If you only have a few strings

If your strings are in the first column you can use the CHOOSE() function like this:

=CHOOSE(RANDBETWEEN(1,6),$A$1,$A$2,$A$3,$A$4,$A$5,$A$6)

RANDBETWEEN(1,6) will randomly pick a number n from 1 to 6 - CHOOSE then counts through all the parameters you've listed (your strings in column A) and displays the nth term.

But, if you have many strings

If you have more than a few items to CHOOSE from, maintaining this formula becomes unwieldy.

In which case it's probably a good idea to insert a Table (let's call it MyStrings), create an ID column in column A using =ROW()-1 and switch our formula to a VLOOKUP. We can use MIN & MAX and structured references to dynamically work out how many terms there are in your table, for use in the RANDBETWEEN:

=VLOOKUP(RANDBETWEEN(MIN(MyStrings[ID]),MAX(MyStrings[ID])),MyStrings,2,0)

Careful Now

RANDBETWEEN is a volatile function, which means that it recalculates every time any other cell changes (anywhere in your workbook). If you need to keep the randomly-generated strings fixed as they are once you've produced them, you'll need to copy and Paste Special as Values.

If you use a lot of volatile functions, it may slow your worbook down, but there are things you can to do alleviate this.

Solution 2:

You can use INDEX together with RANDBETWEEN to do this.

=INDEX({"New","Mint","Very Good","Good","Acceptable","Poor"},RANDBETWEEN(1,6))

Just fill this formula down your column.