|
Random Number
Is there a way to create a 40 by 40 sheet where each column and each row have
no duplicate numbers. And have this randomly generated? |
Random Number
Sure. Create a 40 X 40 matrix of numbers 1 through 1600 and then reference
that matrix with the RAND function; i.e. =RAND()*A1, etc. Dave -- Brevity is the soul of wit. "Jon" wrote: Is there a way to create a 40 by 40 sheet where each column and each row have no duplicate numbers. And have this randomly generated? |
Random Number
Try this. If you want the highest number bigger or lower then change the 10000
Sub fillrandom() For y = 1 To 40 For x = 1 To 40 a = Int((100000 * Rnd) + 1) Worksheets("sheet1").Cells(y, x).Value = a Next Next End Sub Mike "Jon" wrote: Is there a way to create a 40 by 40 sheet where each column and each row have no duplicate numbers. And have this randomly generated? |
Random Number
integer values ?
fractional values ? -- Gary''s Student gsnu200702 "Jon" wrote: Is there a way to create a 40 by 40 sheet where each column and each row have no duplicate numbers. And have this randomly generated? |
Random Number
I just tried this and spotted two instances of 1112....
I don't think this will generate unique random numbers over 1600 cells. At least not every time the macro is run. Dave -- Brevity is the soul of wit. "Mike" wrote: Try this. If you want the highest number bigger or lower then change the 10000 Sub fillrandom() For y = 1 To 40 For x = 1 To 40 a = Int((100000 * Rnd) + 1) Worksheets("sheet1").Cells(y, x).Value = a Next Next End Sub Mike "Jon" wrote: Is there a way to create a 40 by 40 sheet where each column and each row have no duplicate numbers. And have this randomly generated? |
Random Number
Hello, Dave!
You wrote on Wed, 24 Jan 2007 09:21:02 -0800: DF I don't think this will generate unique random numbers over DF 1600 cells. At least not every time the macro is run. DF Dave DF -- DF Brevity is the soul of wit. I suppose that you could argue, pedantically perhaps, that a set of unique integers is not random :-) However, unique integers are wanted and there are a surprising number of hits on Google for:- unique integers involving some non-trivial programming. James Silverton Potomac, Maryland E-mail, with obvious alterations: not.jim.silverton.at.comcast.not |
Random Number
Maybe you could use J.E. McGimpsey's =RandInt() User Defined Function:
http://www.mcgimpsey.com/excel/udfs/randint.html If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Jon wrote: Is there a way to create a 40 by 40 sheet where each column and each row have no duplicate numbers. And have this randomly generated? -- Dave Peterson |
Random Number
You can create a random list of 1600 numbers by sorting by an adjacent
column of random numbers, then link these values to a 40x40 matrix on another sheet as follows: On Sheet1 enter: A B 1 =RAND() 2 =RAND() 3 =RAND() .... 1600 =RAND() On Sheet2 enter: =Sheet1!A1 =Sheet1!A41 =Sheet1!A41 =Sheet1!A2 =Sheet1!A42 =Sheet1!A42 =Sheet1!A3 =Sheet1!A43 =Sheet1!A43 Format cells as text before entering the formulas and use the fill handle to increment across the sheet, then choose Edit Replace "=" with "=". On Jan 24, 4:51 pm, Jon wrote: Is there a way to create a 40 by 40 sheet where each column and each row have no duplicate numbers. And have this randomly generated? |
Random Number
Hello, Dave!
You wrote on Wed, 24 Jan 2007 11:45:36 -0600: DP If you're new to macros, you may want to read David DP McRitchie's intro at: DP http://www.mvps.org/dmcritchie/excel/getstarted.htm DP Jon wrote: ?? ?? Is there a way to create a 40 by 40 sheet where each ?? column and each row have no duplicate numbers. And have ?? this randomly generated? Yes, as you say, it looks like J.E. McGimpsey's =RandInt() User Defined Function: http://www.mcgimpsey.com/excel/udfs/randint.html will work for a column of unique integers . You would then have to reorganize the result into the matrix or previously equate each cell in the matrix to a column value. It seems that something might be done with index if that is not re-inventing the wheel! James Silverton Potomac, Maryland E-mail, with obvious alterations: not.jim.silverton.at.comcast.not |
Random Number
JE's function will work with multiple columns and multiple rows. I use it all
the time to create test data. James Silverton wrote: Hello, Dave! You wrote on Wed, 24 Jan 2007 11:45:36 -0600: DP If you're new to macros, you may want to read David DP McRitchie's intro at: DP http://www.mvps.org/dmcritchie/excel/getstarted.htm DP Jon wrote: ?? ?? Is there a way to create a 40 by 40 sheet where each ?? column and each row have no duplicate numbers. And have ?? this randomly generated? Yes, as you say, it looks like J.E. McGimpsey's =RandInt() User Defined Function: http://www.mcgimpsey.com/excel/udfs/randint.html will work for a column of unique integers . You would then have to reorganize the result into the matrix or previously equate each cell in the matrix to a column value. It seems that something might be done with index if that is not re-inventing the wheel! James Silverton Potomac, Maryland E-mail, with obvious alterations: not.jim.silverton.at.comcast.not -- Dave Peterson |
Random Number
I might of been unclear on what Iwas tring to accomplish. 40x40 sheet, with
whole numbers 1-40, only appearing once in each row and column, but randomly generated. "Dave F" wrote: Sure. Create a 40 X 40 matrix of numbers 1 through 1600 and then reference that matrix with the RAND function; i.e. =RAND()*A1, etc. Dave -- Brevity is the soul of wit. "Jon" wrote: Is there a way to create a 40 by 40 sheet where each column and each row have no duplicate numbers. And have this randomly generated? |
Random Number
I might of been unclear on what Iwas tring to accomplish. 40x40 sheet, with
whole numbers 1-40, only appearing once in each row and column, but randomly generated. Thanks "Lori" wrote: You can create a random list of 1600 numbers by sorting by an adjacent column of random numbers, then link these values to a 40x40 matrix on another sheet as follows: On Sheet1 enter: A B 1 =RAND() 2 =RAND() 3 =RAND() .... 1600 =RAND() On Sheet2 enter: =Sheet1!A1 =Sheet1!A41 =Sheet1!A41 =Sheet1!A2 =Sheet1!A42 =Sheet1!A42 =Sheet1!A3 =Sheet1!A43 =Sheet1!A43 Format cells as text before entering the formulas and use the fill handle to increment across the sheet, then choose Edit Replace "=" with "=". On Jan 24, 4:51 pm, Jon wrote: Is there a way to create a 40 by 40 sheet where each column and each row have no duplicate numbers. And have this randomly generated? |
Random Number
Is there a way to create a 40 by 40 sheet where each column and each
row have no duplicate numbers. And have this randomly generated? Here's one way. In cell A1 of Sheet1, put =RAND() and extend right and down 40 by 40. In cell A1 of Sheet2, put =RANK(Sheet1!A1,Sheet1!A:A) and extend right and down 40 by 40. Hit the F9 key to get a new set of values. |
Random Number
On Wed, 24 Jan 2007 23:19:17 -0000, MyVeryOwnSelf
wrote: Is there a way to create a 40 by 40 sheet where each column and each row have no duplicate numbers. And have this randomly generated? Here's one way. In cell A1 of Sheet1, put =RAND() and extend right and down 40 by 40. In cell A1 of Sheet2, put =RANK(Sheet1!A1,Sheet1!A:A) and extend right and down 40 by 40. Hit the F9 key to get a new set of values. There is is a bit in the original question that says *NO DUPLICATES* The question is whether that original poster wants the numbers 1 through 1600 arranged randomly in a 40 x 40 grid or the numbers 1 to 40 arranged in each row such that each column has the numbers 1 to 40 ie if it was 3x3 1 2 3 . . . 1 2 3 4 5 6 . . . 2 3 1 7 8 9 . . . 3 1 2 using the numbers 1 to 9 or 1 to 3 in eachrow/column -- Steve (3) |
Random Number
Hello, Dave!
You wrote on Wed, 24 Jan 2007 13:33:34 -0600: DP JE's function will work with multiple columns and multiple rows. I use it all the time to create test data. : ?? Yes, as you say, it looks like J.E. McGimpsey's =RandInt() ?? User Defined ?? Function: http://www.mcgimpsey.com/excel/udfs/randint.html will ?? work for a column of unique integers . You would then have ?? to reorganize the result into the matrix or previously ?? equate each cell in the matrix to a column value. It seems ?? that something might be done with index if that is not ?? re-inventing the wheel! Thanks! I missed that! James Silverton Potomac, Maryland E-mail, with obvious alterations: not.jim.silverton.at.comcast.not |
Random Number
On Thu, 25 Jan 2007 01:44:19 -0000, James Silverton
wrote: Hello, Dave! You wrote on Wed, 24 Jan 2007 13:33:34 -0600: DP JE's function will work with multiple columns and multiple rows. I use it all the time to create test data. : ?? Yes, as you say, it looks like J.E. McGimpsey's =RandInt() ?? User Defined ?? Function: http://www.mcgimpsey.com/excel/udfs/randint.html will Error 404 - do you have a different url ? ?? work for a column of unique integers . You would then have ?? to reorganize the result into the matrix or previously ?? equate each cell in the matrix to a column value. It seems ?? that something might be done with index if that is not ?? re-inventing the wheel! Thanks! I missed that! James Silverton Potomac, Maryland E-mail, with obvious alterations: not.jim.silverton.at.comcast.not |
Random Number
It worked ok for me.
Did you click on it or copy|paste into your browser's address box? SteveW wrote: On Thu, 25 Jan 2007 01:44:19 -0000, James Silverton wrote: Hello, Dave! You wrote on Wed, 24 Jan 2007 13:33:34 -0600: DP JE's function will work with multiple columns and multiple rows. I use it all the time to create test data. : ?? Yes, as you say, it looks like J.E. McGimpsey's =RandInt() ?? User Defined ?? Function: http://www.mcgimpsey.com/excel/udfs/randint.html will Error 404 - do you have a different url ? ?? work for a column of unique integers . You would then have ?? to reorganize the result into the matrix or previously ?? equate each cell in the matrix to a column value. It seems ?? that something might be done with index if that is not ?? re-inventing the wheel! Thanks! I missed that! James Silverton Potomac, Maryland E-mail, with obvious alterations: not.jim.silverton.at.comcast.not -- Dave Peterson |
Random Number
I am only looking for the numbers 1 through 40, not 1 to 1600.
Each row and column should contain no duplicates. numbers should be in random order. "SteveW" wrote: On Wed, 24 Jan 2007 23:19:17 -0000, MyVeryOwnSelf wrote: Is there a way to create a 40 by 40 sheet where each column and each row have no duplicate numbers. And have this randomly generated? Here's one way. In cell A1 of Sheet1, put =RAND() and extend right and down 40 by 40. In cell A1 of Sheet2, put =RANK(Sheet1!A1,Sheet1!A:A) and extend right and down 40 by 40. Hit the F9 key to get a new set of values. There is is a bit in the original question that says *NO DUPLICATES* The question is whether that original poster wants the numbers 1 through 1600 arranged randomly in a 40 x 40 grid or the numbers 1 to 40 arranged in each row such that each column has the numbers 1 to 40 ie if it was 3x3 1 2 3 . . . 1 2 3 4 5 6 . . . 2 3 1 7 8 9 . . . 3 1 2 using the numbers 1 to 9 or 1 to 3 in eachrow/column -- Steve (3) |
Random Number
That's odd !
The link in the post had .htmlwill at the end - which I didn't notice but is obviously why it failed. When I replied it has changed to .html will But I didn't add the space - must be something to do with my reader and it's handling of previous posts etc Anyway - works now - thanks Steve On Thu, 25 Jan 2007 01:58:18 -0000, Dave Peterson wrote: It worked ok for me. Did you click on it or copy|paste into your browser's address box? SteveW wrote: On Thu, 25 Jan 2007 01:44:19 -0000, James Silverton wrote: Hello, Dave! You wrote on Wed, 24 Jan 2007 13:33:34 -0600: DP JE's function will work with multiple columns and multiple rows. I use it all the time to create test data. : ?? Yes, as you say, it looks like J.E. McGimpsey's =RandInt() ?? User Defined ?? Function: http://www.mcgimpsey.com/excel/udfs/randint.html will Error 404 - do you have a different url ? ?? work for a column of unique integers . You would then have ?? to reorganize the result into the matrix or previously ?? equate each cell in the matrix to a column value. It seems ?? that something might be done with index if that is not ?? re-inventing the wheel! Thanks! I missed that! James Silverton Potomac, Maryland E-mail, with obvious alterations: not.jim.silverton.at.comcast.not |
Random Number
Right in that case the rows
will have to be 1,2,3,4,...40 2,3,4,5...40,1 3,4,5...40,1,2 .... 40,1,2,3...39 So that's 40 rows - the order of which can be altered Add a helper column (41) = rand() sort on the 41st column Then delete it. Steve On Thu, 25 Jan 2007 02:00:00 -0000, Jon wrote: I am only looking for the numbers 1 through 40, not 1 to 1600. Each row and column should contain no duplicates. numbers should be in random order. "SteveW" wrote: On Wed, 24 Jan 2007 23:19:17 -0000, MyVeryOwnSelf wrote: Is there a way to create a 40 by 40 sheet where each column and each row have no duplicate numbers. And have this randomly generated? Here's one way. In cell A1 of Sheet1, put =RAND() and extend right and down 40 by 40. In cell A1 of Sheet2, put =RANK(Sheet1!A1,Sheet1!A:A) and extend right and down 40 by 40. Hit the F9 key to get a new set of values. There is is a bit in the original question that says *NO DUPLICATES* The question is whether that original poster wants the numbers 1 through 1600 arranged randomly in a 40 x 40 grid or the numbers 1 to 40 arranged in each row such that each column has the numbers 1 to 40 ie if it was 3x3 1 2 3 . . . 1 2 3 4 5 6 . . . 2 3 1 7 8 9 . . . 3 1 2 using the numbers 1 to 9 or 1 to 3 in eachrow/column -- Steve (3) |
Random Number
Excellent idea.
And the OP could add =rand() to a new row 1 and sort by that. Select A1:AN41 (avoid the column 41 with =rand() in it) Then Data|Sort|Options button|Sort left to right. (and when doing the sort by the 41st column, don't include that top helper row.) SteveW wrote: Right in that case the rows will have to be 1,2,3,4,...40 2,3,4,5...40,1 3,4,5...40,1,2 ... 40,1,2,3...39 So that's 40 rows - the order of which can be altered Add a helper column (41) = rand() sort on the 41st column Then delete it. Steve On Thu, 25 Jan 2007 02:00:00 -0000, Jon wrote: I am only looking for the numbers 1 through 40, not 1 to 1600. Each row and column should contain no duplicates. numbers should be in random order. "SteveW" wrote: On Wed, 24 Jan 2007 23:19:17 -0000, MyVeryOwnSelf wrote: Is there a way to create a 40 by 40 sheet where each column and each row have no duplicate numbers. And have this randomly generated? Here's one way. In cell A1 of Sheet1, put =RAND() and extend right and down 40 by 40. In cell A1 of Sheet2, put =RANK(Sheet1!A1,Sheet1!A:A) and extend right and down 40 by 40. Hit the F9 key to get a new set of values. There is is a bit in the original question that says *NO DUPLICATES* The question is whether that original poster wants the numbers 1 through 1600 arranged randomly in a 40 x 40 grid or the numbers 1 to 40 arranged in each row such that each column has the numbers 1 to 40 ie if it was 3x3 1 2 3 . . . 1 2 3 4 5 6 . . . 2 3 1 7 8 9 . . . 3 1 2 using the numbers 1 to 9 or 1 to 3 in eachrow/column -- Steve (3) -- Dave Peterson |
Random Number
And in code, it would look kind of like:
Option Explicit Sub testme01() Dim wks As Worksheet Dim HowMany As Long HowMany = 40 Set wks = Workbooks.Add(1).Worksheets(1) With wks With .Range("A1").Offset(1, 0).Resize(HowMany, HowMany) .Formula = "=MOD(ROW()-1+COLUMN()-1-1," & HowMany & ")+1" End With .Range("a1").Resize(1, HowMany).Formula = "=rand()" .Range("a1").Offset(1, HowMany).Resize(HowMany, 1).Formula = "=rand()" With .UsedRange .Value = .Value End With With Range("A1").Offset(1, 0).Resize(HowMany, HowMany + 1) .Sort key1:=.Columns(HowMany + 1), Order1:=xlAscending, _ Header:=xlNo, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End With With .Range("a1").Resize(HowMany + 1, HowMany) .Sort key1:=.Rows(1), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight End With .Rows(1).Delete .Columns(HowMany + 1).Delete .UsedRange.Columns.AutoFit End With End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm (Nice idea again, Steve!) Dave Peterson wrote: Excellent idea. And the OP could add =rand() to a new row 1 and sort by that. Select A1:AN41 (avoid the column 41 with =rand() in it) Then Data|Sort|Options button|Sort left to right. (and when doing the sort by the 41st column, don't include that top helper row.) SteveW wrote: Right in that case the rows will have to be 1,2,3,4,...40 2,3,4,5...40,1 3,4,5...40,1,2 ... 40,1,2,3...39 So that's 40 rows - the order of which can be altered Add a helper column (41) = rand() sort on the 41st column Then delete it. Steve On Thu, 25 Jan 2007 02:00:00 -0000, Jon wrote: I am only looking for the numbers 1 through 40, not 1 to 1600. Each row and column should contain no duplicates. numbers should be in random order. "SteveW" wrote: On Wed, 24 Jan 2007 23:19:17 -0000, MyVeryOwnSelf wrote: Is there a way to create a 40 by 40 sheet where each column and each row have no duplicate numbers. And have this randomly generated? Here's one way. In cell A1 of Sheet1, put =RAND() and extend right and down 40 by 40. In cell A1 of Sheet2, put =RANK(Sheet1!A1,Sheet1!A:A) and extend right and down 40 by 40. Hit the F9 key to get a new set of values. There is is a bit in the original question that says *NO DUPLICATES* The question is whether that original poster wants the numbers 1 through 1600 arranged randomly in a 40 x 40 grid or the numbers 1 to 40 arranged in each row such that each column has the numbers 1 to 40 ie if it was 3x3 1 2 3 . . . 1 2 3 4 5 6 . . . 2 3 1 7 8 9 . . . 3 1 2 using the numbers 1 to 9 or 1 to 3 in eachrow/column -- Steve (3) -- Dave Peterson -- Dave Peterson |
Random Number
Neat.
On Thu, 25 Jan 2007 03:47:33 -0000, Dave Peterson wrote: And in code, it would look kind of like: Option Explicit Sub testme01() Dim wks As Worksheet Dim HowMany As Long HowMany = 40 Set wks = Workbooks.Add(1).Worksheets(1) With wks With .Range("A1").Offset(1, 0).Resize(HowMany, HowMany) .Formula = "=MOD(ROW()-1+COLUMN()-1-1," & HowMany & ")+1" End With .Range("a1").Resize(1, HowMany).Formula = "=rand()" .Range("a1").Offset(1, HowMany).Resize(HowMany, 1).Formula = "=rand()" With .UsedRange .Value = .Value End With With Range("A1").Offset(1, 0).Resize(HowMany, HowMany + 1) .Sort key1:=.Columns(HowMany + 1), Order1:=xlAscending, _ Header:=xlNo, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End With With .Range("a1").Resize(HowMany + 1, HowMany) .Sort key1:=.Rows(1), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight End With .Rows(1).Delete .Columns(HowMany + 1).Delete .UsedRange.Columns.AutoFit End With End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm (Nice idea again, Steve!) Dave Peterson wrote: Excellent idea. And the OP could add =rand() to a new row 1 and sort by that. Select A1:AN41 (avoid the column 41 with =rand() in it) Then Data|Sort|Options button|Sort left to right. (and when doing the sort by the 41st column, don't include that top helper row.) SteveW wrote: Right in that case the rows will have to be 1,2,3,4,...40 2,3,4,5...40,1 3,4,5...40,1,2 ... 40,1,2,3...39 So that's 40 rows - the order of which can be altered Add a helper column (41) = rand() sort on the 41st column Then delete it. Steve On Thu, 25 Jan 2007 02:00:00 -0000, Jon wrote: I am only looking for the numbers 1 through 40, not 1 to 1600. Each row and column should contain no duplicates. numbers should be in random order. "SteveW" wrote: On Wed, 24 Jan 2007 23:19:17 -0000, MyVeryOwnSelf wrote: Is there a way to create a 40 by 40 sheet where each column and each row have no duplicate numbers. And have this randomly generated? Here's one way. In cell A1 of Sheet1, put =RAND() and extend right and down 40 by 40. In cell A1 of Sheet2, put =RANK(Sheet1!A1,Sheet1!A:A) and extend right and down 40 by 40. Hit the F9 key to get a new set of values. There is is a bit in the original question that says *NO DUPLICATES* The question is whether that original poster wants the numbers 1 through 1600 arranged randomly in a 40 x 40 grid or the numbers 1 to 40 arranged in each row such that each column has the numbers 1 to 40 ie if it was 3x3 1 2 3 . . . 1 2 3 4 5 6 . . . 2 3 1 7 8 9 . . . 3 1 2 using the numbers 1 to 9 or 1 to 3 in eachrow/column -- Steve (3) -- Dave Peterson -- Steve (3) |
Random Number
Permuting rows and columns only gives you a very small fraction of the
possibilities: http://en.wikipedia.org/wiki/Latin_square On Jan 25, 4:20 am, SteveW wrote: Neat. On Thu, 25 Jan 2007 03:47:33 -0000, Dave Peterson wrote: And in code, it would look kind of like: Option Explicit Sub testme01() Dim wks As Worksheet Dim HowMany As Long HowMany = 40 Set wks = Workbooks.Add(1).Worksheets(1) With wks With .Range("A1").Offset(1, 0).Resize(HowMany, HowMany) .Formula = "=MOD(ROW()-1+COLUMN()-1-1," & HowMany & ")+1" End With .Range("a1").Resize(1, HowMany).Formula = "=rand()" .Range("a1").Offset(1, HowMany).Resize(HowMany, 1).Formula = "=rand()" With .UsedRange .Value = .Value End With With Range("A1").Offset(1, 0).Resize(HowMany, HowMany + 1) .Sort key1:=.Columns(HowMany + 1), Order1:=xlAscending, _ Header:=xlNo, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End With With .Range("a1").Resize(HowMany + 1, HowMany) .Sort key1:=.Rows(1), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight End With .Rows(1).Delete .Columns(HowMany + 1).Delete .UsedRange.Columns.AutoFit End With End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm (Nice idea again, Steve!) Dave Peterson wrote: Excellent idea. And the OP could add =rand() to a new row 1 and sort by that. Select A1:AN41 (avoid the column 41 with =rand() in it) Then Data|Sort|Options button|Sort left to right. (and when doing the sort by the 41st column, don't include that top helper row.) SteveW wrote: Right in that case the rows will have to be 1,2,3,4,...40 2,3,4,5...40,1 3,4,5...40,1,2 ... 40,1,2,3...39 So that's 40 rows - the order of which can be altered Add a helper column (41) = rand() sort on the 41st column Then delete it. Steve On Thu, 25 Jan 2007 02:00:00 -0000, Jon wrote: I am only looking for the numbers 1 through 40, not 1 to 1600. Each row and column should contain no duplicates. numbers should be in random order. "SteveW" wrote: On Wed, 24 Jan 2007 23:19:17 -0000, MyVeryOwnSelf wrote: Is there a way to create a 40 by 40 sheet where each column and each row have no duplicate numbers. And have this randomly generated? Here's one way. In cell A1 of Sheet1, put =RAND() and extend right and down 40 by 40. In cell A1 of Sheet2, put =RANK(Sheet1!A1,Sheet1!A:A) and extend right and down 40 by 40. Hit the F9 key to get a new set of values. There is is a bit in the original question that says *NO DUPLICATES* The question is whether that original poster wants the numbers 1 through 1600 arranged randomly in a 40 x 40 grid or the numbers 1 to 40 arranged in each row such that each column has the numbers 1 to 40 ie if it was 3x3 1 2 3 . . . 1 2 3 4 5 6 . . . 2 3 1 7 8 9 . . . 3 1 2 using the numbers 1 to 9 or 1 to 3 in eachrow/column -- Steve (3) -- Dave Peterson-- Steve (3) |
Random Number
But if Jon only wanted one, then he is in luck!
Lori wrote: Permuting rows and columns only gives you a very small fraction of the possibilities: http://en.wikipedia.org/wiki/Latin_square On Jan 25, 4:20 am, SteveW wrote: Neat. On Thu, 25 Jan 2007 03:47:33 -0000, Dave Peterson wrote: And in code, it would look kind of like: Option Explicit Sub testme01() Dim wks As Worksheet Dim HowMany As Long HowMany = 40 Set wks = Workbooks.Add(1).Worksheets(1) With wks With .Range("A1").Offset(1, 0).Resize(HowMany, HowMany) .Formula = "=MOD(ROW()-1+COLUMN()-1-1," & HowMany & ")+1" End With .Range("a1").Resize(1, HowMany).Formula = "=rand()" .Range("a1").Offset(1, HowMany).Resize(HowMany, 1).Formula = "=rand()" With .UsedRange .Value = .Value End With With Range("A1").Offset(1, 0).Resize(HowMany, HowMany + 1) .Sort key1:=.Columns(HowMany + 1), Order1:=xlAscending, _ Header:=xlNo, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End With With .Range("a1").Resize(HowMany + 1, HowMany) .Sort key1:=.Rows(1), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight End With .Rows(1).Delete .Columns(HowMany + 1).Delete .UsedRange.Columns.AutoFit End With End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm (Nice idea again, Steve!) Dave Peterson wrote: Excellent idea. And the OP could add =rand() to a new row 1 and sort by that. Select A1:AN41 (avoid the column 41 with =rand() in it) Then Data|Sort|Options button|Sort left to right. (and when doing the sort by the 41st column, don't include that top helper row.) SteveW wrote: Right in that case the rows will have to be 1,2,3,4,...40 2,3,4,5...40,1 3,4,5...40,1,2 ... 40,1,2,3...39 So that's 40 rows - the order of which can be altered Add a helper column (41) = rand() sort on the 41st column Then delete it. Steve On Thu, 25 Jan 2007 02:00:00 -0000, Jon wrote: I am only looking for the numbers 1 through 40, not 1 to 1600. Each row and column should contain no duplicates. numbers should be in random order. "SteveW" wrote: On Wed, 24 Jan 2007 23:19:17 -0000, MyVeryOwnSelf wrote: Is there a way to create a 40 by 40 sheet where each column and each row have no duplicate numbers. And have this randomly generated? Here's one way. In cell A1 of Sheet1, put =RAND() and extend right and down 40 by 40. In cell A1 of Sheet2, put =RANK(Sheet1!A1,Sheet1!A:A) and extend right and down 40 by 40. Hit the F9 key to get a new set of values. There is is a bit in the original question that says *NO DUPLICATES* The question is whether that original poster wants the numbers 1 through 1600 arranged randomly in a 40 x 40 grid or the numbers 1 to 40 arranged in each row such that each column has the numbers 1 to 40 ie if it was 3x3 1 2 3 . . . 1 2 3 4 5 6 . . . 2 3 1 7 8 9 . . . 3 1 2 using the numbers 1 to 9 or 1 to 3 in eachrow/column -- Steve (3) -- Dave Peterson-- Steve (3) -- Dave Peterson |
Random Number
That is exactly what I am looking for. Only 40x40. I just want it to randomly
generate the table. I saw some macro's and other stuff, but I have no idea how to do those. Any easy ways to make this happen? "Lori" wrote: Permuting rows and columns only gives you a very small fraction of the possibilities: http://en.wikipedia.org/wiki/Latin_square On Jan 25, 4:20 am, SteveW wrote: Neat. On Thu, 25 Jan 2007 03:47:33 -0000, Dave Peterson wrote: And in code, it would look kind of like: Option Explicit Sub testme01() Dim wks As Worksheet Dim HowMany As Long HowMany = 40 Set wks = Workbooks.Add(1).Worksheets(1) With wks With .Range("A1").Offset(1, 0).Resize(HowMany, HowMany) .Formula = "=MOD(ROW()-1+COLUMN()-1-1," & HowMany & ")+1" End With .Range("a1").Resize(1, HowMany).Formula = "=rand()" .Range("a1").Offset(1, HowMany).Resize(HowMany, 1).Formula = "=rand()" With .UsedRange .Value = .Value End With With Range("A1").Offset(1, 0).Resize(HowMany, HowMany + 1) .Sort key1:=.Columns(HowMany + 1), Order1:=xlAscending, _ Header:=xlNo, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End With With .Range("a1").Resize(HowMany + 1, HowMany) .Sort key1:=.Rows(1), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight End With .Rows(1).Delete .Columns(HowMany + 1).Delete .UsedRange.Columns.AutoFit End With End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm (Nice idea again, Steve!) Dave Peterson wrote: Excellent idea. And the OP could add =rand() to a new row 1 and sort by that. Select A1:AN41 (avoid the column 41 with =rand() in it) Then Data|Sort|Options button|Sort left to right. (and when doing the sort by the 41st column, don't include that top helper row.) SteveW wrote: Right in that case the rows will have to be 1,2,3,4,...40 2,3,4,5...40,1 3,4,5...40,1,2 ... 40,1,2,3...39 So that's 40 rows - the order of which can be altered Add a helper column (41) = rand() sort on the 41st column Then delete it. Steve On Thu, 25 Jan 2007 02:00:00 -0000, Jon wrote: I am only looking for the numbers 1 through 40, not 1 to 1600. Each row and column should contain no duplicates. numbers should be in random order. "SteveW" wrote: On Wed, 24 Jan 2007 23:19:17 -0000, MyVeryOwnSelf wrote: Is there a way to create a 40 by 40 sheet where each column and each row have no duplicate numbers. And have this randomly generated? Here's one way. In cell A1 of Sheet1, put =RAND() and extend right and down 40 by 40. In cell A1 of Sheet2, put =RANK(Sheet1!A1,Sheet1!A:A) and extend right and down 40 by 40. Hit the F9 key to get a new set of values. There is is a bit in the original question that says *NO DUPLICATES* The question is whether that original poster wants the numbers 1 through 1600 arranged randomly in a 40 x 40 grid or the numbers 1 to 40 arranged in each row such that each column has the numbers 1 to 40 ie if it was 3x3 1 2 3 . . . 1 2 3 4 5 6 . . . 2 3 1 7 8 9 . . . 3 1 2 using the numbers 1 to 9 or 1 to 3 in eachrow/column -- Steve (3) -- Dave Peterson-- Steve (3) |
Random Number
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm Short course: Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side Paste the code in there. Now go back to excel to test it out. Tools|Macro|macros|select that macro and click Run Jon wrote: That is exactly what I am looking for. Only 40x40. I just want it to randomly generate the table. I saw some macro's and other stuff, but I have no idea how to do those. Any easy ways to make this happen? "Lori" wrote: Permuting rows and columns only gives you a very small fraction of the possibilities: http://en.wikipedia.org/wiki/Latin_square On Jan 25, 4:20 am, SteveW wrote: Neat. On Thu, 25 Jan 2007 03:47:33 -0000, Dave Peterson wrote: And in code, it would look kind of like: Option Explicit Sub testme01() Dim wks As Worksheet Dim HowMany As Long HowMany = 40 Set wks = Workbooks.Add(1).Worksheets(1) With wks With .Range("A1").Offset(1, 0).Resize(HowMany, HowMany) .Formula = "=MOD(ROW()-1+COLUMN()-1-1," & HowMany & ")+1" End With .Range("a1").Resize(1, HowMany).Formula = "=rand()" .Range("a1").Offset(1, HowMany).Resize(HowMany, 1).Formula = "=rand()" With .UsedRange .Value = .Value End With With Range("A1").Offset(1, 0).Resize(HowMany, HowMany + 1) .Sort key1:=.Columns(HowMany + 1), Order1:=xlAscending, _ Header:=xlNo, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End With With .Range("a1").Resize(HowMany + 1, HowMany) .Sort key1:=.Rows(1), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight End With .Rows(1).Delete .Columns(HowMany + 1).Delete .UsedRange.Columns.AutoFit End With End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm (Nice idea again, Steve!) Dave Peterson wrote: Excellent idea. And the OP could add =rand() to a new row 1 and sort by that. Select A1:AN41 (avoid the column 41 with =rand() in it) Then Data|Sort|Options button|Sort left to right. (and when doing the sort by the 41st column, don't include that top helper row.) SteveW wrote: Right in that case the rows will have to be 1,2,3,4,...40 2,3,4,5...40,1 3,4,5...40,1,2 ... 40,1,2,3...39 So that's 40 rows - the order of which can be altered Add a helper column (41) = rand() sort on the 41st column Then delete it. Steve On Thu, 25 Jan 2007 02:00:00 -0000, Jon wrote: I am only looking for the numbers 1 through 40, not 1 to 1600. Each row and column should contain no duplicates. numbers should be in random order. "SteveW" wrote: On Wed, 24 Jan 2007 23:19:17 -0000, MyVeryOwnSelf wrote: Is there a way to create a 40 by 40 sheet where each column and each row have no duplicate numbers. And have this randomly generated? Here's one way. In cell A1 of Sheet1, put =RAND() and extend right and down 40 by 40. In cell A1 of Sheet2, put =RANK(Sheet1!A1,Sheet1!A:A) and extend right and down 40 by 40. Hit the F9 key to get a new set of values. There is is a bit in the original question that says *NO DUPLICATES* The question is whether that original poster wants the numbers 1 through 1600 arranged randomly in a 40 x 40 grid or the numbers 1 to 40 arranged in each row such that each column has the numbers 1 to 40 ie if it was 3x3 1 2 3 . . . 1 2 3 4 5 6 . . . 2 3 1 7 8 9 . . . 3 1 2 using the numbers 1 to 9 or 1 to 3 in eachrow/column -- Steve (3) -- Dave Peterson-- Steve (3) -- Dave Peterson |
Random Number
Dave,
I figured out how to input the macro. Now my question is, how do I save it so that when you open the file the macro is still there. Or if I want to send the file to someone else will the macro go with it or do I need to have them install it? Thanks for the help. "Dave Peterson" wrote: If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Short course: Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side Paste the code in there. Now go back to excel to test it out. Tools|Macro|macros|select that macro and click Run Jon wrote: That is exactly what I am looking for. Only 40x40. I just want it to randomly generate the table. I saw some macro's and other stuff, but I have no idea how to do those. Any easy ways to make this happen? "Lori" wrote: Permuting rows and columns only gives you a very small fraction of the possibilities: http://en.wikipedia.org/wiki/Latin_square On Jan 25, 4:20 am, SteveW wrote: Neat. On Thu, 25 Jan 2007 03:47:33 -0000, Dave Peterson wrote: And in code, it would look kind of like: Option Explicit Sub testme01() Dim wks As Worksheet Dim HowMany As Long HowMany = 40 Set wks = Workbooks.Add(1).Worksheets(1) With wks With .Range("A1").Offset(1, 0).Resize(HowMany, HowMany) .Formula = "=MOD(ROW()-1+COLUMN()-1-1," & HowMany & ")+1" End With .Range("a1").Resize(1, HowMany).Formula = "=rand()" .Range("a1").Offset(1, HowMany).Resize(HowMany, 1).Formula = "=rand()" With .UsedRange .Value = .Value End With With Range("A1").Offset(1, 0).Resize(HowMany, HowMany + 1) .Sort key1:=.Columns(HowMany + 1), Order1:=xlAscending, _ Header:=xlNo, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End With With .Range("a1").Resize(HowMany + 1, HowMany) .Sort key1:=.Rows(1), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight End With .Rows(1).Delete .Columns(HowMany + 1).Delete .UsedRange.Columns.AutoFit End With End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm (Nice idea again, Steve!) Dave Peterson wrote: Excellent idea. And the OP could add =rand() to a new row 1 and sort by that. Select A1:AN41 (avoid the column 41 with =rand() in it) Then Data|Sort|Options button|Sort left to right. (and when doing the sort by the 41st column, don't include that top helper row.) SteveW wrote: Right in that case the rows will have to be 1,2,3,4,...40 2,3,4,5...40,1 3,4,5...40,1,2 ... 40,1,2,3...39 So that's 40 rows - the order of which can be altered Add a helper column (41) = rand() sort on the 41st column Then delete it. Steve On Thu, 25 Jan 2007 02:00:00 -0000, Jon wrote: I am only looking for the numbers 1 through 40, not 1 to 1600. Each row and column should contain no duplicates. numbers should be in random order. "SteveW" wrote: On Wed, 24 Jan 2007 23:19:17 -0000, MyVeryOwnSelf wrote: Is there a way to create a 40 by 40 sheet where each column and each row have no duplicate numbers. And have this randomly generated? Here's one way. In cell A1 of Sheet1, put =RAND() and extend right and down 40 by 40. In cell A1 of Sheet2, put =RANK(Sheet1!A1,Sheet1!A:A) and extend right and down 40 by 40. Hit the F9 key to get a new set of values. There is is a bit in the original question that says *NO DUPLICATES* The question is whether that original poster wants the numbers 1 through 1600 arranged randomly in a 40 x 40 grid or the numbers 1 to 40 arranged in each row such that each column has the numbers 1 to 40 ie if it was 3x3 1 2 3 . . . 1 2 3 4 5 6 . . . 2 3 1 7 8 9 . . . 3 1 2 using the numbers 1 to 9 or 1 to 3 in eachrow/column -- Steve (3) -- Dave Peterson-- Steve (3) -- Dave Peterson |
Random Number
Save the workbook with the macro and share that workbook with others.
Macros live in workbooks and you can just share the workbook with others. But you could make it easier to run. Just keep a single sheet in that workbook. Show the Forms toolbar (view|toolbars) and plop a big button from that toolbar onto that single worksheet. Then change the caption to "Click Here To Generate 40 by 40 Matrix" (or something you like). Assign the macro to that big old button. Put a couple of instructions on that worksheet and save it as nice name: MacroWorkbookToGenerateGiantMatrix.xls Tell the users to open the workbook (enabling macros) and click the giant button. Jon wrote: Dave, I figured out how to input the macro. Now my question is, how do I save it so that when you open the file the macro is still there. Or if I want to send the file to someone else will the macro go with it or do I need to have them install it? Thanks for the help. "Dave Peterson" wrote: If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Short course: Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side Paste the code in there. Now go back to excel to test it out. Tools|Macro|macros|select that macro and click Run Jon wrote: That is exactly what I am looking for. Only 40x40. I just want it to randomly generate the table. I saw some macro's and other stuff, but I have no idea how to do those. Any easy ways to make this happen? "Lori" wrote: Permuting rows and columns only gives you a very small fraction of the possibilities: http://en.wikipedia.org/wiki/Latin_square On Jan 25, 4:20 am, SteveW wrote: Neat. On Thu, 25 Jan 2007 03:47:33 -0000, Dave Peterson wrote: And in code, it would look kind of like: Option Explicit Sub testme01() Dim wks As Worksheet Dim HowMany As Long HowMany = 40 Set wks = Workbooks.Add(1).Worksheets(1) With wks With .Range("A1").Offset(1, 0).Resize(HowMany, HowMany) .Formula = "=MOD(ROW()-1+COLUMN()-1-1," & HowMany & ")+1" End With .Range("a1").Resize(1, HowMany).Formula = "=rand()" .Range("a1").Offset(1, HowMany).Resize(HowMany, 1).Formula = "=rand()" With .UsedRange .Value = .Value End With With Range("A1").Offset(1, 0).Resize(HowMany, HowMany + 1) .Sort key1:=.Columns(HowMany + 1), Order1:=xlAscending, _ Header:=xlNo, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End With With .Range("a1").Resize(HowMany + 1, HowMany) .Sort key1:=.Rows(1), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight End With .Rows(1).Delete .Columns(HowMany + 1).Delete .UsedRange.Columns.AutoFit End With End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm (Nice idea again, Steve!) Dave Peterson wrote: Excellent idea. And the OP could add =rand() to a new row 1 and sort by that. Select A1:AN41 (avoid the column 41 with =rand() in it) Then Data|Sort|Options button|Sort left to right. (and when doing the sort by the 41st column, don't include that top helper row.) SteveW wrote: Right in that case the rows will have to be 1,2,3,4,...40 2,3,4,5...40,1 3,4,5...40,1,2 ... 40,1,2,3...39 So that's 40 rows - the order of which can be altered Add a helper column (41) = rand() sort on the 41st column Then delete it. Steve On Thu, 25 Jan 2007 02:00:00 -0000, Jon wrote: I am only looking for the numbers 1 through 40, not 1 to 1600. Each row and column should contain no duplicates. numbers should be in random order. "SteveW" wrote: On Wed, 24 Jan 2007 23:19:17 -0000, MyVeryOwnSelf wrote: Is there a way to create a 40 by 40 sheet where each column and each row have no duplicate numbers. And have this randomly generated? Here's one way. In cell A1 of Sheet1, put =RAND() and extend right and down 40 by 40. In cell A1 of Sheet2, put =RANK(Sheet1!A1,Sheet1!A:A) and extend right and down 40 by 40. Hit the F9 key to get a new set of values. There is is a bit in the original question that says *NO DUPLICATES* The question is whether that original poster wants the numbers 1 through 1600 arranged randomly in a 40 x 40 grid or the numbers 1 to 40 arranged in each row such that each column has the numbers 1 to 40 ie if it was 3x3 1 2 3 . . . 1 2 3 4 5 6 . . . 2 3 1 7 8 9 . . . 3 1 2 using the numbers 1 to 9 or 1 to 3 in eachrow/column -- Steve (3) -- Dave Peterson-- Steve (3) -- Dave Peterson -- Dave Peterson |
Random Number
Easy, macros etc are saved automatically as part of the workbook
Just save in the normal way. Steve On Thu, 25 Jan 2007 22:01:02 -0000, Jon wrote: Dave, I figured out how to input the macro. Now my question is, how do I save it so that when you open the file the macro is still there. Or if I want to send the file to someone else will the macro go with it or do I need to have them install it? Thanks for the help. "Dave Peterson" wrote: If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Short course: Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side Paste the code in there. Now go back to excel to test it out. Tools|Macro|macros|select that macro and click Run Jon wrote: That is exactly what I am looking for. Only 40x40. I just want it to randomly generate the table. I saw some macro's and other stuff, but I have no idea how to do those. Any easy ways to make this happen? "Lori" wrote: Permuting rows and columns only gives you a very small fraction of the possibilities: http://en.wikipedia.org/wiki/Latin_square On Jan 25, 4:20 am, SteveW wrote: Neat. On Thu, 25 Jan 2007 03:47:33 -0000, Dave Peterson wrote: And in code, it would look kind of like: Option Explicit Sub testme01() Dim wks As Worksheet Dim HowMany As Long HowMany = 40 Set wks = Workbooks.Add(1).Worksheets(1) With wks With .Range("A1").Offset(1, 0).Resize(HowMany, HowMany) .Formula = "=MOD(ROW()-1+COLUMN()-1-1," & HowMany & ")+1" End With .Range("a1").Resize(1, HowMany).Formula = "=rand()" .Range("a1").Offset(1, HowMany).Resize(HowMany, 1).Formula = "=rand()" With .UsedRange .Value = .Value End With With Range("A1").Offset(1, 0).Resize(HowMany, HowMany + 1) .Sort key1:=.Columns(HowMany + 1), Order1:=xlAscending, _ Header:=xlNo, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End With With .Range("a1").Resize(HowMany + 1, HowMany) .Sort key1:=.Rows(1), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight End With .Rows(1).Delete .Columns(HowMany + 1).Delete .UsedRange.Columns.AutoFit End With End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm (Nice idea again, Steve!) Dave Peterson wrote: Excellent idea. And the OP could add =rand() to a new row 1 and sort by that. Select A1:AN41 (avoid the column 41 with =rand() in it) Then Data|Sort|Options button|Sort left to right. (and when doing the sort by the 41st column, don't include that top helper row.) SteveW wrote: Right in that case the rows will have to be 1,2,3,4,...40 2,3,4,5...40,1 3,4,5...40,1,2 ... 40,1,2,3...39 So that's 40 rows - the order of which can be altered Add a helper column (41) = rand() sort on the 41st column Then delete it. Steve On Thu, 25 Jan 2007 02:00:00 -0000, Jon wrote: I am only looking for the numbers 1 through 40, not 1 to 1600. Each row and column should contain no duplicates. numbers should be in random order. "SteveW" wrote: On Wed, 24 Jan 2007 23:19:17 -0000, MyVeryOwnSelf wrote: Is there a way to create a 40 by 40 sheet where each column and each row have no duplicate numbers. And have this randomly generated? Here's one way. In cell A1 of Sheet1, put =RAND() and extend right and down 40 by 40. In cell A1 of Sheet2, put =RANK(Sheet1!A1,Sheet1!A:A) and extend right and down 40 by 40. Hit the F9 key to get a new set of values. There is is a bit in the original question that says *NO DUPLICATES* The question is whether that original poster wants the numbers 1 through 1600 arranged randomly in a 40 x 40 grid or the numbers 1 to 40 arranged in each row such that each column has the numbers 1 to 40 ie if it was 3x3 1 2 3 . . . 1 2 3 4 5 6 . . . 2 3 1 7 8 9 . . . 3 1 2 using the numbers 1 to 9 or 1 to 3 in eachrow/column -- Steve (3) -- Dave Peterson-- Steve (3) -- Dave Peterson |
Random Number
Thanks Dave,
I got it to work just like I wanted it to. One last question, in the macro code what would I need to put a border that is visible when printed around each of the squares in the matrix? "Dave Peterson" wrote: Save the workbook with the macro and share that workbook with others. Macros live in workbooks and you can just share the workbook with others. But you could make it easier to run. Just keep a single sheet in that workbook. Show the Forms toolbar (view|toolbars) and plop a big button from that toolbar onto that single worksheet. Then change the caption to "Click Here To Generate 40 by 40 Matrix" (or something you like). Assign the macro to that big old button. Put a couple of instructions on that worksheet and save it as nice name: MacroWorkbookToGenerateGiantMatrix.xls Tell the users to open the workbook (enabling macros) and click the giant button. Jon wrote: Dave, I figured out how to input the macro. Now my question is, how do I save it so that when you open the file the macro is still there. Or if I want to send the file to someone else will the macro go with it or do I need to have them install it? Thanks for the help. "Dave Peterson" wrote: If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Short course: Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side Paste the code in there. Now go back to excel to test it out. Tools|Macro|macros|select that macro and click Run Jon wrote: That is exactly what I am looking for. Only 40x40. I just want it to randomly generate the table. I saw some macro's and other stuff, but I have no idea how to do those. Any easy ways to make this happen? "Lori" wrote: Permuting rows and columns only gives you a very small fraction of the possibilities: http://en.wikipedia.org/wiki/Latin_square On Jan 25, 4:20 am, SteveW wrote: Neat. On Thu, 25 Jan 2007 03:47:33 -0000, Dave Peterson wrote: And in code, it would look kind of like: Option Explicit Sub testme01() Dim wks As Worksheet Dim HowMany As Long HowMany = 40 Set wks = Workbooks.Add(1).Worksheets(1) With wks With .Range("A1").Offset(1, 0).Resize(HowMany, HowMany) .Formula = "=MOD(ROW()-1+COLUMN()-1-1," & HowMany & ")+1" End With .Range("a1").Resize(1, HowMany).Formula = "=rand()" .Range("a1").Offset(1, HowMany).Resize(HowMany, 1).Formula = "=rand()" With .UsedRange .Value = .Value End With With Range("A1").Offset(1, 0).Resize(HowMany, HowMany + 1) .Sort key1:=.Columns(HowMany + 1), Order1:=xlAscending, _ Header:=xlNo, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End With With .Range("a1").Resize(HowMany + 1, HowMany) .Sort key1:=.Rows(1), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight End With .Rows(1).Delete .Columns(HowMany + 1).Delete .UsedRange.Columns.AutoFit End With End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm (Nice idea again, Steve!) Dave Peterson wrote: Excellent idea. And the OP could add =rand() to a new row 1 and sort by that. Select A1:AN41 (avoid the column 41 with =rand() in it) Then Data|Sort|Options button|Sort left to right. (and when doing the sort by the 41st column, don't include that top helper row.) SteveW wrote: Right in that case the rows will have to be 1,2,3,4,...40 2,3,4,5...40,1 3,4,5...40,1,2 ... 40,1,2,3...39 So that's 40 rows - the order of which can be altered Add a helper column (41) = rand() sort on the 41st column Then delete it. Steve On Thu, 25 Jan 2007 02:00:00 -0000, Jon wrote: I am only looking for the numbers 1 through 40, not 1 to 1600. Each row and column should contain no duplicates. numbers should be in random order. "SteveW" wrote: On Wed, 24 Jan 2007 23:19:17 -0000, MyVeryOwnSelf wrote: Is there a way to create a 40 by 40 sheet where each column and each row have no duplicate numbers. And have this randomly generated? Here's one way. In cell A1 of Sheet1, put =RAND() and extend right and down 40 by 40. In cell A1 of Sheet2, put =RANK(Sheet1!A1,Sheet1!A:A) and extend right and down 40 by 40. Hit the F9 key to get a new set of values. There is is a bit in the original question that says *NO DUPLICATES* The question is whether that original poster wants the numbers 1 through 1600 arranged randomly in a 40 x 40 grid or the numbers 1 to 40 arranged in each row such that each column has the numbers 1 to 40 ie if it was 3x3 1 2 3 . . . 1 2 3 4 5 6 . . . 2 3 1 7 8 9 . . . 3 1 2 using the numbers 1 to 9 or 1 to 3 in eachrow/column -- Steve (3) -- Dave Peterson-- Steve (3) -- Dave Peterson -- Dave Peterson |
Random Number
Lots of times, it's easiest to just record a macro when you do it yourself and
tweak the code a bit. That's what I did to get this: Option Explicit Sub DoBorders() With ActiveSheet.UsedRange With .Borders(xlInsideHorizontal) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With .Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With .Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With .Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With .Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With .Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With .Borders(xlInsideHorizontal) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End With End Sub Right before the other macro ends, you'd add a line like: Call DoBorders 'then finish up with End Sub Jon wrote: Thanks Dave, I got it to work just like I wanted it to. One last question, in the macro code what would I need to put a border that is visible when printed around each of the squares in the matrix? "Dave Peterson" wrote: Save the workbook with the macro and share that workbook with others. Macros live in workbooks and you can just share the workbook with others. But you could make it easier to run. Just keep a single sheet in that workbook. Show the Forms toolbar (view|toolbars) and plop a big button from that toolbar onto that single worksheet. Then change the caption to "Click Here To Generate 40 by 40 Matrix" (or something you like). Assign the macro to that big old button. Put a couple of instructions on that worksheet and save it as nice name: MacroWorkbookToGenerateGiantMatrix.xls Tell the users to open the workbook (enabling macros) and click the giant button. Jon wrote: Dave, I figured out how to input the macro. Now my question is, how do I save it so that when you open the file the macro is still there. Or if I want to send the file to someone else will the macro go with it or do I need to have them install it? Thanks for the help. "Dave Peterson" wrote: If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Short course: Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side Paste the code in there. Now go back to excel to test it out. Tools|Macro|macros|select that macro and click Run Jon wrote: That is exactly what I am looking for. Only 40x40. I just want it to randomly generate the table. I saw some macro's and other stuff, but I have no idea how to do those. Any easy ways to make this happen? "Lori" wrote: Permuting rows and columns only gives you a very small fraction of the possibilities: http://en.wikipedia.org/wiki/Latin_square On Jan 25, 4:20 am, SteveW wrote: Neat. On Thu, 25 Jan 2007 03:47:33 -0000, Dave Peterson wrote: And in code, it would look kind of like: Option Explicit Sub testme01() Dim wks As Worksheet Dim HowMany As Long HowMany = 40 Set wks = Workbooks.Add(1).Worksheets(1) With wks With .Range("A1").Offset(1, 0).Resize(HowMany, HowMany) .Formula = "=MOD(ROW()-1+COLUMN()-1-1," & HowMany & ")+1" End With .Range("a1").Resize(1, HowMany).Formula = "=rand()" .Range("a1").Offset(1, HowMany).Resize(HowMany, 1).Formula = "=rand()" With .UsedRange .Value = .Value End With With Range("A1").Offset(1, 0).Resize(HowMany, HowMany + 1) .Sort key1:=.Columns(HowMany + 1), Order1:=xlAscending, _ Header:=xlNo, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End With With .Range("a1").Resize(HowMany + 1, HowMany) .Sort key1:=.Rows(1), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight End With .Rows(1).Delete .Columns(HowMany + 1).Delete .UsedRange.Columns.AutoFit End With End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm (Nice idea again, Steve!) Dave Peterson wrote: Excellent idea. And the OP could add =rand() to a new row 1 and sort by that. Select A1:AN41 (avoid the column 41 with =rand() in it) Then Data|Sort|Options button|Sort left to right. (and when doing the sort by the 41st column, don't include that top helper row.) SteveW wrote: Right in that case the rows will have to be 1,2,3,4,...40 2,3,4,5...40,1 3,4,5...40,1,2 ... 40,1,2,3...39 So that's 40 rows - the order of which can be altered Add a helper column (41) = rand() sort on the 41st column Then delete it. Steve On Thu, 25 Jan 2007 02:00:00 -0000, Jon wrote: I am only looking for the numbers 1 through 40, not 1 to 1600. Each row and column should contain no duplicates. numbers should be in random order. "SteveW" wrote: On Wed, 24 Jan 2007 23:19:17 -0000, MyVeryOwnSelf wrote: Is there a way to create a 40 by 40 sheet where each column and each row have no duplicate numbers. And have this randomly generated? Here's one way. In cell A1 of Sheet1, put =RAND() and extend right and down 40 by 40. In cell A1 of Sheet2, put =RANK(Sheet1!A1,Sheet1!A:A) and extend right and down 40 by 40. Hit the F9 key to get a new set of values. There is is a bit in the original question that says *NO DUPLICATES* The question is whether that original poster wants the numbers 1 through 1600 arranged randomly in a 40 x 40 grid or the numbers 1 to 40 arranged in each row such that each column has the numbers 1 to 40 ie if it was 3x3 1 2 3 . . . 1 2 3 4 5 6 . . . 2 3 1 7 8 9 . . . 3 1 2 using the numbers 1 to 9 or 1 to 3 in eachrow/column -- Steve (3) -- Dave Peterson-- Steve (3) -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 04:52 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com