Random List Generation

[Guest]

I have a list of 500 dates in ColA. Is there an easy way to generate a random
list of 25 dates.

Thank you in advance.

 

[daddylonglegs]

You could put this formula in B1

=RAND() copied down to B500.

Then sort columns A and B by column B and use the first 25

 

[Guest]

In B1 thru B500 enter:

=rand()

Then sort cols A&B by B.
Then select the first 25 items in column A

 

[Pete_UK]

If you want your original dates untouched, copy column A into a new
worksheet. Enter this formula in B1 of the new worksheet:

=RAND()

and copy down. Then sort Columns A and B using B as the sort field.
Then just take the top 25 (or bottom 25, or 25 consecutive cells in the
middle somewhere) dates from this list.

Hope this helps.

Pete

 

[Guest]

Perhaps something like this:

For a list of dates in A1:A500

C1: =INDEX($A$1:$A$500,INT(RAND()*500))
Copy C1 down through C25
format those cells as dates

That will choose 25 random dates from the range A1:A500

To check if you have any duplicate dates listed....
D1: =SUMPRODUCT((C1:C25<>"")/COUNTIF(C1:C25,C1:C25&""))
If that formula returns anything less than 25...there are duplicates..recalc.

Note: the RAND function is volatile and generates new numbers every time the
workbook recalculates. Consequently, the list of dates will also keep
changing.

If you would like to deal with a static set of dates, create a Pivot Table
based on the random date list. Since Pivot Tables create an internal cache of
the source data, the Pivot Table list will not change even if the random date
list keeps changing. Anytime you want to use a new list of random dates,
just refresh the Pivot Table data

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP