Randomly select unique numbers

[Alex Sanchez]

I have a lot of ID numbers and I want to randomly select 10 of them. I already tried the sampling, but that one repeats some of the numbers at times. Is there a way to randomly select unique numbers in Excel?

Thanks,

 

[Joe Was]

This works by a code slight-of-hand trick. Your list of data is in column "A" the list is copied to Column "B" the code then randomly picks an item form Column "A" and adds it to a results grid and then erases that pick from the list in column "A." If a blank is picked it re-picks until a value is found. When the Grid is full it copies the backup list in Column "B" on top of Column "A" and erases the backup list.

This way it only displays a Unique Set of Random selections from the Data List.

You can adjust the Grid Columns and Rows and the Starting row of the data list as needed in the sample below:


Sub RandomGridUnique()
'Standard module code, like: Module1.
Dim lngCols&, lngRows&, lngListBottom&, lngListCount&
Dim strThisItem$
Dim varTemp As Variant

'Please build your list of items in
'Column "A" starting in Cell: A10.
'You must have at least the same number of items in the list as you have picks!

lngListBottom = Sheets("List").Range("A65536").End(xlUp).Row

lngListCount = Sheets("List").Range("A10:A" & lngListBottom).Count

'Adjust number to match number of picks!
If lngListCount < 25 Then
MsgBox "Your list of items does not contain at least 25 items?)"
Exit Sub
End If

Sheets("List").Range("A10:A" & lngListBottom).Copy _
Destination:=Sheets("List").Range("B10")

'Grid size for picks!
For lngCols = 4 To 8
For lngRows = 2 To 6

myNonBlnk:
'Note: 10 = the Starting Row of the data list in the formula below!
varTemp = Int((lngListBottom * Rnd) + 10)

strThisItem = Sheets("List").Range("B" & varTemp)

If strThisItem = "" Then GoTo myNonBlnk

Sheets("List").Cells(lngRows, lngCols) = strThisItem
Sheets("List").Range("B" & varTemp).ClearContents
Next lngRows
Next lngCols

Sheets("List").Range("B10:B" & lngListBottom).ClearContents
Sheets("List").Range("A1").Select
End Sub

 

[Damon Ostrander]

Hi Alex,

One easy way to do this uses the Excel Rank function. First, create a list of random numbers, one for each ID number. Then find the rank of each random number in the list using the Rank() function. Then simply take the corresponding ID numbers having rank 1..10.

Keep Excelling.

Damon

 

[Jonmo1]

Here's a method I use. It loops and adds a unique value from column A to column B. ON each loop, it generates a value from A, and compares it to column B to see if it has already been used. If Yes, then it get's another one. Until it gets a value that has not already been used.

Sub test()
Dim MyValue
For i = 2 To 11
    LR = Cells(Rows.Count, "B").End(xlUp).Row
restart:
    Randomize
    MyValue = Int((41 * Rnd) + 1)
    If MyValue = 1 Then GoTo restart
    x = Cells(MyValue, "A").Value
    If WorksheetFunction.CountIf(Range("B1:B" & LR), x) > 0 Then GoTo restart
    
    Cells(i, "B").Value = x
Next i
End Sub

 

[mikerickson]

Another approach; your ids are in IDarray, you want to select 10 of them.

For i= 0 to 9
   rndIndex = Rnd() * Ubound(IDarray)
   tempVal = IDarray(i)
   IDarray(i) = IDarray(rndIndex)
   IDarray(rndIndex) = tempVal
Next I

IDarray(0) through IDarray(9) are your ten random, unique id numbers.