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?

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)