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?
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 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!
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.
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.
For i = 2 To 11
LR = Cells(Rows.Count, "B").End(xlUp).Row
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