Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
inkap
 
Posts: n/a
Default how do i shuffle the numbers in a table?

Does anyone know how to shuffle the numbers in a table until the user stops?
  #2   Report Post  
Max
 
Posts: n/a
Default

"inkap" wrote:
Does anyone know how to
shuffle the numbers in a table until the user stops?


Here's one play using non-array formulas ..

In Sheet1
---------
List the numbers in the table (let's take say, 12 numbers)
down in A1:A12 (in any order)

Put in B1: =RAND()
Put in C1: =INDEX(A:A,RANK(B1,$B$1:$B$12))
Select B1:C1, copy down to C12

Now, in a new Sheet2
-----------------
Let's say the table set-up is to be a 4R x 3C in A1:C4

Put in A1:
=INDEX(Sheet2!$C:$C,
ROWS($A$1:A1)*3-3+COLUMNS($A$1:A1))

Copy across to C1, fill down to C4

A1:C4 will return a random shuffle of all
the 12 numbers listed in Sheet1's A1:A12

Now we could just press F9 to generate a fresh shuffle. Or, if we hold down
F9, it'll appear as a dazzling continuous shuffle until we release F9. This
yields the desired control / visual of:
shuffle the numbers in a table until the user stops?


If the table set-up is to be a 3R x 4C in A1:D3,
just amend the formula in A1 to:

=INDEX(Sheet2!$C:$C,
ROWS($A$1:A1)*4-4+COLUMNS($A$1:A1))

Then copy A1 across to D1, fill down to D3

And for a table set-up of 6R x 2C in A1:B6,
amend the formula in A1 to:

=INDEX(Sheet2!$C:$C,
ROWS($A$1:A1)*2-2+COLUMNS($A$1:A1))

Copy A1 across to B1, fill down to B6

And so on, .. for the other possible
table configs from 12 elements
(leave it to you to enjoy tinkering with these set-ups <g!)

Adapt to suit ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--


  #3   Report Post  
Max
 
Posts: n/a
Default

Here's a link to a demo file to play with:
http://savefile.com/files/6238428

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to get pivot table Time field to appear correctly wccmgr Excel Worksheet Functions 1 August 23rd 05 12:26 AM
Pivot Table Naz Excel Discussion (Misc queries) 3 July 18th 05 12:23 AM
Pivot -- want to use Max and Sum in same table Dave Excel Worksheet Functions 1 July 13th 05 04:37 AM
Change Data In Pivot Table John Calder New Users to Excel 1 July 7th 05 10:41 PM
Removing errors from a Pivot table Mighty Magpie Excel Discussion (Misc queries) 2 February 3rd 05 03:15 PM


All times are GMT +1. The time now is 02:23 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"