Randomly select unique numbers

Alex Sanchez

New Member
Jun 26, 2007
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?


Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Joe Was

MrExcel MVP
Feb 19, 2002
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 _

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

'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
End Sub

Damon Ostrander

MrExcel MVP
Feb 17, 2002
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.



MrExcel MVP
Oct 12, 2006
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
    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


MrExcel MVP
Jan 15, 2007
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.

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back