If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Haw to Randomized available data
I have 10 columns of data. I wanted to randomized each column so that I will
get a new combination of data. Its like permutation but actually applying it to the data. is there any function I can use for this? |
#2
|
|||
|
|||
"Adda" wrote in message ... I have 10 columns of data. I wanted to randomized each column so that I will get a new combination of data. Its like permutation but actually applying it to the data. is there any function I can use for this? Assuming your data is in colums A to K, do this 1) Select column B and then select Insert Columns 3) Keep on inserting colums so that every other column is empty 4) Insert =RAND() in the empty columns and dragfill downwards At this point, you should turn on the recorder if you want to do this random sort every now and then 5) Select columns A and B 6) Select Data|Sort. Sort on column B 7) Repeat until you have sorted the last column on the column with the RAND function 8) Hide the colums with the rand formula 9) Turn off the recorder I think you could get away with two colums with the RAND function. It apppears as if Data|Sort has a limit on how many columns it can sort. You would have to do the sort several times and change the selected columns each time. Either way, you have to sort 10 times. /Fredrik |
#3
|
|||
|
|||
An often used procedure for something like this is to add a column
containing the RAND() function, and then sort the columns, using the returns from the RAND() function as the sort keys. With 10 columns however, this procedure would not "mix up" the relationships between the rows within these columns. This may or may not be acceptable. In an adjoining column, or an inserted column, enter, =RAND() and copy down as needed. Now, simply select all the columns and: Data Sort And choose the Rand column as the "Sort By". Another approach, where sorting the original data is *not* necessary, is to use a formula which will *display* the original data in a random order, matching, once again, an out-of-the-way column containing the Rand() function. Here however, you could in reality, create 10 columns containing the Rand() function, and then have 10 columns of formulas, each accessing a different Rand column, therefore displaying a truly "mixed up", randomized display of the original 10 columns of data. A new random display would be produced with each hit of the F9 (calculate) key. With original data in A1:J100 In AA1, enter =RAND(), And drag across and then down to create 10 columns, AA1:AJ100 Then, let"s say we enter this formula in L1: =INDEX(A$1:A$100,RANK(AA1,AA$1:AA$100)) Drag across to copy to U1, Then drag L1:U1 down to copy to Row100. You now have L1 to U100 displaying a random order of your original data, where you can now hit F9, and get a new random display. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Adda" wrote in message ... I have 10 columns of data. I wanted to randomized each column so that I will get a new combination of data. Its like permutation but actually applying it to the data. is there any function I can use for this? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do I get 3 series in sync with the x-axis? | zizbird | Charts and Charting | 10 | October 25th, 2004 01:23 PM |
Data Source issues. ??data.access.pages | Phil | Database Design | 2 | October 11th, 2004 02:42 AM |
Data Source issues | Philippe | Database Design | 1 | October 10th, 2004 09:45 PM |
Export data from OLE object in PowerPoint | Brett Ellingson | Powerpoint | 8 | August 25th, 2004 12:28 AM |
Mial merge data base problems | Rachael | Mailmerge | 16 | May 21st, 2004 06:22 PM |