How to get 50 unique random values from 1 to 150

[James Perry]

Dear friends:
I have 150 questions in my database in a separate excel sheet.
I want 50 random questions out of those 150 questions each time a user opens the excel file.
Thank u.

 

[NARAYANK991]

Hi James ,

Similar questions have been asked in the past ; one of the answers here contains a link to a macro ; see if it helps :

http://chandoo.org/forum/threads/generate-random-number.12189/#post-71410

Narayan

 

[Marc L]

​

Hi James,​

​

this is a fast unique random values function, check it by launching Demo on a new workbook :​

Function ArrayRandom(Number%, Lower%, Upper%) As Integer()
         ReDim AR%(1 To Number)

         Randomize
         C% = Upper - Lower + 1

         For N% = 1 To Number
             Do
                 R% = Lower + C * Rnd
                  V = Application.Match(R, AR, 0)
             Loop Until IsError(V)

             AR(N) = R
         Next

         ArrayRandom = AR
         Erase AR
End Function


Sub Demo()
    Dim UV%()
                UV = ArrayRandom(50, 1, 150)
    [B1:B50].Value = Application.Transpose(UV)
    Erase UV
End Sub

Enjoy !​

​

 

[James Perry]

THANK YOU!

 

[Marc L]

If you need to sort the column, launch SortDemo on a test workbook :​

Function UniqueValues(Number%, Lower%, Upper%) As Integer()
        ReDim AR%(1 To Number)
 
        Randomize
        C% = Upper - Lower + 1
 
        For N% = 1 To Number
            Do
                R% = Lower + C * Rnd
            Loop Until IsError(Application.Match(R, AR, 0))
 
            AR(N) = R
        Next
 
        UniqueValues = AR
        Erase AR
End Function
 
 
Sub SortDemo()
    [B1:B50].Value = Application.Transpose(UniqueValues(50, 1, 150))
    [B1:B50].Value = Application.Transpose(Evaluate("TRANSPOSE(SMALL(B1:B50,ROW(1:50)))"))
End Sub

In SortDemo procedure, the first code line loads the array into the range.​

The second line sorts the range through maybe the most powerful VBA instruction : Evaluate …​