Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 18th 07, 08:20 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 10
Default Save Random Number

I would like to generate a row of 5000 - unique random numbers and not have
them change once generated.

Any ideas would be appreciated.

Cheers

  #2   Report Post  
Old October 18th 07, 08:50 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 2,059
Default Save Random Number

On Oct 18, 12:20 pm, Paul B. wrote:
I would like to generate a row of 5000 - unique random numbers
and not have them change once generated.


Irritating, isn't it?

One way: Create a UDF that calls the VBA function Rnd().

Another way: After generating the column using =RAND(), copy the
column, then use Paste Special -- Values to replace the column.

  #3   Report Post  
Old October 18th 07, 08:54 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 2,059
Default Save Random Number

[reposting]

On Oct 18, 12:20 pm, Paul B. wrote:
I would like to generate a row of 5000 - unique random
numbers and not have them change once generated.


Irritating, isn't it?

One way: Create a UDF that calls the VBA function Rnd(),

Another way: After generating the column using =RAND(), copy the
column, then use Paste Special -- Values to replace the column with
the generated values.

  #4   Report Post  
Old October 18th 07, 08:58 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 418
Default Save Random Number

[reposting]

On Oct 18, 12:20 pm, Paul B. wrote:
I would like to generate a row of 5000 - unique random
numbers and not have them change once generated.


Irritating, isn't it?

One way: Create a UDF that calls the VBA function Rnd(),

Another way: After generating the column using =RAND(), copy the column,
then use Paste Special -- Values to replace the column with the generated
values.

  #5   Report Post  
Old October 18th 07, 09:18 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 418
Default Save Random Number

Errata....

I wrote:
One way: Create a UDF that calls the VBA function Rnd().


But Excel might still recalculate those calls when it feels like it, namely
(I think) when it deletes a worksheet and when it saves or loads (I don't
recall which) the workbook.

Of course, you could always turn off automatic recalculation. But the calls
will be recalculated when you eventually manually recalculate the worksheet.

Another way: After generating the column using =RAND(), copy the
column, then use Paste Special -- Values to replace the column.


This is the only way I know of to ensure that the random numbers are never
replaced. If you want the option to regenerate the column of random numbers,
Paste Special Values into another column and be sure that your formulas refer
to the "value only" column. You can even create a macro to do the Paste
Special Values, and have a button to run the macro. The column with =RAND()
can be hidden.

Gotta run! HTH.



  #6   Report Post  
Old April 3rd 15, 03:50 AM
Junior Member
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1
Default

Quote:
Originally Posted by Paul B. View Post
I would like to generate a row of 5000 - unique random numbers and not have
them change once generated.

Any ideas would be appreciated.

Cheers
Copy the randomly generated numbers and then paste values over the same space. Past values is under the paste sub-menu when you right click a cell.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Addding a Random number to a fixed number..... Dermot Excel Discussion (Misc queries) 6 August 20th 06 12:17 PM
How can I match a random number with closest number from sequence? Matt Excel Worksheet Functions 4 August 3rd 06 01:22 AM
same number appears in a random number generator Carmel Excel Worksheet Functions 4 May 28th 06 12:22 AM
Generating (in a random order)each number once from a given number Neil Goldwasser Excel Worksheet Functions 2 December 2nd 05 11:27 PM
How do I find random number in list of random alpha? (Position is. jlahealth-partners Excel Discussion (Misc queries) 0 February 8th 05 05:31 PM


All times are GMT +1. The time now is 12:14 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017