# Unique Random Numbers

#### JVirtual

##### New Member
New user here, but I am looking for a way to extract unique (non duplicate) random numbers from a list. I have 300 or so numbers in column A and need to generate 30 random numbers (to column B) from the values in column A. I have tried the 'randbetween' function but it periodically duplicates one or more of the values in the list of 30 random numbers. I can keep hitting F9 until I get a list with no duplicates but I was hoping for an easier way from the start.

Thanks

JV

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### Jonmo1

##### MrExcel MVP
Welcome to the board...

You can do this fairly simply with a helper column...
I think there is still an extremely slim chance of a duplicate. But EXTREMELY slim chance...

IN column B put
=RAND()
In Column C put
=INDEX(\$A\$1:\$A\$300,RANK(B1,\$B\$1:\$B\$300))

Here's a small sample of just 10 names, giving 5 random results...

Hope this helps..
Personal.xls
ABCD
1Jon0.839Jenna
2Jenna0.935Jon
3Fred0.812Fred
4Mark0.136Alice
5Eric0.061Wilma
6George0.546
7Alice0.406
8Betty0.127
9Wilma0.764
10Nancy0.050
Sheet1

#### JVirtual

##### New Member
Sorry, I should know better but I need to be more descriptive.

In column A are sequential numbers (say from 31000 to 31300) and I need to select 30 random numbers from that sequence of numbers. This is for an audit process that we run and the list in column A will change from week to week but it will always be sequential and always be sorted from lowest to highest from the start.

Thanks

JV

#### Jonmo1

##### MrExcel MVP
and did you try my suggestion?

You can move the Rand() formula to another column and hide it if you like...

#### JVirtual

##### New Member
yep, I was thrown a bit by the reference to names but it does work the same for my list of numbers.

Thanks

JV

Replies
7
Views
860
Replies
24
Views
3K
Replies
6
Views
973
Replies
11
Views
3K
Replies
4
Views
1K

1,191,166
Messages
5,985,051
Members
439,935
Latest member
Monty238

### 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.

### Which adblocker are you using?

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

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