Adding a Unique Random Number within a Range

One key aspect of testing any data set is the ability to quickly and easily  generate random numbers in Excel.

As an example you may wish to generate a dummy data set made up of completely different numbers to test formula on.  That way when you put the real data in you will know that the formula works on the random data set.  

How to Generate Random Numbers in Excel


The key worksheet function to perform the random generator is a formula call RANDBETWEEN. Excel RANDBETWEEN function generates a set of integer random numbers between the two specified numbers.

RANDBETWEEN function takes two arguments – the start value (lower number) and the end value (higher number). The result will give you a whole number which falls between the low and the high numbers.

As an example let’s say we want to generate numbers between 1 and 1000. We follow the following steps to generate random numbers using RANDBETWEEN:


Put your cursor in the cell in which you want to get the random numbers.

In the below example in cell C2, enter =RANDBETWEEN(1,1000).

Copy the formula down to the bottom of the range.

Random number generator excel

This is a working example of creating a set of Random numbers for the month of April for 10 trusts. It works well and is easy to repeat to create data sets from scratch.


USING VBA to Generate Random Unique Numbers


With the assistance of VBA you can add a unique random number between two data points.  For example, you could generate random numbers between 1 and 10 and list the sequence in an Excel range.  So every number only appeared once and will not repeat itself.  

Option Explicit

Sub ProduceUniqRandom() 'Excel VBA to generate random number
Dim myStart As Long
Dim myEnd As Long
Dim i As Long
Dim a() 'Excel Dynamic Variable
Dim sh As Worksheet

Set sh=Sheet1
myStart=sh.[A2]
myEnd=sh.[b2]
ReDim a(0 To myEnd - myStart)

With Createobject("System.Collections.SortedList")
Randomize
For i=myStart To myEnd
.Item(Rnd)=i
Next i
For i=0 To .Count - 1
a(i)=.GetByIndex(i)
Next
End With
sh.Range("A5").Resize(UBound(a) + 1).Value=Application.Transpose(a)
End Sub

The following is a random number generator in Excel. It produces unique random numbers.