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)