Generating 10 unique random numbers

[CommiC]

I need to generate 10 unique random numbers between 1 and 100
I can't do that with RAND()*100-1 as I keep getting double numbers.
How can I do this??

Thanks
John

 

[Gord Dibben]

John McGimpsey has a User Defined Function for this.

Returns non-repeating integers.

http://www.mcgimpsey.com/excel/udfs/randint.html

Gord Dibben Excel MVP

 

[Ragdyer]

So, what you're actually looking for is not a random number, but a random
*order* of pre-determined numbers.

One approach to accomplish this without constant sorting, is to use an
"out-of-the-way" column, say Column Z, and enter
=RAND()

Drag down to copy, to Z100.

Now, enter this formula where you wish to *start* displaying the
random order, say C1:

=INDEX(ROW($A$1:$A$100),RANK($Z$1:$Z$100,$Z$1:$Z$100))
But *just* use <ENTER>.

Now, click in C1, and drag down to copy to C10.

This will now give you a display of your 1 to 10 numbers, in random order.
To display a *new* order, all you have to do now, is simply hit <F9>.

 

[CommiC]

Great! this works fine and was just what I was looking for.

Thanks!
John