Randomly sorting a group of 5 values multiple times
 

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

Randomly sorting a group of 5 values multiple times
 

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

Randomly sorting a group of 5 values multiple times
 

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

Randomly sorting a group of 5 values multiple times
 

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 RndArray(1 To 5)
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)
'MsgBox ("In J loop. j = " & i)
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))
'MsgBox ("In I loop. i = " & j)
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

Randomly sorting a group of 5 values multiple times
 

"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)
'MsgBox ("In J loop. j = " & i)
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))
'MsgBox ("In I loop. i = " & j)
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

Randomly sorting a group of 5 values multiple times
 

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"
'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

Randomly sorting a group of 5 values multiple times
 

Here is an adaptation of code found a John Walkenbach's site:

Dim CurrentRow

Sub GetString()
Dim InString As String
InString = "abcde"
ActiveSheet.Columns(1).Clear
CurrentRow = 1
Call GetPermutation("", InString)

End Sub

Sub GetPermutation(x As String, y As String)
' The source of this algorithm is unknown
Dim i As Integer, j As Integer
j = Len(y)
If j < 2 Then
'Cells(CurrentRow, 1) = x & y
For i = 1 To 5
Cells(CurrentRow, i) = Mid(x & y, i, 1)
Next
CurrentRow = CurrentRow + 1
Else
For i = 1 To j
Call GetPermutation(x + Mid(y, i, 1), _
Left(y, i - 1) + Right(y, j - i))
Next
End If
End Sub

-----------------------
http://www.j-walk.com/ss/excel/tips/tip46.htm
----------------------

--
Regards,
Tom Ogilvy

"Skylara" wrote in message
...
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

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

Randomly sorting a group of 5 values multiple times
 

You have to use good judgement when programming. It is easier in some cases
to generate tables than to generate long formula that will take hours to
write and debug. I considered lastt night writingg a routine to generate
unique permintations ( I have done it in the past). for 120 row, it is
easier to just generate the table by hand. I did it this morningg and it
took only 10 minutes. If I needed 6! or more, then I would of writen a
program. thanks tom for finding a program. The table is really the best way
of generating the random list that you are looking for.

"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

Randomly sorting a group of 5 values multiple times
 

Hi Tom,

You still need to delete all rows which repeat any value in a column,
I think.

Regards,
Bernd

Randomly sorting a group of 5 values multiple times
 

I didn't read that into the request. The word "column" didn't appear in the
original post. But maybe he wants a 5 x 5 where each letter appears once
in each column and each row.

--
Regards,
Tom Ogilvy


"Bernd" wrote in message
oups.com...
Hi Tom,

You still need to delete all rows which repeat any value in a column,
I think.

Regards,
Bernd

Randomly sorting a group of 5 values multiple times
 

Hi Tom,

Your are right.

Regards,
Bernd