Randomly Rearrange Records

[Fred]

I have a spread sheet where the first row contains column
headings- these headings are not to move!

Following the column headings are approx. 25,000 records.
I want (need) to scramble the records. The first 199
records are repeated over and over. This is fine, but I
need to have some randomness to the whole thing. I would
like, as an example, to move record 134 to the position
occupied by 13,234, or move record 20,000 to position 11.

This is something that would be run once.

Can this be done in Excel 2000?

 

[Frank Kabel]

Hi
one way:
- add a helper column
- enter the formula
=RAND()
in this column for all rows (to copy just double-click on the lower
right corner of the first formula cell)
- now use this column for sorting
- delete the helper column

 

[Tom Ogilvy]

If you want to randomize all the records, then

in an adjacent column starting in row 2, put in the formula

=rand()

then fill down. Now sort all the data on that column using Data=>Sort.
Specify that the data has Headers.

if you just want to swap to random records, then post back.

 

[Jan Karel Pieterse]

Hi Fred,

Following the column headings are approx. 25,000 records.
I want (need) to scramble the records. The first 199
records are repeated over and over. This is fine, but I
need to have some randomness to the whole thing. I would
like, as an example, to move record 134 to the position
occupied by 13,234, or move record 20,000 to position 11.

This is something that would be run once.

Can this be done in Excel 2000?

Add another column with this function:
=RAND()

Now sort the entire table on the new column.
Repeat to get another order.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Fred]

Thank you very much- what you offered did what I needed!