J
jedg
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
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