Home |
Search |
Today's Posts |
|
#1
![]() |
|||
|
|||
![]()
Does anyone know how to shuffle the numbers in a table until the user stops?
|
#2
![]() |
|||
|
|||
![]()
"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
![]() |
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How to get pivot table Time field to appear correctly | Excel Worksheet Functions | |||
Pivot Table | Excel Discussion (Misc queries) | |||
Pivot -- want to use Max and Sum in same table | Excel Worksheet Functions | |||
Change Data In Pivot Table | New Users to Excel | |||
Removing errors from a Pivot table | Excel Discussion (Misc queries) |