Random Number Generator - Multiple, Non-repeating, results
 

Is there a way to produce multiple, non-repeating, 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

Answer: Random Number Generator - Multiple, Non-repeating, results
 

Generating Non-Repeating Random Integers in Excel

1. In a blank column, enter the formula =RAND() in the first cell.

1. Copy the formula down to the number of cells you want to generate random numbers for.

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.

1. Copy the formula down to the number of cells you want to generate random numbers for.

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.

1. Copy the formula down to the number of cells you want to generate random numbers for.

You should now have a list of non-repeating random numbers within the range you specified.

Random Number Generator - Multiple, Non-repeating, 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()*(20-ROW()+ROW($B$2)))+1)

Hope this helps!

In article ,
A Long <A wrote:

Is there a way to produce multiple, non-repeating, 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

Random Number Generator - Multiple, Non-repeating, 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()*(20-ROW()+ROW($B$2)))+1)

Hope this helps!

In article ,
A Long <A wrote:

Is there a way to produce multiple, non-repeating, 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

Random Number Generator - Multiple, Non-repeating, 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()*(20-ROW()+ROW($B$2)))+1)

Hope this helps!

In article ,
A Long <A wrote:

Is there a way to produce multiple, non-repeating, 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

Random Number Generator - Multiple, Non-repeating, results
 

One way:

http://mcgimpsey.com/excel/udfs/randint.html

In article ,
A Long <A wrote:

Is there a way to produce multiple, non-repeating, 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

Random Number Generator - Multiple, Non-repeating, 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

Random Number Generator - Multiple, Non-repeating, 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()*(20-ROW()+ROW($B$2)))+1)

Hope this helps!

In article ,
A Long <A wrote:

Is there a way to produce multiple, non-repeating, 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

Random Number Generator - Multiple, Non-repeating, 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()*(20-ROW()+ROW($B$2)))+1)

Hope this helps!

In article ,
A Long <A wrote:

Is there a way to produce multiple, non-repeating, 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

Random Number Generator - Multiple, Non-repeating, 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

Random Number Generator - Multiple, Non-repeating, 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?

Random Number Generator - Multiple, Non-repeating, 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

Random Number Generator - Multiple, Non-repeating, results
 

I got it to work for the range 1-20, but am not sure what to change in the
formula to make the range 10-19. 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

Random Number Generator - Multiple, Non-repeating, 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()*(10-ROW()+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 1-20, but am not sure what to change in the
formula to make the range 10-19. Sorry to be a bother and a dummy.

Thanks
A Long

Random Number Generator - Multiple, Non-repeating, 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()*(10-ROW()+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 1-20, but am not sure what to change in the
formula to make the range 10-19. Sorry to be a bother and a dummy.

Thanks
A Long

Random Number Generator - Multiple, Non-repeating, 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()*(10-ROW()+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()*(10-ROW()+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 1-20, but am not sure what to change in
the
formula to make the range 10-19. Sorry to be a bother and a dummy.

Thanks
A Long

Random Number Generator - Multiple, Non-repeating, results
 

In this part of the formula...

INT(RAND()*(10-ROW()+ROW($B$2)))+1

....you probably have a line break...

INT(R
AND()*(10-ROW()+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

Random Number Generator - Multiple, Non-repeating, 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

Random Number Generator - Multiple, Non-repeating, 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.

Random Number Generator - Multiple, Non-repeating, results
 

Agreed. This special case is a substitution of RANDBETWEEN. Regards,
Bernd

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