unique random numbers

[einemanw]

I need to assign a unique random number to 500 students in an excel
worksheet. Can anyone tell me how to do this?

 

[Frank Kabel]

Hi
have a look at
http://www.mvps.org/dmcritchie/excel/random.htm

HTH
Frank

 

[Ed]

=RAND()*10000000 in the adjacent cell and then copy down
the list of students and then sort by the column with the
formula in.

 

[jasonsweeney]

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

 

[Tom Ogilvy]

The best way is to use a method that only assigns unique random numbers.
This can be done without using kludge methods.

 

[Tom Ogilvy]

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

 

[xnman]

Tom, how is this done?
xnman

The best way is to use a method that only assigns unique random numbers.
This can be done without using kludge methods.