Unique Random Numbers

JVirtual

New Member
Joined
Jun 27, 2008
Messages
3
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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
 
Upvote 0
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
 
Upvote 0
and did you try my suggestion?

You can move the Rand() formula to another column and hide it if you like...
 
Upvote 0
yep, I was thrown a bit by the reference to names but it does work the same for my list of numbers.

Thanks

JV
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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
Back
Top