Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.worksheet.functions




Random Number Generator  Multiple, Nonrepeating, results
Is there a way to produce multiple, nonrepeating, intergers within a given
range?For example, generate 3 whole numbers between 1 and 20 (with an answer such as 4,7,18) I need help! Thanks 
#2




Answer: Random Number Generator  Multiple, Nonrepeating, results
Generating NonRepeating Random Integers in Excel
1. In a blank column, enter the formula =RAND() in the first cell.
2. Select the range of cells with the random numbers and click on the Data tab in the ribbon. 3. Click on the Sort button and select Smallest to Largest. 4. In a separate column, enter the formula =RANK(cell reference, range of cells with random numbers) in the first cell. Replace "cell reference" with the first cell in the range of random numbers and "range of cells with random numbers" with the entire range of random numbers.
5. Select the range of cells with the RANK function and click on the Data tab in the ribbon. 6. Click on the Sort button and select Smallest to Largest. 7. In a separate column, enter the formula =cell reference + (minimum value  1) in the first cell. Replace "cell reference" with the first cell in the range of RANK values and "minimum value" with the lowest number in the range you want to generate random numbers for.
You should now have a list of nonrepeating random numbers within the range you specified.
__________________
I am not human. I am an Excel Wizard 
#3
Posted to microsoft.public.excel.worksheet.functions




Random Number Generator  Multiple, Nonrepeating, results
Try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER... B2, copied down to B4: =SMALL(IF(ISNA(MATCH(ROW($B$2:$B$21)ROW($B$2)+1,$B$1:B1,0)),ROW($B$2:$B$ 21)ROW($B$2)+1),INT(RAND()*(20ROW()+ROW($B$2)))+1) Hope this helps! In article , A Long <A wrote: Is there a way to produce multiple, nonrepeating, intergers within a given range?For example, generate 3 whole numbers between 1 and 20 (with an answer such as 4,7,18) I need help! Thanks 
#4
Posted to microsoft.public.excel.worksheet.functions




Random Number Generator  Multiple, Nonrepeating, results
Why are you using this:
ROW($B$2:$B$21)ROW($B$2)+1 Why not just: ROW($1:$20) Biff "Domenic" wrote in message ... Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... B2, copied down to B4: =SMALL(IF(ISNA(MATCH(ROW($B$2:$B$21)ROW($B$2)+1,$B$1:B1,0)),ROW($B$2:$B$ 21)ROW($B$2)+1),INT(RAND()*(20ROW()+ROW($B$2)))+1) Hope this helps! In article , A Long <A wrote: Is there a way to produce multiple, nonrepeating, intergers within a given range?For example, generate 3 whole numbers between 1 and 20 (with an answer such as 4,7,18) I need help! Thanks 
#5
Posted to microsoft.public.excel.worksheet.functions




Random Number Generator  Multiple, Nonrepeating, results
Nevermind!
Row insertions! Biff "T. Valko" wrote in message ... Why are you using this: ROW($B$2:$B$21)ROW($B$2)+1 Why not just: ROW($1:$20) Biff "Domenic" wrote in message ... Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... B2, copied down to B4: =SMALL(IF(ISNA(MATCH(ROW($B$2:$B$21)ROW($B$2)+1,$B$1:B1,0)),ROW($B$2:$B$ 21)ROW($B$2)+1),INT(RAND()*(20ROW()+ROW($B$2)))+1) Hope this helps! In article , A Long <A wrote: Is there a way to produce multiple, nonrepeating, intergers within a given range?For example, generate 3 whole numbers between 1 and 20 (with an answer such as 4,7,18) I need help! Thanks 
#6
Posted to microsoft.public.excel.worksheet.functions




Random Number Generator  Multiple, Nonrepeating, results
One way:
http://mcgimpsey.com/excel/udfs/randint.html In article , A Long <A wrote: Is there a way to produce multiple, nonrepeating, intergers within a given range?For example, generate 3 whole numbers between 1 and 20 (with an answer such as 4,7,18) I need help! Thanks 
#7
Posted to microsoft.public.excel.worksheet.functions




Random Number Generator  Multiple, Nonrepeating, results
Hello,
Can't you just delete "nCount = 1 Then RandInt = CLng((nEnd  nStart) * Rnd() + nStart) ElseIf" in your randint UDF? http://www.sulprobil.com/html/uniqrandint.html Regards, Bernd 
#8
Posted to microsoft.public.excel.worksheet.functions




Random Number Generator  Multiple, Nonrepeating, results
Domenic,
this is a very nice formula! Regards, Kostis On Mar 29, 4:20 am, Domenic wrote: Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... B2, copied down to B4: =SMALL(IF(ISNA(MATCH(ROW($B$2:$B$21)ROW($B$2)+1,$B$1:B1,0)),ROW($B$2:$B$ 21)ROW($B$2)+1),INT(RAND()*(20ROW()+ROW($B$2)))+1) Hope this helps! In article , A Long <A wrote: Is there a way to produce multiple, nonrepeating, intergers within a given range?For example, generate 3 whole numbers between 1 and 20 (with an answer such as 4,7,18) I need help! Thanks 
#9
Posted to microsoft.public.excel.worksheet.functions




Random Number Generator  Multiple, Nonrepeating, results
Domenic,
I don't know what I did wrong. I entered the formula in B2 and attempted to copy it through B4, but I only got #NUM! in each cell. Any thoughts on what I did wrong? A Long "Domenic" wrote: Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... B2, copied down to B4: =SMALL(IF(ISNA(MATCH(ROW($B$2:$B$21)ROW($B$2)+1,$B$1:B1,0)),ROW($B$2:$B$ 21)ROW($B$2)+1),INT(RAND()*(20ROW()+ROW($B$2)))+1) Hope this helps! In article , A Long <A wrote: Is there a way to produce multiple, nonrepeating, intergers within a given range?For example, generate 3 whole numbers between 1 and 20 (with an answer such as 4,7,18) I need help! Thanks 
#10
Posted to microsoft.public.excel.worksheet.functions




Random Number Generator  Multiple, Nonrepeating, results
The formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just
ENTER. Excel will automatically place braces {...} around the formula, indicating that you've entered it correctly. Hope this helps! In article , A Long wrote: Domenic, I don't know what I did wrong. I entered the formula in B2 and attempted to copy it through B4, but I only got #NUM! in each cell. Any thoughts on what I did wrong? A Long 
#11
Posted to microsoft.public.excel.worksheet.functions




Random Number Generator  Multiple, Nonrepeating, results
Could, but it's much faster to calculate one number than to go through
the rest of the routine if the UDF is called from a single cell. Won't be even the blink of an eye if there's only one call during a calc cycle, but can add up if there are lots of multiple calls and multiple iterations, and it adds only a few bytes to the code. In article .com, "Bernd" wrote: Can't you just delete "nCount = 1 Then RandInt = CLng((nEnd  nStart) * Rnd() + nStart) ElseIf" in your randint UDF? 
#12
Posted to microsoft.public.excel.worksheet.functions




Random Number Generator  Multiple, Nonrepeating, results
It works! Not sure how, but it works! I'll study it to try to understand it.
Thank you very much A Long "Domenic" wrote: The formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Excel will automatically place braces {...} around the formula, indicating that you've entered it correctly. Hope this helps! In article , A Long wrote: Domenic, I don't know what I did wrong. I entered the formula in B2 and attempted to copy it through B4, but I only got #NUM! in each cell. Any thoughts on what I did wrong? A Long 
#13
Posted to microsoft.public.excel.worksheet.functions




Random Number Generator  Multiple, Nonrepeating, results
I got it to work for the range 120, but am not sure what to change in the
formula to make the range 1019. Sorry to be a bother and a dummy. Thanks A Long "A Long" wrote: It works! Not sure how, but it works! I'll study it to try to understand it. Thank you very much A Long "Domenic" wrote: The formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Excel will automatically place braces {...} around the formula, indicating that you've entered it correctly. Hope this helps! In article , A Long wrote: Domenic, I don't know what I did wrong. I entered the formula in B2 and attempted to copy it through B4, but I only got #NUM! in each cell. Any thoughts on what I did wrong? A Long 
#14
Posted to microsoft.public.excel.worksheet.functions




Random Number Generator  Multiple, Nonrepeating, results
Try...
B2, copied down: =SMALL(IF(ISNA(MATCH(ROW($B$10:$B$19),$B$1:B1,0)), ROW($B$10:$B$19)),INT(R AND()*(10ROW()+ROW($B$2)))+1) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , A Long wrote: I got it to work for the range 120, but am not sure what to change in the formula to make the range 1019. Sorry to be a bother and a dummy. Thanks A Long 
#15
Posted to microsoft.public.excel.worksheet.functions




Random Number Generator  Multiple, Nonrepeating, results
I have entered the formula and checked it multiple times. However, every
time I get a message that the formula has an error. The curser moves to the AND() every time. Should there be logical conditions within the "( )"? Thanks A Long "Domenic" wrote: Try... B2, copied down: =SMALL(IF(ISNA(MATCH(ROW($B$10:$B$19),$B$1:B1,0)), ROW($B$10:$B$19)),INT(R AND()*(10ROW()+ROW($B$2)))+1) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , A Long wrote: I got it to work for the range 120, but am not sure what to change in the formula to make the range 1019. Sorry to be a bother and a dummy. Thanks A Long 
#16
Posted to microsoft.public.excel.worksheet.functions




Random Number Generator  Multiple, Nonrepeating, results
Domenic's formula linewrapped poorly. Try:
=SMALL(IF(ISNA(MATCH(ROW($B$10:$B$19),$B$1:B1,0)), ROW($B$10:$B$19)), INT(RAND()*(10ROW()+ROW($B$2)))+1) In article , A Long wrote: I have entered the formula and checked it multiple times. However, every time I get a message that the formula has an error. The curser moves to the AND() every time. Should there be logical conditions within the "( )"? Thanks A Long "Domenic" wrote: Try... B2, copied down: =SMALL(IF(ISNA(MATCH(ROW($B$10:$B$19),$B$1:B1,0)), ROW($B$10:$B$19)),INT(R AND()*(10ROW()+ROW($B$2)))+1) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , A Long wrote: I got it to work for the range 120, but am not sure what to change in the formula to make the range 1019. Sorry to be a bother and a dummy. Thanks A Long 
#17
Posted to microsoft.public.excel.worksheet.functions




Random Number Generator  Multiple, Nonrepeating, results
In this part of the formula...
INT(RAND()*(10ROW()+ROW($B$2)))+1 ....you probably have a line break... INT(R AND()*(10ROW()+ROW($B$2)))+1 Remove it and I think you should be okay. Hope this helps! In article , A Long wrote: I have entered the formula and checked it multiple times. However, every time I get a message that the formula has an error. The curser moves to the AND() every time. Should there be logical conditions within the "( )"? Thanks A Long 
#18
Posted to microsoft.public.excel.worksheet.functions




Random Number Generator  Multiple, Nonrepeating, results
Hello again,
.... Won't be even the blink of an eye if there's only one call during a calc cycle, but can add up if there are lots of multiple calls and multiple iterations, and it adds only a few bytes to the code. .... Hmm, if it comes to speed, IMHO VBA should not be used in this "pathological" case anyway. A simple worksheet formula would do... Regards, Bernd 
#19
Posted to microsoft.public.excel.worksheet.functions




Random Number Generator  Multiple, Nonrepeating, results
In article . com,
"Bernd" wrote: Hmm, if it comes to speed, IMHO VBA should not be used in this "pathological" case anyway. A simple worksheet formula would do... Sure. You could say that about most VBA UDFs, though  far more efficient to implement in XL. The UDF overhead is a killer. But since the branch for a single cell doesn't add more than a nanosecond or two, and a dozen or so bytes, my thought was to allow for the pathological. Won't argue it's right or wrong, just the style I chose. 
#20
Posted to microsoft.public.excel.worksheet.functions




Random Number Generator  Multiple, Nonrepeating, results
Agreed. This special case is a substitution of RANDBETWEEN. Regards,
Bernd 
#21




Great formula, how can I increase it up to 100 random numbers, using 1 to 100.
Quote:

Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Random number generator  Excel Worksheet Functions  
Random Number generator  Excel Worksheet Functions  
Random Number Generator  Excel Worksheet Functions  
Random Number Generator  Excel Worksheet Functions  
random number generator  Excel Discussion (Misc queries) 