1. Registered User
Join Date
Mar 2004
Posts
6

I am trying to get excel to generate random numbers. When I write the formula =randombetween(1,28000) it returns one number between 1 and 28,000 but I need 400 unique numbers between 1 and 28,000. What we are trying to do is using all available parcels in our community and there are 28,000 of them we need to pick 400 of them to analyze. We thought the easiest way was to copy and paste all 28,000 parcels into excel and assign them a number between 1 and 28000 and then pick 400 to analyze. Any suggestions to have excel return 400 unique random numbers between 1 and 28000 thanks for the help!

2. Registered User
Join Date
Oct 2003
Posts
1,091
Select the 28000 cells that you will be using, type in your formula, then hit CTRL + ENTER, and it will populate all 28000 cells.

But keep in mind that everytime you do something in Excel, it will regenerate the numbers. Therefore, immediately after you do this, copy, paste special, values.
Last edited by shades; 03-10-04 at 14:24.

3. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
This isn't really easy to do in Excel due to the unique constraint. It can be done though. The basic process boils down to:

1) Begin loop with 400 iterations to go
2) Pick a row
3) If it is already "tagged", move up or down through the list to the first untagged row
4) tag the current row
5) decrement the loop counter, and repeat if not zero

Is it possible to use Access instead of using Excel, because that would make it a lot easier?

-PatP

4. Registered User
Join Date
Mar 2004
Posts
6
I found an easy way to do this, so I solved my own question! First you go to tools and choose add-ins then you choose the Analysis tool pack and click ok. Then go back to tools and choose Data Analysis and choose random number generator. Then simply enter the paramaters if you don't know what they are just click on help and it gives ample description then once you have entered the things you need it does just that, Very quickly it generated 400 different numbers between 1 and 28000. easy as pie!!!

#### Posting Permissions

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