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?

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?

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?

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

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?

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

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

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

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?

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)

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)

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