Generating 500,000 random unique numbers

W

wigi

Well-known Member

Joined

Jul 10, 2006

Messages

7,964

Office Version

1. 365

Platform

1. Windows
2. Mobile
3. Web

• May 28, 2010

• #2

chris meardon said:

the numbers need to be within 1-9999

Click to expand...

Do you have decimals as well? How else could you have half a million numbers in that range

 

Upvote 0

C

chris meardon

New Member

Joined

May 28, 2010

Messages

5

• May 28, 2010

• #3

lol Am i being really stupid in that there isnt 500,000 combos with that many digits? no idea how to work that out

 

Upvote 0

ravi4ever

Active Member

Joined

Apr 13, 2010

Messages

316

• May 28, 2010

• #4

no its not possible without decimals, according to probablity.. there are only 10000 possible combinations from 0000 to 9999..

Of the 10000 4-digit numbers 5040 are non-repeating, 4320 one-repeating (doubles), 270 two-repeating (dual-doubles), 360 one-tripled, and 10 quadruples

 

Upvote 0

C

chris meardon

New Member

Joined

May 28, 2010

Messages

5

• May 28, 2010

• #5

Ok so I would need to add another digit on, so 5 digit number? perhaps thats why i was getting some copys using the RAND fuction simply because there wasnt enough numbers to have them all unique??

 

Upvote 0

A

al_b_cnu

Well-known Member

Joined

Jul 18, 2003

Messages

4,545

• May 28, 2010

• #6

however many numbers are required, this could easily be done by, e.g. column A containing the formula =Rand()
Column B containing the unique numbers 1 to 9999 (or whatever) and each time you wish to generate a sequence, press [F9] then sort the two columns using column A as the key.

custom format column B as '0000'

 

Upvote 0

W

wigi

Well-known Member

Joined

Jul 10, 2006

Messages

7,964

Office Version

1. 365

Platform

1. Windows
2. Mobile
3. Web

• May 28, 2010

• #7

You need 6 digits...

But that isn't the reason why you have duplicates - the RAND function does not allow for unique numbers.

 

Upvote 0

C

chris meardon

New Member

Joined

May 28, 2010

Messages

5

• May 28, 2010

• #8

How will i ensure these are unique then?

Thanks

 

Upvote 0

W

wigi

Well-known Member

Joined

Jul 10, 2006

Messages

7,964

Office Version

1. 365

Platform

1. Windows
2. Mobile
3. Web

• May 28, 2010

• #9

Use the RANK function applied on the random numbers, that should lead to unique numbers.

 

Upvote 0

C

chris meardon

New Member

Joined

May 28, 2010

Messages

5

• May 28, 2010

• #10

Sorry Im struggling to see how the rank would be used to make sure they are unique? I dont have much experience with the rank funtion

 

Upvote 0