unique random numbers

  • Thread starter Thread starter einemanw
  • Start date Start date
E

einemanw

I need to assign a unique random number to 500 students in an excel
worksheet. Can anyone tell me how to do this?
 
=RAND()*10000000 in the adjacent cell and then copy down
the list of students and then sort by the column with the
formula in.
 
Yep.


Best way is to use really large random numbers where the odds of 2 o
your 500 students getting the same random number is very small. The
Just sort the column.

I assume you want to give the students a number so you can post thei
grades. To do this, you may want to truncate the large random numbe
to 3 digits and then sort, keeping in mind that as the random numbe
gets smaller the odds of two students with the same numbe
increases....you can right a simple function to test this of course
 
The best way is to use a method that only assigns unique random numbers.
This can be done without using kludge methods.
 
Here is a generalized function for shuffling a 1D long array: (I then show
you how to call it with an array of numbers, 1 to 500) and place this in the
cells A1:A500

Public Function ShuffleArray(varr)

'
' Algorithm from:
' The Art of Computer Programming: _
' SemiNumerical Algorithms Vol 2, 2nd Ed.
' Donald Knuth
' p. 139
'
'
Dim List() As Long
Dim t As Long
Dim i As Long
Dim j As Long
Dim k As Long
Dim lngTemp As Long

t = UBound(varr, 1) - LBound(varr, 1) + 1
ReDim List(1 To t)
For i = 1 To t
List(i) = varr(i)
Next
j = t
Randomize
For i = 1 To t
k = Rnd() * j + 1
lngTemp = List(j)
List(j) = List(k)
List(k) = lngTemp
j = j - 1
Next
ShuffleArray = List
End Function

This shows how to call it:

Sub Main()
Dim varr()
Dim varr1
ReDim varr(1 To 500)
For i = 1 To 500
varr(i) = i
Next
varr1 = ShuffleArray(varr)
Range("A1:A500").Value = Application.Transpose(varr1)
End Sub
 
Back
Top