Unique random numbers from list

[Matt]

I would like to create a function that would put 5 random numbers from
a list. The list is in cells A5:A105 and the five random selections
can go in B5:B9. I'm using the formula:

=INDEX(A:A,RAND()*COUNTA(A:A))

This formula does work most of the time but does not guarantee that
the five random selections are unique. Any ideas on how this can be
done?

 

[RagDyer]

You're saying that you have 101 numbers in your list.

In an out of the way location on you sheet, say Z1 to Z101, enter the Rand
function.
In Z1 enter,
=Rand()
And copy down to Z101.

Then in B5, enter this formula:

=INDEX(A$5:A$105,RANK(Z1,Z$1:Z$101))

And copy down to B9.

This will give you a random selection from your list, "without replacement",
meaning ... no duplicates.

You will get a new random selection with every hit of <F9>.

You could make your calc mode "manual", so that you don't inadvertently
refresh you random selection.

 

[Tyro]

Are the numbers in A5:A105 unique -i.e. no duplicates?

Tyro

 

[Matt]

That is correct. There are no duplicates in the original list.