Select B2:T2 and array-enter either =RandSeq() or =RandSeq(True) (the latter if you want the sequence to change each time you calculate.
Then drag down as far as you want.
Here's the function:
Function RandSeq(Optional bVolatile As Boolean = False) As Long()
' shg 2006-0307
' Worksheet function *only*
' Returns a random sequence from 1 to N to the calling range
' (entered in a single cell, returns 1)
If bVolatile Then Application.Volatile
Dim nRow As Long ' count of rows
Dim nCol As Long ' count of columns
Dim afRnd() As Single ' random rumbers
Dim nNum As Long ' count of afRnd
Dim iRnd As Long ' index to afRnd
Dim jRnd As Long ' secondary index to afRnd
Dim aiRank() As Long ' rank array
Dim iRow As Long ' row index to aiRank
Dim iCol As Long ' column index to aiRank
' verify that the caller is a range
If Not TypeOf Application.Caller Is Range Then Exit Function
With Application.Caller
nNum = .Count
nRow = .Rows.Count
nCol = .Columns.Count
End With
' size arrays to size of range
ReDim afRnd(0 To nNum - 1)
ReDim aiRank(0 To nRow - 1, 0 To nCol - 1)
' get random numbers
For iRnd = 0 To nNum - 1
afRnd(iRnd) = Rnd
Next iRnd
' rank 'em
For iRnd = 0 To nNum - 1
iRow = iRnd \ nCol
iCol = iRnd Mod nCol
For jRnd = 0 To nNum - 1
If afRnd(iRnd) >= afRnd(jRnd) Then aiRank(iRow, iCol) = aiRank(iRow, iCol) + 1
Next
Next
RandSeq = aiRank
End Function
Bookmarks