Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Randomly sorting a group of 5 values multiple times
Hi Tom,
Your are right. Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Randomly add numbers in a group | Excel Worksheet Functions | |||
Randomly sort a set group of integers | Excel Worksheet Functions | |||
How do I group multiple lines of excel info for easy sorting | New Users to Excel | |||
sorting multiple minimum times | Excel Discussion (Misc queries) | |||
How do I shuffle a group of cells randomly in Excel? | Excel Discussion (Misc queries) |