Random Numbers With No Duplicates 


Random Numbers With No Duplicates  Excel 
View Answers 
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.
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
Random Num 1 100 Not Divisible By 3
 See how to create random numbers between 1 and 100 that are not divisible by 3 with a large array formula. See these functions in this video:
The ...
The ...
Excel Magic Trick 276 MoreFunc Excel addin 66 New functions
 See how to download and install MoreFunc. See new functions like:
EVAL evaluates a text string
FILENAME gives workbook name
FILENAME gi ...
EVAL evaluates a text string
FILENAME gives workbook name
FILENAME gi ...
Find Duplicates, Then Extract Unique Records
 See how to use SUMPRODUCT and the Join Symbol (& Ampersand) to group duplicates and then see how to use Advanced Filter Unique Records to extract ...
Delete Unique Records: MATCH & ROW
 Use the MATCH & ROW functions to create a logical test to find the First Occurrence of items in a list with duplicates.
See how to use ...
See how to use ...
Helpful Excel Macros
Generate a NonRepeating List of Random Numbers in Excel  UDF
 Generate a series of nonrepeating random numbers in Excel with this UDF (user defined function). This is a great funct
 Generate a series of nonrepeating 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
 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
 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
 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.
 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
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?
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. 19992006. 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!
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?
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.
without repeating any number more than once. The data analysis  sampling
tool generates the 60 random numbers but duplicates several numbers.
I have 199 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 199. So if i coded this to a button, (Call Random) then cells B1B9 would display 10 random numbers (one per cell) from the list of 199. 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 nonrepeating 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
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:
Code:
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 AA0 random data random data AA1 random data random data AA2 random data random data AB0 random data random data AB1 random data random data AB2 random data random data AC0 random data random data AC1 random data random data AC2 random data random data BA0 random data random data BA1 random data random data BA2 random data random data BB0 random data random data BB1 random data random data BB2 random data random data BC0 random data random data BC1 random data random data BC2 random data random data BD0 random data random data BD1 random data random data BD2 random data random data BE0 random data random data BE1 random data random data BE2 random data random data BF0 random data random data BF1 random data random data BF2 random data random data CA0 random data random data CA1 random data random data CA2 random data random data CB0 random data random data CB1 random data random data CB2 random data random data CC0 random data random data CC1 random data random data CC2 random data random data CD0 random data random data CD1 random data random data CD2 random data random data CE0 random data random data CE1 random data random data CE2 random data random data CF0 random data random data
Code:
Needs to look like AA0 random data random data AA1 random data random data AA2 random data random data AB0 random data random data AB1 random data random data AB2 random data random data AC0 random data random data AC1 random data random data AC2 random data random data BA0 random data random data BA1 random data random data BA2 random data random data BB0 random data random data BB1 random data random data BB2 random data random data BC0 random data random data BC1 random data random data BC2 random data random data BD0 random data random data BD1 random data random data BD2 random data random data BE0 random data random data BE1 random data random data BE2 random data random data BF0 random data random data BF1 random data random data BF2 random data random data CA0 random data random data CA1 random data random data CA2 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)
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...
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.
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?
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()*(801)+1)
How can I change the formula or replace it?
Thank you in advance for your help.
Ragards to all supporters.
John.
At present I have the following formula entered and it works, but after I click F9 some repeated numbers are generated.
=INT(RAND()*(801)+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!!!!
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!
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?
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 (120) 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 A1A20 with number 120, respectively. I just want Excel to generate these numbers in a random ordereither in column A or column B. Can someone please explain how to do this?
I have A1A20 with number 120, respectively. I just want Excel to generate these numbers in a random ordereither 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 1100 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
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'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.
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?
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 !
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
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