Can someone please tell me how to generate randon numbers from data on sheet 2 which do not repeat themselves. eg sheet 2 data numbers 1 to 20
random numbers should be 3,15,7,9,1,,,,
not 3,15,7,7,9,3,,,,,
Regards
Craig
random numbers



This generates a list of numbers from 1 to 20 in random order on sheet 2. Not sure if it is what you want, but it might help
CodePublic Sub main() Dim iRandArray() As Integer Dim i As Integer RandomOrder 20, iRandArray() For i = 1 To UBound(iRandArray) Sheets("Sheet2").Range("A1").Offset(i, 0).Value = iRandArray(i) Next End Sub
Code
Display MorePrivate Sub RandomOrder(ByVal iNumRange As Integer, ByRef iRandArray() As Integer) Dim iNumArray() As Integer Dim iRandValue As Integer ReDim iNumArray(1 To iNumRange, 1 To 2) ReDim iRandArray(1 To iNumRange) For i = 1 To iNumRange iNumArray(i, 1) = i iNumArray(i, 2) = 1 Next For i = 1 To iNumRange Randomize iRandValue = Int((iNumRange * Rnd) + 1) If iNumArray(iRandValue, 2) = 0 Then i = i  1 Else iRandArray(i) = iRandValue iNumArray(iRandValue, 2) = 0 End If Next End Sub

Here's a formula you can use. In this example, enter the numbers 1 thru 20 in cells A1:A20 on Sheet2 and in cells B1:B20 enter the formula =Rand()
Then use this formula:
=INDEX(Sheet2!A1:A20,RANK(Sheet2!B1,Sheet2!B1:B20)) 
You'll want to set the range references to absolute when you fill the formula down:
=INDEX(Sheet2!$A$1:$A$20,RANK(Sheet2!B1,Sheet2!$B$1:$B$20))

Thanks for the help. I used btadams version and it works a treat. Its actually just for my footy tipping.
Regards
CraigEdit:Willr[NA]


Re: random numbers
This is *possibly* a simpler way...
In cells A1:A20 on Sheet2 put the formula;
=RAND()
In cell A1 on Sheet1 put this formula;
=RANK(Sheet2!A1,Sheet2!$A$1:$A$20)
Then copy cell A1 on Sheet1 thru to cell A20 on Sheet1.
Good Luck!
roadmax 
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!