Featured Product

Excel Daily News

Search MrExcel

Random Numbers

I'm trying to get Excel to return a list of random numbers, but using the RAND and RANDBETWEEN functions don't help because a unique # is not returned every time (#'s repeat). Does anyone know how to do this?

Posted by Mark W. on April 18, 2001 8:26 AM

How many random numbers are you trying to produce?What should the range of these numbers be?

Posted by Alisa on April 18, 2001 8:31 AM

I need 2000 unique #'s between 1 and 2000.

Posted by Mark W. on April 18, 2001 8:36 AM

Please claify. Do you mean to say that you'd like to randomly order the values 1 thru 2000?

Posted by Alisa on April 18, 2001 8:48 AM

Yes, that's what I want to do.

Posted by Mark W. on April 18, 2001 8:54 AM

For discussion purposes...suppose that A1:A10 contains {1;2;3;4;5;6;7;8;9;10}. You can enter =RAND() into B1 and copy down to B10. Next, =INDEX($A$1:$A$10,RANK(B1,$B$1:$B$10)) into C1, and copy down to C10. This will produce a new, randomized list in column C.

Posted by Alisa on April 18, 2001 8:56 AM

Yes, that's what I want to do.

Posted by big bob on April 18, 2001 9:04 AM

In column A put the numbers from 1 to 2000In column B put the formula =RAND() for eachcell with a number in column A.Select a cellin column B and click one of the sort buttonson the toolbar .Column A will now contain arandomly sorted list and each click of a sortbutton will generate another different one.HTH Bob

Posted by Alisa on April 18, 2001 9:06 AM

Thanks,it worked!

Posted by Alisa on April 18, 2001 9:47 AM

I am able to generate the list of #'s, but every time I add text to any other cells, save changes, etc., the #'s change. Is there any way I can prevent this?

Posted by Mark W. on April 18, 2001 10:05 AM

Alisa, there are couple of ways that you can preserve the random numbers.

1. Use Copy/Paste Special Values to overwrite the values produced by the RAND() function. If, you need to produce a new set of random numbers later you'd just re-enter the RAND() function.

2. Use the Random Number Generation facility of the Tools Data Analysis... command. If this command isn't listed in your Tools menu you'll need to add the Analysis ToolPak using the Tools Add-ins... menu command.

Posted by Mark W. on April 18, 2001 10:18 AM

Oh, yeah...there's a 3rd option...

Put your random list is a separate Workbook. Create a defined name (i.e., randList) that refers to the list range ($C$1:$C$10, in my example). Reference this new defined name from any of your other workbooks.

Posted by Mark W. on April 18, 2001 10:20 AM

Re: Oh, yeah...there's a 3rd option...

Don't forget to close the workbook containing the new defined name randList.

(12 responses) HI! I am using a vlookup/match to populate fields from column headers. For the most part it's doing exactly what I need except instead of pu

Let's Connect

MrExcel.com debuted on November 21, 1998.

MrExcel.com provides examples of Formulas, Functions and Visual Basic procedures
for illustration only, without warranty either expressed or implied, including
but not limited to the implied warranties of merchantability and/or fitness for
a particular purpose. The Formulas, Functions and Visual Basic procedures on this
web site are provided "as is" and we do not guarantee that they can be used in all
situations.
This site contains affiliate links. Any affiliate commissions that we
earn when you click a link to Amazon or other sites is reinvested in keeping MrExcel.com
running. You can earn a commission for sales leads that you send to us by joining our
affiliate program.
View our Privacy Policy, Cookies Policy, and Terms of Use.