Remember Me?

Posted to microsoft.public.excel.worksheet.functions
 A Long external usenet poster Posts: 1 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

 ExcelBanter AI Excel Super Guru Posts: 1,867 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.
__________________
I am not human. I am an Excel Wizard
Posted to microsoft.public.excel.worksheet.functions
 Domenic external usenet poster Posts: 150 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

Posted to microsoft.public.excel.worksheet.functions
 T. Valko external usenet poster Posts: 15,768 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
such as 4,7,18)

I need help! Thanks

Posted to microsoft.public.excel.worksheet.functions
 T. Valko external usenet poster Posts: 15,768 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
such as 4,7,18)

I need help! Thanks

Posted to microsoft.public.excel.worksheet.functions
 JE McGimpsey external usenet poster Posts: 4,624 Random Number Generator - Multiple, Non-repeating, results

Posted to microsoft.public.excel.worksheet.functions
 Bernd external usenet poster Posts: 92 Random Number Generator - Multiple, Non-repeating, results

Hello,

Can't you just delete
"nCount = 1 Then
RandInt = CLng((nEnd - nStart) * Rnd() + nStart)
ElseIf"

http://www.sulprobil.com/html/uniqrandint.html

Regards,
Bernd

Posted to microsoft.public.excel.worksheet.functions
 vezerid external usenet poster Posts: 751 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

Posted to microsoft.public.excel.worksheet.functions
 A Long[_2_] external usenet poster Posts: 5 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

Posted to microsoft.public.excel.worksheet.functions
 Domenic external usenet poster Posts: 150 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

Posted to microsoft.public.excel.worksheet.functions
 JE McGimpsey external usenet poster Posts: 4,624 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"

Posted to microsoft.public.excel.worksheet.functions
 A Long[_2_] external usenet poster Posts: 5 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

Posted to microsoft.public.excel.worksheet.functions
 A Long[_2_] external usenet poster Posts: 5 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

Posted to microsoft.public.excel.worksheet.functions
 Domenic external usenet poster Posts: 150 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

Posted to microsoft.public.excel.worksheet.functions
 A Long[_2_] external usenet poster Posts: 5 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

Posted to microsoft.public.excel.worksheet.functions
 JE McGimpsey external usenet poster Posts: 4,624 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

Posted to microsoft.public.excel.worksheet.functions
 Domenic external usenet poster Posts: 150 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

Posted to microsoft.public.excel.worksheet.functions
 Bernd external usenet poster Posts: 92 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

Posted to microsoft.public.excel.worksheet.functions
 JE McGimpsey external usenet poster Posts: 4,624 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.
Posted to microsoft.public.excel.worksheet.functions
 Bernd external usenet poster Posts: 92 Random Number Generator - Multiple, Non-repeating, results

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

 banatello Junior Member Posts: 1 Great formula, how can I increase it up to 100 random numbers, using 1 to 100.

Quote:
 Originally Posted by Domenic 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
 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post deelee Excel Worksheet Functions 3 October 12th 06 07:46 PM Neil M Excel Worksheet Functions 2 February 1st 06 06:00 PM STEVEB Excel Worksheet Functions 7 July 25th 05 11:41 PM Pascale Excel Worksheet Functions 3 July 9th 05 12:37 AM Jose Luis Excel Discussion (Misc queries) 1 June 14th 05 09:41 PM

All times are GMT +1. The time now is 08:49 AM. Copyright ©2004-2023 ExcelBanter.