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.

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.
--
Gary''s Student
gsnu200703


"Rowland" wrote:

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.

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

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.
-- Gary''s Student gsnu200703 "Rowland" wrote:
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.

Rowland wrote:
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