Email:      Pass:    Pass?
Advertisements


Free Excel Forum

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.


Similar Excel Video Tutorials

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.


This is a familiar topic but the challenge is different. I wish to develop an array of non-repeating random numbers from a given range say, 1 to 30 and where each of the numbers in the range will be represented. in other words, the random list must be exhaustive (without duplicates).

The other aspect of the problem (and which I find daunting) is that no recourse should be had to the Worksheet as interface. At no stage should we write to the worksheet to sort, randomize and copy back to array.


I have attempted a solution which is not pretty. First, I built up an array of elements drawn at random from the range using RANDBETWEEN. I had to do this by loooping quite a number of times such that I would reasonably pick all of the numbers in the randomization process. Next, I subjected my overbloated array to cleansing by weeding out all duplicates when present. The New Collection Method came in handy for this exercise.

As you would appreciate, this approach could almost always get me there but it is at heart not scientific and ponderous, to say the least. Can someone kindly come up with a nifty solution?

TIA


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!


Hi All,

In Column A i generate random numbers using the Rand() function. In column B, I would like to arrange these random numbers in ascending order, So that each time column A recalculates the random numbers, they appear in numerical order in column B.

Got any suggestions?


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?


Is there a formula/ Function I can use that would pick a random set of 6 numbers between 1-100 that wouldn't include duplicates?


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?



Hello,
Basically I have 2 ranges : Range("E5:E10") and Range("E14:E19").
In the 1st one, the user inserts numbers from 1 to 42. In the second, random numbers (between 1 to 42) are generated when a button is clicked.

Here is part of my code:
Private Sub CommandButton2_Click()
Dim randomnumbers As Range
Dim MyCell As Range
Set randomnumbers = Range("E14:E19")
For Each MyCell In randomnumbers
MyCell.Value = Int((42 * Rnd) + 1)
Next MyCell
Range("E14:E19").Sort key1:=Range("E14"), order1:=xlAscending, Header:=xlNo
End Sub

Here is what I still want to do:
1) When I generate random numbers for Range("E14:E19"), I don't want duplicates (i.e: I don't want (3,3,12,26,37,41)
2) I want to be able to count the # of numbers that are the same in the 2 ranges, which would be displayed in msgbox. (i.e if Range("E5:E10") displays: (3,5,13,24,28,41) and Range("E14:E19") displays: (7,13,29,35,41,42), in this case I would like my msgbox to display: You got 2 out of 6 !

I would really appreciate if someone could help me !


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