Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Randomization of Multiple Columns and Sequences
Sorry if this posted before...I did not fill in the Subject Line, so I
don't think my original went through. On with the show---- I created a simple spreadsheet with 10 sets of 3 columns, each separated by a blank column, so 39 columns in all. The cells contain either a single letter or number in a specific sequence, so each row will contain 10 specific sequences of the alphanumeric figures, i.e. the 3 cells "go together." There are over 40 rows of this on the spreadsheet. For example: 4 8 5 3 8 8 etc., with the blank column separating the 5 and 3 cells in this example. 10 of these "clusters" run across each row. Right now the spreadsheet is carefully set up in a type of numeric order. What I want to do is be able to randomize or mix up all the clusters on the sheet. So that a cluster in say, the very top left hand corner of the sheet might now show up somewhere in the middle. All clusters must retain their 3-digit internal order. I would like to be able to hit a key or something to make the sheet mix itself up randomly. Each time I do that, I would like the result to be different from the previous randomization, so I don't keep getting the same randomization starting from the original "in-order" spreadsheet. Any help would be appreciated...If this is possible to do without VB, that would be great as well. Thanks in advance! *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Randomization of Multiple Columns and Sequences
Try this against a copy of your data.
Option Explicit Sub testme() Dim myArray() As Variant Dim totalGroups As Long Dim myNums() As Long Dim iRow As Long Dim iCol As Long Dim iCtr As Long Dim jCtr As Long Dim tempVal As Long Dim wks As Worksheet Dim StartRow As Long Dim StartCol As Long Dim NumRows As Long Dim numCols As Long 'modify these to where your data starts/finishes StartRow = 5 StartCol = 3 numCols = 10 NumRows = 40 totalGroups = NumRows * numCols ReDim myArray(1 To totalGroups) ReDim myNums(1 To totalGroups) Set wks = ActiveSheet With wks iCtr = 0 For iRow = StartRow To StartRow + NumRows - 1 For iCol = StartCol To StartCol + (4 * numCols) - 1 Step 4 iCtr = iCtr + 1 myArray(iCtr) = .Cells(iRow, iCol).Resize(, 3).Value Next iCol Next iRow For iCtr = 1 To totalGroups myNums(iCtr) = iCtr Next iCtr Randomize For iCtr = totalGroups To 1 Step -1 jCtr = Int(Rnd() * totalGroups) + 1 tempVal = myNums(iCtr) myNums(iCtr) = myNums(jCtr) myNums(jCtr) = tempVal Next iCtr iCtr = 0 For iRow = StartRow To StartRow + NumRows - 1 For iCol = StartCol To StartCol + (numCols * 4) - 1 Step 4 iCtr = iCtr + 1 .Cells(iRow, iCol).Resize(1, 3).Value _ = myArray(myNums(iCtr)) Next iCol Next iRow End With End Sub I I picked up the data starting in A1 Sandy Pasdak wrote: Sorry if this posted before...I did not fill in the Subject Line, so I don't think my original went through. On with the show---- I created a simple spreadsheet with 10 sets of 3 columns, each separated by a blank column, so 39 columns in all. The cells contain either a single letter or number in a specific sequence, so each row will contain 10 specific sequences of the alphanumeric figures, i.e. the 3 cells "go together." There are over 40 rows of this on the spreadsheet. For example: 4 8 5 3 8 8 etc., with the blank column separating the 5 and 3 cells in this example. 10 of these "clusters" run across each row. Right now the spreadsheet is carefully set up in a type of numeric order. What I want to do is be able to randomize or mix up all the clusters on the sheet. So that a cluster in say, the very top left hand corner of the sheet might now show up somewhere in the middle. All clusters must retain their 3-digit internal order. I would like to be able to hit a key or something to make the sheet mix itself up randomly. Each time I do that, I would like the result to be different from the previous randomization, so I don't keep getting the same randomization starting from the original "in-order" spreadsheet. Any help would be appreciated...If this is possible to do without VB, that would be great as well. Thanks in advance! *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If Statement with 9 sequences | Excel Discussion (Misc queries) | |||
If Statement with 9 sequences | Excel Discussion (Misc queries) | |||
Excel - randomization with certain criteria | Excel Worksheet Functions | |||
Data Randomization | Excel Worksheet Functions | |||
multiple hits in random sequences | Excel Worksheet Functions |