Non repeating random numbers

[Rowland]

I have a much larger range but for the this example I only use 10 cells.
I need to pull random numbers from a range without repeating any of them
INDEX(A$1:A$10,RAND()*10+1
This works but it obviously is prone to picking repeats.How can I do
this with a formula without repeating..I can't use a VBA solution in
this spreadsheet.I can put a big clunky If in there to check each one
but theres got to be a cleaner way.

 

[Guest]

Here the trick. Say you have numbers in A1 thru A100 and you want to sample
ten of them with any repeats. In B1 thru B100 enter the formula:

=RAND()

then sort cols A & B by B.

This will scramble the material in column A.

Just pick the first 10 items.

 

[Rowland]

Do to the end users of this SS I can't use that approach,it has to be
automated.

 

[Rowland]

I have a much larger range but for the this example I only use 10 cells.
I need to pull random numbers from a range without repeating any of them
INDEX(A$1:A$10,RAND()*10+1
This works but it obviously is prone to picking repeats.How can I do
this with a formula without repeating..I can't use a VBA solution in
this spreadsheet.I can put a big clunky If in there to check each one
but theres got to be a cleaner way.

OK I think I have solved my problem with only adding one column
I add a column and enter Rand() down B1:B10(and then hide it)
In the C column where I need 10 non repeating random numbers from A1:A10
I use this formula
=INDIRECT("A"&RANK(B1,B$1:B$10))
It gets the Rank of the rand()number in B column(1 to 10) and uses that
with the indirect function to return a value from the A Column