Random Numbers With No Duplicates 
New Excel Help Service!
We need your input to help create a new Live Online Excel Help service.
Please take our survey and let us know your thoughts!
Have a great day!
Don


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 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 ...
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 ...
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 ...
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 ...
How to delete duplicate values from a data set in all versions of Excel. This includes Excel 2003 and earlier and ...
Highlight Duplicate Values in Excel
How to highlight duplicate values in a list. Also, how to arrange those values next to each other so they are easy ...
How to highlight duplicate values in a list. Also, how to arrange those values next to each other so they are easy ...
Make All Numbers in a List Positive in Excel
Take a list of numbers and make them all positive, regardless of whether the list contains a mixed set of positive ...
Take a list of numbers and make them all positive, regardless of whether the list contains a mixed set of positive ...
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
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)
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.
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,
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.
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:
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
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 nonrepeating, all inclusive and still random.
Any help? A bonus point for anyone who can figure out what this is for (hint: there is a kickoff in about four hours).
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 nonrepeating, all inclusive and still random.
Any help? A bonus point for anyone who can figure out what this is for (hint: there is a kickoff in about four hours).
I want to create random numbers for a specific total within a range.
I'm a newbie to Excel. So if anyone can create and attach the excel file it will be more helpful.
The upper limit,lower limit, No of random numbers & Total has to be entered manually and the excel sheet has to provide 31 random numbers.
For example:
I want to create random numbers for 40,000 within a range of 250 to 2,000
Lower limit = 250
Upper limit =2,000
Random numbers Total will be 40,000
No. of random numbers =31
Duplicate Randoms: Allowed
1864
1800
338
780
1304
1600
1281
1239
1700
878
1139
1390
1750
540
1839
1460
750
828
1925
1724
1822
1353
1769
1950
1952
1603
1423
1999

Total =40,000

Thanks a lot.
I'm a newbie to Excel. So if anyone can create and attach the excel file it will be more helpful.
The upper limit,lower limit, No of random numbers & Total has to be entered manually and the excel sheet has to provide 31 random numbers.
For example:
I want to create random numbers for 40,000 within a range of 250 to 2,000
Lower limit = 250
Upper limit =2,000
Random numbers Total will be 40,000
No. of random numbers =31
Duplicate Randoms: Allowed
1864
1800
338
780
1304
1600
1281
1239
1700
878
1139
1390
1750
540
1839
1460
750
828
1925
1724
1822
1353
1769
1950
1952
1603
1423
1999

Total =40,000

Thanks a lot.
Hi all,
I have a workbook that has a list of numbers in column A. Can anyone please assist with the VBA to do the following:
1. Bring up a message box asking how many random cells should be selected.
2. Select the requested number of random cells.
3. Copy to column A on sheet 2.
It would be beneficial if a cell could only be selected once i.e. to avoid the random selection of duplicates,
Thanks in advance for any help!
I have a workbook that has a list of numbers in column A. Can anyone please assist with the VBA to do the following:
1. Bring up a message box asking how many random cells should be selected.
2. Select the requested number of random cells.
3. Copy to column A on sheet 2.
It would be beneficial if a cell could only be selected once i.e. to avoid the random selection of duplicates,
Thanks in advance for any help!
hey guys,
here's my question 
is there any way of generating random numbers within a set range in ONE cell only without having any repetition until all the numbers have been cycled through once, after which the random selection starts again.
I've tried using RANDBETWEEN but whilst this does the random number generation in once cell only, it does repeat numbers. What I would like is a similar function which does not.
So if we have 1 as the lowest number, and 75 as the highest, it should be on the 76th number generated where we have the cycle of random generation start again.
Any ideas anybody? and the numbers do have to generate in the same cell each time only.
thanks!
here's my question 
is there any way of generating random numbers within a set range in ONE cell only without having any repetition until all the numbers have been cycled through once, after which the random selection starts again.
I've tried using RANDBETWEEN but whilst this does the random number generation in once cell only, it does repeat numbers. What I would like is a similar function which does not.
So if we have 1 as the lowest number, and 75 as the highest, it should be on the 76th number generated where we have the cycle of random generation start again.
Any ideas anybody? and the numbers do have to generate in the same cell each time only.
thanks!
Hi,
i am dealing with random number generator and the link below gives information to return random numbers from Normal Distribution but i would like to know how to return random numbers from LogNormal Distribution, Weibull Distribution and Gamma Distribution. Is anyone familiar with this or would be able to give me some advise?
http://www.anthonyvba.kefra.com/vba/vba4.htm
Thank you.
i am dealing with random number generator and the link below gives information to return random numbers from Normal Distribution but i would like to know how to return random numbers from LogNormal Distribution, Weibull Distribution and Gamma Distribution. Is anyone familiar with this or would be able to give me some advise?
http://www.anthonyvba.kefra.com/vba/vba4.htm
Thank you.
Hello All
I have 8 numbers as shown below:
4.977
4.988
4.992
4.991
4.990
4.984
4.939
4.963
These numbers (only) appear in a random nature in a column B of a spread sheet. This column can have up to 3000 rows containing a random subset of the above numbers only. What I wish to return is which one of these numbers appears with the greatest frequency. I hope that makes sense.
Thanks in advance!
I have 8 numbers as shown below:
4.977
4.988
4.992
4.991
4.990
4.984
4.939
4.963
These numbers (only) appear in a random nature in a column B of a spread sheet. This column can have up to 3000 rows containing a random subset of the above numbers only. What I wish to return is which one of these numbers appears with the greatest frequency. I hope that makes sense.
Thanks in advance!
I'm a newbie in the Excel world and I'm hoping that someone can help me with a macro. Context: column A contains 66 entries, with numbers 1 to 11 where each number has its corresponding number of cells associated. I.e. 1 has 1cell, 5 has 5cells and 11 has 11cells.
I'm hoping to have some help with a macro that would: make an initial random selection from the 66 cells, place that number in an ordered ranked column on the same sheet  ranking from 11th place to 1st place, remove all the corresponding number cells equal to the number initially chosen (i.e. if the initial random pick is 10, then all cells with the number 10 would be excluded from the next random selection), Restack the remaining numbers in column A and do the random selection again, continue until there are no numbers left in column A and all 11 numbers are ranked in reverse order in another column on the same sheet. I am really not sure how to start this off but I reviewed some of the posts in this forum and I found this code for the random part. I hope that someone can help me.
Cheers
Sub PickRandom()
MsgBox Cells(Rnd * (Cells(Rows.Count, 1).End(xlUp).Row  1) + 2, 1)
End Sub
I'm hoping to have some help with a macro that would: make an initial random selection from the 66 cells, place that number in an ordered ranked column on the same sheet  ranking from 11th place to 1st place, remove all the corresponding number cells equal to the number initially chosen (i.e. if the initial random pick is 10, then all cells with the number 10 would be excluded from the next random selection), Restack the remaining numbers in column A and do the random selection again, continue until there are no numbers left in column A and all 11 numbers are ranked in reverse order in another column on the same sheet. I am really not sure how to start this off but I reviewed some of the posts in this forum and I found this code for the random part. I hope that someone can help me.
Cheers
Sub PickRandom()
MsgBox Cells(Rnd * (Cells(Rows.Count, 1).End(xlUp).Row  1) + 2, 1)
End Sub
Hi all,
I have a workbook that has a list of numbers in column A. Can anyone please assist with the VBA to do the following:
1. Bring up a message box asking how many random cells should be selected.
2. Select the requested number of random cells.
3. Copy to column A on sheet 2.
It would be beneficial if a cell could only be selected once i.e. to avoid the random selection of duplicates,
Thanks in advance for any help!
I have a workbook that has a list of numbers in column A. Can anyone please assist with the VBA to do the following:
1. Bring up a message box asking how many random cells should be selected.
2. Select the requested number of random cells.
3. Copy to column A on sheet 2.
It would be beneficial if a cell could only be selected once i.e. to avoid the random selection of duplicates,
Thanks in advance for any help!
I have the numbers 1  55 that I would like to display in a column randomly
without repeating. The numbers should be whole numbers only. Can you help
me? I tried following some of the other posts, but I couldn't get it.
Thanks.
without repeating. The numbers should be whole numbers only. Can you help
me? I tried following some of the other posts, but I couldn't get it.
Thanks.
Hello,
Can an expert in this forum devise of a VBA macro that creates 500 random numbers in cells A1:A500?
The numbers must all be odd and contain 3 digits in each cell... i.e.
135
179
977
etc...
many thanks!
andy
Can an expert in this forum devise of a VBA macro that creates 500 random numbers in cells A1:A500?
The numbers must all be odd and contain 3 digits in each cell... i.e.
135
179
977
etc...
many thanks!
andy
Hey guys I am new to the forum and excel Macros, so go easy on me! Here is my problem:
I need to generate 10 random numbers, lets call that x that if summed/added will equal a set value of 100,000 lets call that y. I also need the generated random numbers to be distributed between 5,000 to 20,000 lets say the lower value of 5,000 is min and the upper value is max. I them need these values placed in a set number of cells, for example A1 to A10 . What would a macro that did something like this look like. Any help would be very appreciated!
I need to generate 10 random numbers, lets call that x that if summed/added will equal a set value of 100,000 lets call that y. I also need the generated random numbers to be distributed between 5,000 to 20,000 lets say the lower value of 5,000 is min and the upper value is max. I them need these values placed in a set number of cells, for example A1 to A10 . What would a macro that did something like this look like. Any help would be very appreciated!
Hi,
I need to randomly generate a set of 5000 numbers using just the following three inputs:
>Min
>Mean
>Max
They have to be normally distributed, but as the mean is never exactly half way between the min and max, the distribution will be skewed.
I am using =(NORMSINV(RAND())*0.13)+0.5 to give me 5000 random numbers that are normally distributed with a mean of 0.5 and (almost always) have a min of 0 and a max of 1.
I am then mapping these to the distribution that I require using
If random number < 0.5, f(x)=min+2*rand*(meanmin)
If random number = 0.5, f(x)=mean
If random number > 0.5, f(x)=mean+2*(rand0.5)*(maxmean)
As expected this is giving me numbers within the predefined bounds, however the mean is way off what I was expecting.
For example, a min of 50,000, mean of 225,000 and a max of 1,000,000 give the following output:
>Min ~ 50,00060,000 (this is close enough)
>Mean ~ 288,000290,000 (this is no where near)
>Max ~ 950,0001,000,000 (this is close enough)
I am not sure if there is a problem with my mapping formula, if it is to do with how I am generating the random numbers in the first place, or something else, but I am now stuck.
If anyone has any experience of this, or can offer any advice, then I would be most grateful!
Many thanks in advance,
Graham
I need to randomly generate a set of 5000 numbers using just the following three inputs:
>Min
>Mean
>Max
They have to be normally distributed, but as the mean is never exactly half way between the min and max, the distribution will be skewed.
I am using =(NORMSINV(RAND())*0.13)+0.5 to give me 5000 random numbers that are normally distributed with a mean of 0.5 and (almost always) have a min of 0 and a max of 1.
I am then mapping these to the distribution that I require using
If random number < 0.5, f(x)=min+2*rand*(meanmin)
If random number = 0.5, f(x)=mean
If random number > 0.5, f(x)=mean+2*(rand0.5)*(maxmean)
As expected this is giving me numbers within the predefined bounds, however the mean is way off what I was expecting.
For example, a min of 50,000, mean of 225,000 and a max of 1,000,000 give the following output:
>Min ~ 50,00060,000 (this is close enough)
>Mean ~ 288,000290,000 (this is no where near)
>Max ~ 950,0001,000,000 (this is close enough)
I am not sure if there is a problem with my mapping formula, if it is to do with how I am generating the random numbers in the first place, or something else, but I am now stuck.
If anyone has any experience of this, or can offer any advice, then I would be most grateful!
Many thanks in advance,
Graham
I'm setting up a spreadsheet that tracks names and generates random,
nonrepeating fourdigit ID#'s. Is it possible in Excel 2000?
nonrepeating fourdigit ID#'s. Is it possible in Excel 2000?
What is the best way to produce a random non repeating serial numbers in access. I was told using autonumber is not the way to go, and besides it produces negative numbers.
Hello everyone,
I have a list of numbers in A1:A50. I would like to have something that would select 10% of those numbers randomly avoiding duplicates
thanks for all your help
I have a list of numbers in A1:A50. I would like to have something that would select 10% of those numbers randomly avoiding duplicates
thanks for all your help
Hi,
Is there a (nonVBA) way to generate a given number of unique random numbers from a list of unique numbers?
Eg. Column A has an unspecified number of unique positive integers in it.
I want to be able to enter an integer in B1 (that is obviously less than the number of elements in column A) and have that many unique random elements from Column A listed in column C.
So for example, A1:A10 might have the numbers 1 .. 10.
The user enters the number 3 (say) in B1 and then 3 unique random numbers from column A are listed from C1:C3.
Thanks in advance.
Is there a (nonVBA) way to generate a given number of unique random numbers from a list of unique numbers?
Eg. Column A has an unspecified number of unique positive integers in it.
I want to be able to enter an integer in B1 (that is obviously less than the number of elements in column A) and have that many unique random elements from Column A listed in column C.
So for example, A1:A10 might have the numbers 1 .. 10.
The user enters the number 3 (say) in B1 and then 3 unique random numbers from column A are listed from C1:C3.
Thanks in advance.
I need to know the formula to create a column of random 8 character ID
numbers, preferably with a set prefix of 3 numbers. I'd be REALLY happy if
I could create it with random text and numerals, but it doesn't have to be.
(where 123 is the constant)
i.e.:
12386756
12337288
12378645
12321356
or
123g568e
1237t7er
12397u23
Thanks in advance for your help.
Kenny
numbers, preferably with a set prefix of 3 numbers. I'd be REALLY happy if
I could create it with random text and numerals, but it doesn't have to be.
(where 123 is the constant)
i.e.:
12386756
12337288
12378645
12321356
or
123g568e
1237t7er
12397u23
Thanks in advance for your help.
Kenny
Hi,
I've tried to create my own bingo game sheet but am not getting the expected result. Hope you can help me with this small dilemma.
I got the random number part down but am having problem making it unique.
B  numbers 115
I  numbers 1630
N  numbers 3145
G  numbers 4660
O  numbers 6175
The "B" column will generate random numbers from 1 through 15 but very often that number is not unique in that column. Could you help me with the solution?
Thanks a bunch!
I've tried to create my own bingo game sheet but am not getting the expected result. Hope you can help me with this small dilemma.
I got the random number part down but am having problem making it unique.
B  numbers 115
I  numbers 1630
N  numbers 3145
G  numbers 4660
O  numbers 6175
The "B" column will generate random numbers from 1 through 15 but very often that number is not unique in that column. Could you help me with the solution?
Thanks a bunch!
Hello,
I'm still learning Excel and I would like help on how to automatically generate four digit numbers without repeating the same number in any four number combination.
Eg. The list would include: 1234, 4321, but not include 1123, 2244.
There must be an easy way to do this in Excel.
Could anyone help me please?
Thank you very much!
I'm still learning Excel and I would like help on how to automatically generate four digit numbers without repeating the same number in any four number combination.
Eg. The list would include: 1234, 4321, but not include 1123, 2244.
There must be an easy way to do this in Excel.
Could anyone help me please?
Thank you very much!