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))

  • 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!
    -roadmax