 # random numbers

• 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

• 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

Code
1. Public Sub main()
2. Dim iRandArray() As Integer
3. Dim i As Integer
4. RandomOrder 20, iRandArray()
5. For i = 1 To UBound(iRandArray)
6. Sheets("Sheet2").Range("A1").Offset(i, 0).Value = iRandArray(i)
7. Next
8. 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
Craig

Edit: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!