"Random sort" a selection?

[Ed]

I'd like to select a range of cells down a single column, and resort the
values in a random order, but within the same cells. Is that easy enough to
do?

Ed

 

[Pete_UK]

I suppose you could insert a random number in the column next to your
data and then sort both columns of data based on the random column,
then delete the column of random numbers.

Hope this helps.

Pete

 

[Opinicus]

I suppose you could insert a random number in the column next to your
data and then sort both columns of data based on the random column,
then delete the column of random numbers.

The trouble with that is that you'd still need a way to generate a
randomized arrangement of all the integers from 1 to n, where n is the
number of items in the set being randomized. For example, suppose I have ten
items that I want to put in random order. I can't simply generate 10 random
numbers in the range 0-9 because of the likelihood of duplications. (With
even as few as 10 items, the probability of NO duplications in my 10 random
numbers is only 10!/(10^10) and the probability of one or more duplications
is 1-(10!/(10^10)) or 99.94%

So how *does* one randomize the order of a selection in Excel?

 

[Pete_UK]

If you want, say, 10 random numbers, they could cover any range (eg 1 -
1000, or .0001 to .9999) which is bigger than the number that you want,
and they don't have to be integers. The larger the range, the less
chance there is of duplications. Anyway, if there are duplicates, then
so what? You still get a random distribution (well, as random as the
Excel random generator produces!)

Pete

 

[Dave Peterson]

And if you use =rand(), you'll see a number between 0 and 1.

And with excel giving 15 digits in those numbers, the probability of a duplicate
would be pretty small.

 

[Guest]

Jim Cone has a fine commercial Add-in that does that easily, amoung many
other tpes of sorts that Excel doesn't normally do...........it's called
SpecialSort and it's available at

http://www.realezsites.com/bus/primitivesoftware/products.php

Vaya con Dios,
Chuck, CABGx3

 

[Opinicus]

And if you use =rand(), you'll see a number between 0 and 1.
And with excel giving 15 digits in those numbers, the probability of a
duplicate
would be pretty small.

Thanks for pointing that out. (It's obvious when I think about it.) I tried
it and it works perfectly.