random numbers without duplicates

Originally Posted by squirellyd

Hi.
I am using excel 2007 and have read how tos over and over on getting a list of random numbers but i guess i am doing something wrong because i keep getting duplicated numbers.
Is there a simple way to get a list of numbers 1 to 22986 to randomize without repeating ?

ANY help would be so appreciated.

I can think of a way to do it, but it will take two columns...
in A1 enter the normal RAND function =RAND()
in B1 enter the following: =RANK(A1,$A$1:$A$22986)+COUNTIF($A1:$A$22986,A1)-1

Drag both formulas down to row 22986 and you will have random values with no repeats in column B.

EDIT: Explanation of COUNTIF...
The COUNTIF resolves the fact that RAND can repeat values and RANK equally ranks matching numbers. This will give duplicates +1 for each duplicate below it in effect filling in the blanks that RANK leaves naturally.

2nd EDIT: Reversed the order of the variables in the Countif... it should be range first, value second.

There is a VBA UDF example here:

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

If you use a UDF, the formula MUST be confirmed with Ctrl+Shift+Enter rather than just Enter.

The dead simple way is to list the numbers down one column, put =RAND() down an adjacent column, and sort by the random numbers.