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!

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