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?

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?

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?

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?

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?

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

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

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?

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

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

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?

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?

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.

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)

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

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

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

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)

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

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)

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

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

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)

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)

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

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)

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,
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

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

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

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

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