VBA Distinct Random Number Generator
If you want to generate random number in Excel & all the numbers should be unique (no repeat), you can use Excel Rand function to generate random numbers.
In this article, we will learn how to quickly create list of unique random numbers
We need to follow the below steps:
Function DistinctRandomNumbers(NumCount As Long, LLimit As Long, ULimit As Long) As Variant Dim RandColl As Collection, i As Long, varTemp() As Long DistinctRandomNumbers = False If NumCount< 1 Then Exit Function If LLimit>ULimit Then Exit Function If NumCount> (ULimit - LLimit + 1) Then Exit Function Set RandColl = New Collection Randomize Do On Error Resume Next i = CLng(Rnd * (ULimit - LLimit) + LLimit) RandColl.Addi, CStr(i) On Error GoTo 0 Loop Until RandColl.Count = NumCount ReDimvarTemp(1 To NumCount) For i = 1 ToNumCount varTemp(i) = RandColl(i) Next i Set RandColl = Nothing DistinctRandomNumbers = varTemp Erase varTemp End Function Sub Test() Dim varrRandomNumberList As Variant varrRandomNumberList = DistinctRandomNumbers(50, 1, 100) Range(Cells(3, 1), Cells(50 + 2, 1)).Value = _ Application.Transpose(varrRandomNumberList) End Sub
In this way, we can make a list of distinct random numbers without repetition in Excel 2013.
Another way in Excel 2016
RANDBETWEEN function stands for Random number between the 2 numbers. To get the same, follow the below steps.
RANDBETWEEN function returns the random numbers between any two numbers.
|=RANDBETWEEN ( bottom, top )|
Bottom : The smaller of the 2 numbers.
Top : The larger of the 2 numbers.
As we know a phone number have the following condition:
So the smallest possible number comes out to be 1000000000 and the largest possible number comes out to be 9999999999
So use the formula shown below to generate phone numbers
Use the Formula:
|= RANDBETWEEN ( 1000000000 , 9999999999 )|
1000000000 : smaller one
9999999999 : larger one
Just enter the formula once and repeat results using the drag down option in excel as shown in the above snapshot.
As you can see, you can get the random phone numbers in Excel.
Another ways to pick random values from given list
For this, we can opt for any of the two methods mentioned below. One method is using CHOOSE function. In this We need to input all individual values from the list to the function. You can use this formula for when number values in the list are less.
Formula using CHOOSE function:
|= CHOOSE ( RANDBETWEEN ( 1 , n) , "value1" , "value2" , "value3",,,"value n" )|
n : number of values in list
value1 , value2 , value3 , till value n : individual values from the list. These values can be input either using the cell reference or using directly with quotes or using the cell reference.
Another method is using the INDEX function where we will feed a random number as index which will result in the corresponding value at the index.
Formula using INDEX function:
|=INDEX(data, RANDBETWEEN (1,ROWS (data)),col_index)|
data : data array can be a list or table.
1 : start index
col_index : column index to point out the list in table.
Hope this article about Random Numbers Generator without Repeat Numbers using VBA in Microsoft Excel is explanatory. Find more articles on generating random numbers or date values here. If you liked our blogs, share it with your fristarts on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at email@example.com
Related Articles :
Excel VBA Variable Scope : In all the programming languages, we have variable access specifiers that define from where a defined variable can be accessed. Excel VBA is no Exception. VBA too has scope specifiers.
All About Excel Named Ranges : excel ranges that are tagged with names are easy to use in excel formulas. Learn all about it here.
Relative and Absolute Reference in Excel : Understanding of Relative and Absolute Reference in Excel is very important to work effectively on Excel. Relative and Absolute referencing of cells and ranges.
Generate Random Phone Numbers : Generate random 10 digti numbers using the RANDBETWEEN formula in Excel
Get Random number From Fixed Options : Generate random numbers form the list having criteria in Excel.
Get Random numbers between two numbers : RANDBETWEEN function generator number between the two given numbers in Excel.
Excel Random Selection: How to Get Random Sample From a Dataset : Use the random samples in Excel for the explained examples here.
How to use the RANDBETWEEN Function in Excel : Excel RANDBETWEEN function returns a number between two given numbers in Excel.
Popular Articles :
50 Excel Shortcuts to Increase Your Productivity : Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
How to use the IF Function in Excel : The IF statement in Excel checks the condition and returns a specific value if the condition is TRUE or returns another specific value if FALSE.
How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
How to Use SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.
How to use the COUNTIF Function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.