Randomly sorting a group of 5 values multiple times
"OssieMac" wrote:
Add the following code to the bottom of the macro to provide a unique set
using AdvancedFilter.
Need to set the initial NumberOfRows high enough to provide a good
probability of obtaining a unique set. 1000 worked well.
Range("A4") = "Col1"
Range("B4") = "Col2"
Range("C4") = "Col3"
Range("D4") = "Col4"
Range("E4") = "Col5"
'The following code was a recorded macro for simplicity.
Range("A4").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"H1:L1"), Unique:=True
"OssieMac" wrote:
Insert the variables to randomise in the first row like this:-
A B C D E
and then this macro will create random rows with the variables but no
guarantee that some rows will not be the same. Is that important?
Sub Random_List()
Dim NumberOfRows As Integer 'Number of rows required
Dim RndNumber As Integer 'Holds random number
Dim NumberOfElements 'Number of variables eg A,B,C,D,E etc to be sorted
Dim Rng1 As Range 'Range of variables eg A,B,C,D,E etc
Dim Rng2 As Range 'Range of variables and row of random numbers
NumberOfRows = 100 'Set to number of rows required
NumberOfElements = 5 'Set to number of elelments
With Worksheets("RandomList")
Set Rng1 = Range(Cells(1, 1), Cells(1, NumberOfElements))
Rng1.Select
Set Rng2 = Range(Cells(1, 1), Cells(2, NumberOfElements))
For i = 5 To NumberOfRows 'Start on row 5
For j = 1 To NumberOfElements
'Generate random value between 1 and NumberOfElements
.Cells(2, j) = Int((NumberOfElements * Rnd) + 1)
Next j
Rng2.Sort Key1:=Cells(2, 1), Orientation:=xlSortRows
Range(Cells(i, 1), Cells(i, NumberOfElements)).Select
Rng1.Copy Destination:=Range(Cells(i, 1), Cells(i,
NumberOfElements))
Next i
End With
End Sub
"Skylara" wrote:
Thanks Joel:
I was hoping to find a less labor intensive solution. I would like to let
the computer do the work rather than type 120 different combinations. I had
high expectations of Excel.
--
Skylar
"Joel" wrote:
the Process is simple. the work is hard.
I would manually type on one worksheet every combination of 5 letters to use
as a table. the will be 120 rows. 5 factorial. 5 x 4 x 3 x 2 x 1
Sheet1
row 1 : A B C D E
row 2 : A B C E D
row 120 : F E C B A
the all you need to do is generate a random number from 0 to 119 to use as
an offset to a cell.
a random number from 1 to 119 is = rounddown(120 * rand())
On a second worksheet I would put the random number in column A
Sheet2
In Column A row 1
=rounddown(120*rand())
Sheet2
In column B row 1
=offset('sheet1'!A$1,$A1,0,1,1)
In column C row 1
=offset('sheet1'!B$1,$A1,0,1,1)
In column D row 1
=offset('sheet1'!C$1,$A1,0,1,1)
In column E row 1
=offset('sheet1'!D$1,$A1,0,1,1)
In column F row 1
=offset('sheet1'!E$1,$A1,0,1,1)
The you can copy Sheet 2 Row 1 to as many rows as you need.
DONE!!!!!!!!!!
now you have
"Skylara" wrote:
Hi:
I would like to generate multiple rows of the following variables without
repeating any variable in a particular row. For example: a,c,e,d,b (first
row); b,d,c,e,a (second row); etc. The concept is simple, however it seems
impossible in practice.
Thank you.
--
Skylar
|