Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Random Numbers With No Duplicates

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I want to set up a column of numbers that will be random without any repeating numbers.

Example: If I set up for 1,000 rows, I would like the random numbers to be 1 to 1,000, but the catch would be to have no duplicates in the column.

It has been several years since I worked with Excel and I am sure this can be done with a macro, but I am not sure how to go about it right now.

View Answers     

Similar Excel Tutorials

Generate Random Numbers within a Range in Excel
How to generate random whole numbers (integers) that are between two numbers.  This allows you to set a minimum and ...
Easily Compare Duplicate Values in Excel
Here, I'll show you a simple technique to quickly and easily compare large lists of duplicate values.  This allows ...
Prevent Duplicate Values in Excel
I show you how to prevent duplicate values being entered into Excel using Data Validation. Let's say we have a lis ...
Delete Duplicate Values in All Versions of Excel
How to delete duplicate values from a data set in all versions of Excel.  This includes Excel 2003 and earlier and ...

Helpful Excel Macros

Generate a Non-Repeating List of Random Numbers in Excel - UDF
- Generate a series of non-repeating random numbers in Excel with this UDF (user defined function). This is a great funct
Get the Number(s) Out of a Cell that Contains Both Text and Numbers in Excel - UDF
- This free Excel UDF allows you to get the numbers out of a cell that contain both text and numbers. This is a great fun
Delete Duplicate Rows
- This macro will delete rows that appear twice in a list or worksheet. If two cells are identical, this macro will delete
Delete Only the Text from Cells
- This macro will delete all text from selected cells. This will not delete any other characters or numbers from any cell
Reverse Row or Column Order in a Worksheet
- This macro reverses the order of a selected row or column in excel. It will completely reverse the data.

Similar Topics







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


My god, I have spent the last 4 hours trying to understand the numerous threads for this one and I give up.

This should be (I think) simple.

At work we have our own type of lottery running. I need to be able to generate 6 lines of random numbers.

My numbers are can be found in cells C15 through to Q15 so there are only 15 numbers to pick from.

I would like to be able to hit a command button that would pick 6 random numbers (No duplicates) from the cells C15:Q15 and insert them into Cells T4 through to Y4.
I would like 6 rows of 6 numbers so T4 to Y4, T5 to Y5, T6 to Y6 etc
Also the rows would be different random numbers so no two rows are identical.

Is this possible?


1) I have a contact list for previous conference years. 1999-2006. I want to find all the duplicates and have the duplicates pulled onto either another worksheet or an area on the same worksheet.

2) I had to previously pull a random sample of potential duplicates for another database import. I had about 350 random numbers from a population of 3900, and then found those contacts associated with the random numbers. This list was in alpha order. In the future is there a way to extract the contacts associated with the random number set, such that I do not have to manual deleted contacts?

Future Thanks!

Again, I love this forum!


Does anyone know how to generate a list of random numbers without any repeating numbers?

The specific problem I have is that I have a list of 41 Part Numbers. I need to randomly select 24 of these and I cannot have any duplicates.

Oh, and I have Excel '07 if that makes a difference.

Anyone have ideas?


I wish to run a random sample of 60 numbers from a population of 75 numbers
without repeating any number more than once. The data analysis - sampling
tool generates the 60 random numbers but duplicates several numbers.



I have 1-99 and would like to have the numbers go random with no repeats. I would also like to have 10 random numbers pulled and displayed in their own cells that are random with no repeats from the afore mentioned random set of 1-99. So if i coded this to a button, (Call Random) then cells B1-B9 would display 10 random numbers (one per cell) from the list of 1-99. I am guessing this is very similar to a lotto picker in the idea.


Ultimate goal: Insert a page break at each blank row. I have the macro to insert page breaks already.

I run a report several times a day that needs to be printed and handed out to different people for specific tasks to be taken care of. The issue is that I get the data in one lump and I have to manually insert page breaks each time. There are a large number of 'Locations' that are grouped together to be handed to different people. The bulk of this can be separated just by the first letter of each location. Below is an example of what my data looks like, it only needs to insert a row based on what's in column A.

Code:

                                                                                                                                                                                                                                                                                                                                                                           
   


 
Original data
 


   


 


 


   
A-A-0
 
random data
 
random data
   
A-A-1
 
random data
 
random data
   
A-A-2
 
random data
 
random data
   
A-B-0
 
random data
 
random data
   
A-B-1
 
random data
 
random data
   
A-B-2
 
random data
 
random data
   
A-C-0
 
random data
 
random data
   
A-C-1
 
random data
 
random data
   
A-C-2
 
random data
 
random data
   
B-A-0
 
random data
 
random data
   
B-A-1
 
random data
 
random data
   
B-A-2
 
random data
 
random data
   
B-B-0
 
random data
 
random data
   
B-B-1
 
random data
 
random data
   
B-B-2
 
random data
 
random data
   
B-C-0
 
random data
 
random data
   
B-C-1
 
random data
 
random data
   
B-C-2
 
random data
 
random data
   
B-D-0
 
random data
 
random data
   
B-D-1
 
random data
 
random data
   
B-D-2
 
random data
 
random data
   
B-E-0
 
random data
 
random data
   
B-E-1
 
random data
 
random data
   
B-E-2
 
random data
 
random data
   
B-F-0
 
random data
 
random data
   
B-F-1
 
random data
 
random data
   
B-F-2
 
random data
 
random data
   
C-A-0
 
random data
 
random data
   
C-A-1
 
random data
 
random data
   
C-A-2
 
random data
 
random data
   
C-B-0
 
random data
 
random data
   
C-B-1
 
random data
 
random data
   
C-B-2
 
random data
 
random data
   
C-C-0
 
random data
 
random data
   
C-C-1
 
random data
 
random data
   
C-C-2
 
random data
 
random data
   
C-D-0
 
random data
 
random data
   
C-D-1
 
random data
 
random data
   
C-D-2
 
random data
 
random data
   
C-E-0
 
random data
 
random data
   
C-E-1
 
random data
 
random data
   
C-E-2
 
random data
 
random data
   
C-F-0
 
random data
 
random data
  



Code:

  
         


   
Needs to look like
   


       


   


   


       
A-A-0
   
random data
   
random data
       
A-A-1
   
random data
   
random data
       
A-A-2
   
random data
   
random data
       
A-B-0
   
random data
   
random data
       
A-B-1
   
random data
   
random data
       
A-B-2
   
random data
   
random data
       
A-C-0
   
random data
   
random data
       
A-C-1
   
random data
   
random data
       
A-C-2
   
random data
   
random data
       


   


   


       
B-A-0
   
random data
   
random data
       
B-A-1
   
random data
   
random data
       
B-A-2
   
random data
   
random data
       
B-B-0
   
random data
   
random data
       
B-B-1
   
random data
   
random data
       
B-B-2
   
random data
   
random data
       
B-C-0
   
random data
   
random data
       
B-C-1
   
random data
   
random data
       
B-C-2
   
random data
   
random data
       
B-D-0
   
random data
   
random data
       
B-D-1
   
random data
   
random data
       
B-D-2
   
random data
   
random data
       
B-E-0
   
random data
   
random data
       
B-E-1
   
random data
   
random data
       
B-E-2
   
random data
   
random data
       
B-F-0
   
random data
   
random data
       
B-F-1
   
random data
   
random data
       
B-F-2
   
random data
   
random data
       


   


   


       
C-A-0
   
random data
   
random data
       
C-A-1
   
random data
   
random data
       
C-A-2
   
random data
   
random data
   






I need to set up a random matrix in Excel but I don't want the numbers from
the first column to be identical to the corresponding number in the second
column. Any ideas? (Aside from manually looking for duplicates)



hi experts of excel,

i am a basic user of excel, so I need to seek your help in creating an excel formula or template.

first, i would like to key in 10 numbers in a row. then i would like the excel to random pick 5 numbers from the listed 10 numbers without repeat.

but i have a condition for it, the random picked numbers must able to meet the requirement of the SUM of the 4 numbers must equal to a desired numbers which i wan..

for example,

i key in 1, 2, 3, 4, 5 ,6, 7, 8 ,9 and 1 in a row

then i want the SUM to be 15, and i key in 15, then the random picked numbers by excel will be 7+3+4+1 or 9+1+4+1 or 6+5+2+1 and...

anyone can help me on this..
many thanks...


hi
I want 10,000 set of 20 random numbers in excel's row1 to row10,000.
each set in one row.(column A to T).
random numbers must between 0.00 and 0.25 and have only 2 decimal.
summation of 20 numbers in each set must be 1 and number of non zeroes in each set must be 7.
please write vba program for this.
it's very important for me.
thanks alot.


Hi
Looking for a way to select from a data set, which has nubers listed more than once. A number of Random Numbers withot repeating the numbers
Using Ecel 2007

Prefered option would be no VBA

Data set could comprose of:

1,5,9,21,33,2,1,30,21,1,33 - by way of an exaample
I would like to select,say 5 random numbers form this dataset without repeating a number

Any thoughts?


I have a macro that generates 100 random numbers in 100 cells when a CommandButton is clicked. If I click on the button, 100 random numbers show up. Let's say I close the workbook without saving changes and reopen it. I will click on the CommandButton again and the SAME random numbers are generated!! It appears there is some sort of pattern for the random number generation. Each time I click the CommandButton, it does what it is supposed to; generate random numbers for each cell. But if I close the workbook without saving it goes right back to the batch of random numbers.


In my worksheet in column CC in C1:C20 I want Excel to ganerate random numbers between 1 and 80 without repeating the same numbers.
At present I have the following formula entered and it works, but after I click F9 some repeated numbers are generated.

=INT(RAND()*(80-1)+1)

How can I change the formula or replace it?
Thank you in advance for your help.
Ragards to all supporters.
John.


Hi there everybody, here's what I wanna do, I'd like to generate twelve unique random numbers (from one to twelve) in twelve cells (might sound obvious but I just wanna make myself clear), the thing is, even tho I know how to do that this is what I really want excel to generate:

cell A1 must only generate random numbers from one to three (either one, two or three)
cell A2 must only generate random numbers from one to three (either one, two or three)
cell A3 must only generate random numbers from one to four (either one, two, three or four)
cell A4 must only generate random numbers from two to five (either two, three, four or five)
cell A5 must only generate random numbers from three to six (either three, four, five or six)
cell A6 must only generate random numbers from four to seven (either four, five, six or seven)
cell A7 must only generate random numbers from five to eight (either five, six, seven or eight)
cell A8 must only generate random numbers from six to nine (either six, seven, eight or nine)
cell A9 must only generate random numbers from seven to ten (either seven, eight, nine or ten)
cell A10 must only generate random numbers from eight to eleven (either eight, nine, ten or eleven)
cell A11 must only generate random numbers from nine to twelve (either nine, ten, eleven or twelve)
cell A12 must only generate random numbers from ten to twelve (either ten, eleven or twelve)

Once again, numbers must be unique, so if cell A1 came up with a one then cell A2 must come up with either a two or a three :D


Hope u can guys help me, some of u might take this as a sort of a challenge!!!
Thank u!!!!


Is it possible to have a group of random numbers equal a value, even when consistantly pressing F9? For instance, in range A1:A20 I would like random numbers, in cell B1 I would like to have 50. What I would like is for the 20 random numbers to add up to 50 Even if I consistantly press F9. If I change B1 to 63 I would like the 20 random numbers to add up to 63.

Please Help,

Stephen

Thanks in advance!


For the life of me I can't figure out how to get Excel (2007) to sort a column of numbers (1-20) into random numbers. I've seen a few sites that say to use =rand(), hit Ctrl and the plus sign, copy/paste, but none of the directions ever seem to give me the desired result.

I have A1-A20 with number 1-20, respectively. I just want Excel to generate these numbers in a random order--either in column A or column B. Can someone please explain how to do this?


Hi,

Can someone please Help me in generating Random (non Duplicate) numbers as specified by me (macro allows me to choose the number of random numbers to create), from the data in a specified column in the activesheet (lets assume that the data is in Column "C" and the range is endless), and then save the same with a file name specified by me.

While the random numbers are saved in the new file, the macro also delets the generated numbers from the active sheet

Rgds,

Mohit

Hi,

I'm using a random number generator in excel to create values between 17.0 and 21.0 to one decimal point.

Currently using =RANDBETWEEN(1700,2100)/100 to achieve this. (It will give me 18.3 / 19.4 etc etc).

I'm then using this to create 100 random numbers between 17 and 21. What I want to be able to do is out of these 100 random numbers to have 5% (5 numbers) to go down to 16.0 but randomly.

I dont want it to be a formula =RANDBETWEEN(1600,2100)/100 because I only want 5% to be less than 17.0.

Any help would be greatly appreciated.

Regards,
Paul


I would like to generate random numbers between two values, then have the list ordered from smallest to largest. I need 20 random numbers between two values that are kept on the same sheet. I am not worried about duplicates, however I need the random list to be ordered from smallest to largest.

More details:
in cell A2 I have the lower bound
in cell A3 I have the upper bound
I would like the results to display in column C

So, if I had
A2=1
A3=43
I have currently used RANDBETWEEN($A$2,$A$3) for the 20 entries in column C, however the listing isn't ordered/sorted from largest to smallest.

I know I can copy the column and paste it as values to another column, then sort the list, however I was wondering if I could get these 20 random values ordered automatically.

I am using Excel 2007.


How can you use random numbers without repeating them on a worksheet? The way
it is set up now is, example: =RANDBETWEEN(1,8)
I would like to be able to use the random between but I don't want the
numbers to repeat themselves within the worksheet. Any suggestions or Ideas?



Hi,

I'm running into problems with a wee project I'm working on. It involves selecting items for auditing from a list, randomly without repeats.

In cell A1 I have the number of reports required to be selected - this could be anything from 1 - 1000.

A2 has the maximum number of the range that I want the random numbers to lie in, the minimum being 1.

I would like to populate a column of numbers, from B1 down with random selections between 1 and the maximum (from cell A2) without any repeats.

So if A1 contains '20' and A2 contains '100' I would like to generate 20 random numbers that lie between 1 and 100 and have these copied to cells B1 to B100.

I tried formulaes but there doesn't seem to be an easy way without the possiblity of repeats. So I've attempeted some VB but can't get that to work either.

Could anyone provide a possible way of getting this to work.

Thanks in advance


Dear Forum,

I am using the VBA code below (bottom) to return random numbers for a selected area of cells where the range of random numbers is between 1 and the
total number of cells. Unfortunately sometimes the random numbers appear more than once and I would like to amend this to ensure duplicates are not included, any help would be greatly appreciated, thanks, Matt

Ps I have already tried substituting with VBUniqRandInt() but it returns #NAME? in every selected cell??, i.e;

Code:

Sub RandomTable4()

For Each r In Selection
    r.Formula = "=int(VBUniqRandInt() *" & Selection.Count & "+1)"
Next
End Sub

Sub RandomTable4() ' but this returns duplicates

For Each r In Selection
    r.Formula = "=int(Rand() *" & Selection.Count & "+1)"
Next
End Sub





Hi,
How to find the second smallest value in a series of random numbers.
For example, if the series having random numbers as below
11,18,34,67,18,94,68,11,56
In this some numbers are repeating, but I want to skip the same and find unique numbers. There is function "small(array,k)". But this will list the repeated values as well.

The answer should be 18.

Any tips on this?


I am trying to generate random numbers between 0 - 9 in ten cells that do not repeat an always include every value 0 - 9 (0, 1, 2, 3, 4,...9). The object is to have the numbers randomize each time. So cells A1:A10 could look like this:

3
9
4
5
1
0
6
8
7
2

Or, they could look like this:

9
7
5
2
8
4
1
3
6
0

I understand the rand and randbetween formulas, but am having trouble finding the logic to make them non-repeating, all inclusive and still random.

Any help? A bonus point for anyone who can figure out what this is for (hint: there is a kick-off in about four hours).




I'm wanting to use the =RANDBETWEEN Function for a growing price list (updated monthly) where I'm wanting to generating my own random codes, but I don't want any duplicates.

Example. I have a price list with 1000 lines. I want to generate a number from 100000 to 999999 that is random without repeating. I'm not sure how good the =RANDBETWEEN function works for this...

The next problem is, this price list will keep expanding. Meaning next month I have to add 1000 more codes that I also want to generate Random numbers from 100000 to 999999 that will be added to the list of random generated numbers I created last month, without creating duplicate from the numbers created last year.

Is this possible?

Thanks,