# random numbers without duplicates

1. ## random numbers without duplicates

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.

2. ## Re: 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.

3. ## Re: random numbers without duplicates

There is a VBA UDF example here:

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

4. ## Re: random numbers without duplicates

Thank you Tony! I tried what you stated, but alas got duplicates.

5. ## Re: random numbers without duplicates

i got the first sentence then instantly got lost..
i will accept being talked to like a moron because my brain cells have been fried trying to do this simply

6. ## Re: random numbers without duplicates

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1