How to generate fixed length of data (randomly)

One way would be to fill B2:B30 with =RAND() functions then sort columns A:B using column B as the key.

Originally Posted by JohnTopley

=INDEX($A$2:$A$30,RANDBETWEEN(1,29))

In case you don't know how many cells is in the range:

=INDEX(A$2:A$30,RANDBETWEEN(1,ROWS(A$2:A$30)))

Note that if you're copying that formula down a column it's possible (probable) that you could get duplicates.

As usual little modified Tony's formula:

Formula:

Please Login or Register  to view this content.

edit:
I think modification above was wrong

Correction

Formula:

Please Login or Register  to view this content.

or

Formula:

Please Login or Register  to view this content.

assume col A:A has no more numbers than declared
It works but every refresh formula checking full A:A column so this is theory only but not for "fast" use .

Originally Posted by sandy666

As usual little modified Tony's formula:

Formula:

Please Login or Register  to view this content.

You may find #REF values cropping up since the last match can return the value 30, there being 30 values in column A including the label in A1. Change it to

Formula:

Please Login or Register  to view this content.

Did you also pick up on Tony's comment that you will almost certainly get duplicates. Whether that's what you want only you can say.

You're welcome. We appreciate the feedback!