Tags: array, automatically, cells, column, creating, excel, microsoft, msdn, mumber, number, random, software, specific

random mumber array

On Microsoft » Microsoft Excel

9,106 words with 3 Comments; publish: Fri, 23 May 2008 03:21:00 GMT; (30693.75, « »)

hi all

Can anyone give me some help with creating a random number array in a

very specific way?

First of all, in the cells of column A, automatically create a simpl

sequence of whole numbers, increasing from 1 to y, where y is anythin

from 10 upwards. So, if I specify y=10, then A1=1, A2=2

A3=3,...A10=10. ie. creating 10 rows numbered 1 to 10.

Then, for column B, the program generates random whole numbers betwee

1 and y, in this example, y=10, and places them in the cells of colum

B. But, each number in the random range may only appear once in colum

B ie. no duplicates. So, a number can only be generated once from th

range 1 to y. The final number in column B, in cell A10, is therefor

just the remaining unselected number from the random range.

Then, the program compares the numbers in each of the 10 rows, ie. A

against B1, etc; if they are the same in one or more rows, ie

duplicated, then the program runs the random number generation abov

again. Rows are then checked again for duplicates. If no duplicates ar

found in the rows then the column is now completed and the program move

on to the next column, in this example column C.

The program then runs both of the subsets above for the next column an

so on. So, the program must be able to specify x number of columns t

randomly generate up to column H, in this example x=C.

Once the final specified column has been randomly generated, th

program stops and a random array has been generated. In the exampl

above, one possibility would be -

A B C

1 7 5

2 9 6

3 1 10

4 5 8

5 2 1

6 8 3

7 3 4

8 10 2

9 6 7

10 4 9

Note that no numbers have been duplicated in any column or row - th

ultimate aim of the program. And, that each number appears in the arra

exactly the same number of times, in this case three times; which i

always the same as the total number of columns.

What I want to be able to do is open a new spreadsheet, put th

parameters of x and y into the program, click "go", and the rando

array is produced as above.

Now, all I need to know is how do I make Excel do this?

thanks

Je

--

jed

----

jedg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3273

View this thread: http://www.excelforum.com/showthread.php?threadid=52572

All Comments

Leave a comment...

  • 3 Comments
    • Do a google search for Sudoku and Excel. You might find it there.

      "jedg" wrote:

      > hi all

      > Can anyone give me some help with creating a random number array in a

      > very specific way?

      > First of all, in the cells of column A, automatically create a simple

      > sequence of whole numbers, increasing from 1 to y, where y is anything

      > from 10 upwards. So, if I specify y=10, then A1=1, A2=2,

      > A3=3,...A10=10. ie. creating 10 rows numbered 1 to 10.

      > Then, for column B, the program generates random whole numbers between

      > 1 and y, in this example, y=10, and places them in the cells of column

      > B. But, each number in the random range may only appear once in column

      > B ie. no duplicates. So, a number can only be generated once from the

      > range 1 to y. The final number in column B, in cell A10, is therefore

      > just the remaining unselected number from the random range.

      > Then, the program compares the numbers in each of the 10 rows, ie. A1

      > against B1, etc; if they are the same in one or more rows, ie.

      > duplicated, then the program runs the random number generation above

      > again. Rows are then checked again for duplicates. If no duplicates are

      > found in the rows then the column is now completed and the program moves

      > on to the next column, in this example column C.

      > The program then runs both of the subsets above for the next column and

      > so on. So, the program must be able to specify x number of columns to

      > randomly generate up to column H, in this example x=C.

      > Once the final specified column has been randomly generated, the

      > program stops and a random array has been generated. In the example

      > above, one possibility would be -

      > A B C

      > 1 7 5

      > 2 9 6

      > 3 1 10

      > 4 5 8

      > 5 2 1

      > 6 8 3

      > 7 3 4

      > 8 10 2

      > 9 6 7

      > 10 4 9

      > Note that no numbers have been duplicated in any column or row - the

      > ultimate aim of the program. And, that each number appears in the array

      > exactly the same number of times, in this case three times; which is

      > always the same as the total number of columns.

      > What I want to be able to do is open a new spreadsheet, put the

      > parameters of x and y into the program, click "go", and the random

      > array is produced as above.

      > Now, all I need to know is how do I make Excel do this?

      > thanks

      > Jed

      >

      > --

      > jedg

      > ---

      > jedg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32735

      > View this thread: http://www.excelforum.com/showthread.php?threadid=525724

      >

      #1; Fri, 23 May 2008 03:23:00 GMT
    • When you write that "y can be anything from 10 upwards", just how high

      do you have in mind? 1 000? 60 000? And how many columns do you have in

      mind? Just 3? 20? 200?

      Let us know. The scope of the problem drives the few ideas I have.

      Dave Braden

      jedg wrote:

      > hi all

      > Can anyone give me some help with creating a random number array in a

      > very specific way?

      > First of all, in the cells of column A, automatically create a simple

      > sequence of whole numbers, increasing from 1 to y, where y is anything

      > from 10 upwards. So, if I specify y=10, then A1=1, A2=2,

      > A3=3,...A10=10. ie. creating 10 rows numbered 1 to 10.

      > Then, for column B, the program generates random whole numbers between

      > 1 and y, in this example, y=10, and places them in the cells of column

      > B. But, each number in the random range may only appear once in column

      > B ie. no duplicates. So, a number can only be generated once from the

      > range 1 to y. The final number in column B, in cell A10, is therefore

      > just the remaining unselected number from the random range.

      > Then, the program compares the numbers in each of the 10 rows, ie. A1

      > against B1, etc; if they are the same in one or more rows, ie.

      > duplicated, then the program runs the random number generation above

      > again. Rows are then checked again for duplicates. If no duplicates are

      > found in the rows then the column is now completed and the program moves

      > on to the next column, in this example column C.

      > The program then runs both of the subsets above for the next column and

      > so on. So, the program must be able to specify x number of columns to

      > randomly generate up to column H, in this example x=C.

      > Once the final specified column has been randomly generated, the

      > program stops and a random array has been generated. In the example

      > above, one possibility would be -

      > A B C

      > 1 7 5

      > 2 9 6

      > 3 1 10

      > 4 5 8

      > 5 2 1

      > 6 8 3

      > 7 3 4

      > 8 10 2

      > 9 6 7

      > 10 4 9

      > Note that no numbers have been duplicated in any column or row - the

      > ultimate aim of the program. And, that each number appears in the array

      > exactly the same number of times, in this case three times; which is

      > always the same as the total number of columns.

      > What I want to be able to do is open a new spreadsheet, put the

      > parameters of x and y into the program, click "go", and the random

      > array is produced as above.

      > Now, all I need to know is how do I make Excel do this?

      > thanks

      > Jed

      >

      --

      Please keep response(s) solely within this thread.

      #2; Fri, 23 May 2008 03:24:00 GMT
    • When you write that "y can be anything from 10 upwards", just how high

      do you have in mind? 1 000? 60 000? And how many columns do you have

      in

      mind? Just 3? 20? 200?

      Let us know. The scope of the problem drives the few ideas I have.

      Dave Braden

      =======================================================

      hi Dave

      Thanks for offering some ideas on this.

      As I said in the original post, the maximum number of columns (x) would

      be

      eight, ie. A-H.

      The maximum number of rows (y) is 99.

      thanks

      Jed

      jedg

      ---

      jedg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32735

      View this thread: http://www.excelforum.com/showthread.php?threadid=525724

      #3; Fri, 23 May 2008 03:25:00 GMT