I am looking to "shuffle a deck" of 100 numbers in a list and only have
this list reshuffle when I hit a button (not everytime a re-calculation
is done within the worksheet). So far I have been able to get the
following:

Column A1:A100 = the list of values to be sorted
Cell B1 =INDEX(A:A,RANK(C1,$C$1:$C$100))
Cell C1 =RAND()

Cells B1 and C1 are then cut and pasted down to cells B100 and C100
respectively

This does the job, but everytime excel recalculates, the shuffle
changes. How do I avoid this?

Seth


--
Sethnstorm
------------------------------------------------------------------------
Sethnstorm's Profile: http://www.excelforum.com/member.php...o&userid=22571
View this thread: http://www.excelforum.com/showthread...hreadid=523826

Seth:

I'd use a Pivot Table for this.

Here's how:

A1: MyList
A2:A101 (your list of numbers)

B1: SortSeq
B2: =RAND()
Copy that formula down through B101

Select A1:B101
Data|Pivot Table
Use: Excel......Click the [Next] Button
Range: (already selected)......Click the [Next] Button
Click the [Layout] button

Drag the SortSeq field to the ROW section
Dbl-click that field
Set subtotals to: None
Click the [Advanced] button
Set Autosort to: Ascending
Click [OK] twice

Drag the MyList field to the ROW section (under SortSeq)
Dbl-click that field and set subtotals to: None

DATA: Drag the MyList field here
Dbl-click that field and set the function to COUNT

Click the [OK] button
Select the location of the Pivot Table
Click the [Finish] button

No matter how many times the workbook recalculates, the pivot table will
only be altered when you right click on it and select Refresh.

That's because Pivot Tables work with an internal COPY of the source data
and not the actual source data.

Is that something you can work with?

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

XL2002, WinXP-Pro


"Sethnstorm" wrote:


I am looking to "shuffle a deck" of 100 numbers in a list and only have
this list reshuffle when I hit a button (not everytime a re-calculation
is done within the worksheet). So far I have been able to get the
following:

Column A1:A100 = the list of values to be sorted
Cell B1 =INDEX(A:A,RANK(C1,$C$1:$C$100))
Cell C1 =RAND()

Cells B1 and C1 are then cut and pasted down to cells B100 and C100
respectively

This does the job, but everytime excel recalculates, the shuffle
changes. How do I avoid this?

Seth


--
Sethnstorm
------------------------------------------------------------------------
Sethnstorm's Profile: http://www.excelforum.com/member.php...o&userid=22571
View this thread: http://www.excelforum.com/showthread...hreadid=523826

Hello:

Get rid of column B. Just the 100 numbers in A1:A100 and in B1:B100 put
the =rand() formula.

Now just sort A1:B100 on column B. Each time you want a new order just
sort again.

For a more automated featu

You can record a macro to this if you wish. Just turn on the macro recorder
and go through the steps of selecting the area and then select Sort and
column B.

You can put a button on the spreadsheet from forms toolbar and assign the
macro to it. Then you just click.


Pieter Vandenberg

Sethnstorm wrote:

: I am looking to "shuffle a deck" of 100 numbers in a list and only have
: this list reshuffle when I hit a button (not everytime a re-calculation
: is done within the worksheet). So far I have been able to get the
: following:

: Column A1:A100 = the list of values to be sorted
: Cell B1 =INDEX(A:A,RANK(C1,$C$1:$C$100))
: Cell C1 =RAND()

: Cells B1 and C1 are then cut and pasted down to cells B100 and C100
: respectively

: This does the job, but everytime excel recalculates, the shuffle
: changes. How do I avoid this?

: Seth


: --
: Sethnstorm
: ------------------------------------------------------------------------
: Sethnstorm's Profile: http://www.excelforum.com/member.php...o&userid=22571
: View this thread: http://www.excelforum.com/showthread...hreadid=523826