Randomly sort a collection/array

[DrBowes]

Would anybody know a slick way to sort a collection or array into a random order?

The only method I can think of at the moment is to put the values into a worksheet, assign random numbers to the next column, sort and reload into the collection/array. Yuk

 

[dyarwood]

Surely sorting into a random order is a contradiction in terms.

However, you could create a new array with the same dimensions then use random index selections to fill that array.

 

[dyarwood]

Have a look at the randomize function.

 

[CajunCenturion]

I would think that it would be better to simply randomize the entry retrieval order rather than to actually try to sort the data set.

Use the Randomize function to see the Random number generator, then use the Rnd function to obtain a random value. If you're using an array, then use the LBound(Array) and UBound(Array) functions to put the value in range, and for a collection use 1 and Collection.Count to set the range. Use that random number then as the index of the next value to retrieve. If you can only choose each entry once, then a collection works very well because you can remove the item from the original collection and add it to another collection once selected, and you don't have to keep another data structure somewhere to keep track of what you've already selected. You second collection then still has all of the values.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein