Is it possible to mix up a list of numbers

  • Thread starter Thread starter Jamie
  • Start date Start date
J

Jamie

Hi There

If I had a list of numbers in cells A1:A10 that read:
1
2
3
4
5
6
7
8
9
10

Is there any way I can create a macro or do something so
that the numbers are mixed up randomely but each time the
macro was run it would mix them up differently?

Any help would be greatfully appreciated.

Thanks in Advance

Jamie
 
Use a help column and use

=RAND()

then sort on the help column

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Another approach ..

With your list in A1:A10
Put in B1: =RAND(), copy down to B10
Name the range B1:B10 as : TBL

Select C1:C10

Put in the formula bar: =RANK(TBL,TBL)
Hold down Ctrl + Shift keys, press Enter
(It's an array formula)

Done correctly, Excel will wrap curly braces around
the formula, viz: {=RANK(TBL,TBL)}
This formula will appear in each cell in C1:C10

In C1:C10 will be a random shuffle of all the list elements,
i.e. in this instance, the numbers 1 - 10 without repeats

Each press of F9 will re-generate another random shuffle within C1:C10

(Hold down F9 for a CPU-sapping "kaleidoscopic" twirl <g>)

To freeze the random shuffle in C1:C10
just do a copy > paste special > values elsewhere
 
In C1:C10 will be a random shuffle of all the list elements,
i.e. in this instance, the numbers 1 - 10 without repeats
Click to expand...

Clarification:
If the list in A1:A10 comprises other than the numbers 1-10
you need another col to extract the corresponding list elements ..

Put in D1: =OFFSET($A$1,C1-1,)
Copy D1 down to D10

In D1:D10 will be a random shuffle
of the list elements in col A without repeats

Each press of F9 will re-generate another
random shuffle of col A within D1:D10
 
Max wrote...
...
Clarification:
If the list in A1:A10 comprises other than the numbers 1-10 you
need another col to extract the corresponding list elements ..

Put in D1: =OFFSET($A$1,C1-1,)
Copy D1 down to D10

In D1:D10 will be a random shuffle
of the list elements in col A without repeats
Click to expand...
...

And this is superior to Peo's proposed solution how? With an arbitrar
list of N entries, Peo's solution requires just the additional
adjacent cells containing =RAND(), and once the 2-col range is sorte
in either order on the col of random numbers, it's fixed using th
original set of numbers until the next sort.

You solution requires 4*N rather than just N extra cells if the O
wants to freeze shuffled values so they don't change on every recalc
and Edit > Copy, Edit > Paste Special as Values requires two rang
selection operations rather than just one for sorting.

So 4 times as many additional cells and more work to freeze them. Why'
you bother?

And as for the silliness with OFFSET, you don't need both cols C and
to shuffle original arbitrary values in col A. You could select th
whole col C range (C1:C10 given original specs) and enter

=INDEX(A1:A10,RANK(B1:B10,B1:B10))

as an array formula. Greater simplicity is a goal toward which yo
should strive
 
A little off topic. I notice that in Excel, the Rank technique is about
twice as fast as sorting when the size is relatively small (say <100).
However, there appears to be no timing advantage when the size gets larger
than this. (from timing point of view). In contrast, the "rank" technique
is very effective in math programs like Mathematica. With very large 2
dimension arrays, and one wishing to sort on a specific column, this is a
much faster technique. Custom programs often use this technique. However,
it is called "Ordering" instead of Rank, because the term "Rank" is
associated with the Rank of a Matrix.

Dana
 
Dana DeLouis wrote...
A little off topic. I notice that in Excel, the Rank technique is
about twice as fast as sorting when the size is relatively small
(say <100). However, there appears to be no timing advantage
when the size gets larger than this. (from timing point of view).
Click to expand...

To the extent that sorting involves juggling cell contents while usin
a multiple cell array formula calling RANK only involves returnin
different values, this isn't surprising.

This leaves the question whether the unavoidable additional manua
steps needed to freeze the RANK results would offset or swamp it
recalc-only timing advantage.
In contrast, the "rank" technique is very effective in math
programs like Mathematica. With very large 2 dimension arrays,
and one wishing to sort on a specific column, this is a much
faster technique. . . .
Click to expand...

Do you mean that using an ordering function to return an array o
indices that could be used to dereference the original array in sorte
order is faster than sorting the array? If so, shouldn't you includ
the time involved to recreate the entire original array in sorte
order
 
hgrove > said:
And this is superior to Peo's proposed solution how?
Click to expand...
... hmm, this was neither said nor imputed at any time, no? <bg>

....
Greater simplicity is a goal toward which you
should strive..
Click to expand...

Agreed .. And thanks for the lecture
and the masterful insights, Harlan !
 
Hi Harlan. A long time ago I experimented in Excel with Rank on a large
array. Of course, I had no luck. The idea came from Mma...

Here's a (500,000 x 5) matrix of random numbers.

v = Table[Random[], {500000}, {5}];

Sort is hard-wired to use the first element of each Row, so it’s fairly
fast.

Sort[v]
1.493 Second

To sort a 2-dim array on something else, you supply Sort with a custom
function, which slows it down. Here’s a sort on the 3rd column.

Sort[v,#1[[3]]<#2[[3]]&]
84.221 Second

One technique is to use Ordering, similar in concept to Excel’s Rank.

v[[Ordering[v[[All,3]]]]]
9.567 Second

Anyway, I was curious to use Excel vba and Rank at the time, and wanted to
experiment. I know there are other issues involved as well. Just doesn't
work the same in Excel thought...obviously. :>)
Dana
 
Back
Top