# Thread: Unique Random numbers (2003)

1. ## Unique Random numbers (2003)

Loungers,

I have a list of 30 names that I want to allocate unique random numbers to between 1 - 30 - ie no duplicate numbers.

I've tried =Randbetween() but this produces duplicate numbers

Any thoughts?

2. ## Re: Unique Random numbers (2003)

Dean

This should do the job

http://www.ozgrid.com/VBA/RandomNumbers.htm

3. ## Re: Unique Random numbers (2003)

Enter the numbers 1 ... 30 in a column next to the names (for example using Edit | Fill Down | Series...).
In the cells to the right of that, enter the formula =RAND().
Select these two columns of cells (not the column with the names!)
Sort on the column with random numbers. This will shuffle the numbers 1 ... 30 in a random order.

4. ## Re: Unique Random numbers (2003)

Jerry,

Thanks for the quick reply - that works fine, however I need to allocate a unique number in 23 cells to be able to generate a vlookup to a list of names.

Any other thoughts?

5. ## Re: Unique Random numbers (2003)

Hans - Thanks for the reply - that works fine

Thanks again

6. ## Re: Unique Random numbers (2003)

Dean

Here is a VBA response

Function UniqueRandomNumbers(NumCount As Long, LLimit As Long, ULimit As Long) As Variant
' creates an array with NumCount unique long random numbers in the range LLimit - ULimit (including)
Dim RandColl As Collection, i As Long, varTemp() As Long
UniqueRandomNumbers = 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)
On Error GoTo 0
Loop Until RandColl.Count = NumCount
ReDim varTemp(1 To NumCount)
For i = 1 To NumCount
varTemp(i) = RandColl(i)
Next i
Set RandColl = Nothing
UniqueRandomNumbers = varTemp
Erase varTemp
End Function

Sub TestUniqueRandomNumbers()
Dim varrRandomNumberList As Variant
varrRandomNumberList = UniqueRandomNumbers(30, 1, 100)
Range(Cells(1, 1), Cells(30, 1)).Value = _
Application.Transpose(varrRandomNumberList)
End Sub

7. ## Re: Unique Random numbers (2003)

Jerry - will give that a shot also

Thanks very much for your assistance

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•