If you play lottery games of lotto type or want to get a list of some unique random numbers, letters or expressions, Excel is very helpful in generating them. There are many ways to accomplish that. Here are just couple of them.
To create a list of unique random numbers using an array formula
First, format a sellected cell, e.g. A1, as Text. Enter a range of numbers from which to draw, in this format: 1-50 . Next, enter in cell A2 the following array formula:
=SMALL(IF(COUNTIF(C$1:C1,ROW($1:$50))<>1,ROW($1:$50)),1+INT(RAND()*(RIGHT(C$1,LEN(C$1)-FIND("-",C$1))-LEFT(C$1,FIND("-",C$1)-1)+1-ROW()+ROW(C$2))))
To enter it correctly, hold CTRL+SHIFT and press ENTER key on your keyboard. If you need to generate random unique numbers from a different range, you have to replace the numbers (1 and 50) in both A1 and A2 cells.
Now you need to copy cell A2 and paste it to as many rows as many random numbers you are expecting to obtain (up to 50 in the example).
To create a list of unique random numbers using UDF (user defined function)
- Press ALT+F11 on your keyboard to open up the VB Editor.
- If no Module is available within your Project, then right-click within the VBA Project Explorer and select Insert, then choose Module from the menu.
- Enter the following code in the module:
Function GetUnique(Lo As Integer, Hi As Integer, Nr As Integer) As String
'Generates x unique random numbers between any 2 numbers you specify
'e.g. =GetUnique(1,50,8) produces 8 unique random numbers between 1 and 50
Dim iArr As Variant
Dim i As Integer
Dim r As Integer
Dim temp As Integer
Application.Volatile
ReDim iArr(Lo To Hi)
For i = Lo To Hi
iArr(i) = i
Next i
For i = Hi To Lo + 1 Step -1
r = Int(Rnd() * (i - Lo + 1)) + Lo
temp = iArr(r)
iArr(r) = iArr(i)
iArr(i) = temp
Next i
For i = Lo To Lo + Nr - 1
If i = Lo Then GetUnique = GetUnique & "" & iArr(i)
If i > 1 Then GetUnique = GetUnique & " * " & iArr(i)
Next i
GetUnique = Trim(GetUnique)
End Function
- Use the function in any selected cell by entering it in the format shown above on the comment line.
To create a random letter of alphabet (eithe Upper- or Lowercase)
Use this formula:
=IF(RAND()<=0.5,CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122)))
It generates a random letter from a to z and A to Z characters.
To create a string of random letters,
you need to use "&" character to add the letters together:
=IF(RAND()<=0.5,CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122)))&IF(RAND()<=0.5,CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122)))&IF(RAND()<=0.5,CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122)))
This specific formula generates a string of three random letters. If you drag the cell fill handle over to some range you want to cover with this formula, you will get the range filled with random 3-letter strings. The formula can be quite easily modified to get unique random strings including any and many (more than three) alphanumeric characters.