How to generate random numbers in Excel 2010

September 27, 2011 at 23:19:40
Specs: Windows 7

Hi, i would like to know is there any formula or macro code available to generate random numbers in Excel sheet which will act as an unique identification for a particular record or document or folder file that will enhance the search operation..
Thanks in advance..

See More: How to generate random numbers in Excel 2010

Report •


#1
September 28, 2011 at 07:53:56

You have two functions in Excel that generate "random" numbers, look at:

=RAND()
This function creates a random number >=0 but <1.
The number will change each time the worksheet recalculates,

=RANDBETWEEN()
This function produces a random whole number between two specified numbers.
and it will also change each time the worksheet recalculates.

That being said, you will also need some type of mechanism to check that the number has not already been used.
Even "random" numbers repeat on occasion.

MIKE

http://www.skeptic.com/


Report •

#2
September 28, 2011 at 09:32:26

A key point in Mike's response is that the RAND and RANDBETWEEN functions are known as "volatile functions" and will change every time the sheet recalculates. That might be an issue if you are trying to assign a number to a object and have it remain the same forever.

There are quite a few ways to assign static numbers but I don't quite understand how assigning random numbers "will enhance the search operation".

Please explain.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
September 29, 2011 at 22:35:24

Hi, Derby what you said is correct i don't want the numbers to change every time the sheet recalculates.. what i was thinking was to have a formula that will generate a unique number for every record that i will enter in the sheet. The thing about search operation is that i am exporting the entire data from this sheet to a sharepoint site where there is a column named ID which will store this unique number for that record so in the search option when the user gives ID it will help in proper identification. My current search functionality is based on name which creates ambiguity.. So is there any solution to this and if possible can we generate alphanumeric values?? Please help..

Report •

Related Solutions

#4
October 17, 2011 at 02:36:01

well, to generate random numbers in excel can be achieved by using VB Scripts. But actually I don't like to use the scripts because i am not a programmer. What i am looking for is a tool to helo me do this thing. I would like to recommend you Kutools for Excel to generate random numbers. It is an add-ins collection to help you dwal with some repetitive work in Excel. For inserting random numbers, you only need to click the function button, and input the range of number you want, and then the numbers are generated in a fast way.

Report •


Ask Question