If you need to randomly select 6 numbers from the range 1-40 with no repeats:

1. Enter the numbers 1-40 into an un-used column
2. Enter =RAND() into the cells of the adjacent column
3. Sort the two columns by the RAND column

This will jumble the numbers 1-40 randomly. Just pick the first six numbers.
--
Gary''s Student


"Steved" wrote:

Hello from Steved

ok I am no thinking straight

What do I need to do please to have a number in a row 1 to 40
as an example below

1,23,9,25,33,40 each row needs to be 1 to 40 but number no more than once
six rows deep yes the other rows can have the same numbers as above or below

=INDEX(ROW($A$1:$F$10),RANK($H1,$H$1:$H$60))

=RAND() copied H1:H60

Hello Gary From Steved

Can your formula devoloped to display
6 columns across and 10 rows deep please.

Thankyou.

"Gary''s Student" wrote:

If you need to randomly select 6 numbers from the range 1-40 with no repeats:

1. Enter the numbers 1-40 into an un-used column
2. Enter =RAND() into the cells of the adjacent column
3. Sort the two columns by the RAND column

This will jumble the numbers 1-40 randomly. Just pick the first six numbers.
--
Gary''s Student


"Steved" wrote:

Hello from Steved

ok I am no thinking straight

What do I need to do please to have a number in a row 1 to 40
as an example below

1,23,9,25,33,40 each row needs to be 1 to 40 but number no more than once
six rows deep yes the other rows can have the same numbers as above or below

=INDEX(ROW($A$1:$F$10),RANK($H1,$H$1:$H$60))

=RAND() copied H1:H60

Steved wrote...
....
Can your formula devoloped to display
6 columns across and 10 rows deep please.
....
"Gary''s Student" wrote:
If you need to randomly select 6 numbers from the range 1-40 with no repeats:

1. Enter the numbers 1-40 into an un-used column
2. Enter =RAND() into the cells of the adjacent column
3. Sort the two columns by the RAND column
....

You don't need steps 1 or 3. All you need is a range of 60 cells (10*6)
all containing =RAND(). If that range were named PRNA and the top-left
cell of your 10 by 6 result range were C5, enter the following formula
in C5.

C5:
=COUNTIF(PRNA,"<="&INDEX(PRNA,ROWS($C$5:C5)*6-COLUMNS($C$5:C5)+1))

Select C5 and fill right into D5:H5. Then select C5:H5 and fill down
into C6:H14.

Hello Harlan from Steve

I think i'm missing something here

I followed what you put below naming the range PNRA

ok =RAND() are in cell A1:F10 I highlighted and then I
Insert Name Define

please I am lost where do I put the below


COUNTIF(PRNA,"<="&INDEX(PRNA,ROWS($C$5:C5)*6-COLUMNS($C$5:C5)+1))

Also do I change your $C$5:$C$5 to $A$1:$A$1

ok also please what is require to reconize the number between 1 to 40.

Thankyou.

"Harlan Grove" wrote:

Steved wrote...
....
Can your formula devoloped to display
6 columns across and 10 rows deep please.
....
"Gary''s Student" wrote:
If you need to randomly select 6 numbers from the range 1-40 with no repeats:

1. Enter the numbers 1-40 into an un-used column
2. Enter =RAND() into the cells of the adjacent column
3. Sort the two columns by the RAND column
....

You don't need steps 1 or 3. All you need is a range of 60 cells (10*6)
all containing =RAND(). If that range were named PRNA and the top-left
cell of your 10 by 6 result range were C5, enter the following formula
in C5.

C5:
=COUNTIF(PRNA,"<="&INDEX(PRNA,ROWS($C$5:C5)*6-COLUMNS($C$5:C5)+1))

Select C5 and fill right into D5:H5. Then select C5:H5 and fill down
into C6:H14.

You can try this:

Enter
=RAND()
In AA1 and drag across to BN1,
Then down to BN10,
So you have a 40 column by 10 row array of random numbers.

Then, enter this formula anywhere you wish:

=INDEX(ROW($A1:$A40),RANK(AA1,$AA1:$BN1))

Now, copy this formula across 6 columns.
Then, copy down 10 rows.

This should give you what you're looking for.
You'll get a new set of numbers with each hit of <F9.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------



"Steved" wrote in message
...
Hello Harlan from Steve

I think i'm missing something here

I followed what you put below naming the range PNRA

ok =RAND() are in cell A1:F10 I highlighted and then I
Insert Name Define

please I am lost where do I put the below


COUNTIF(PRNA,"<="&INDEX(PRNA,ROWS($C$5:C5)*6-COLUMNS($C$5:C5)+1))

Also do I change your $C$5:$C$5 to $A$1:$A$1

ok also please what is require to reconize the number between 1 to 40.

Thankyou.

"Harlan Grove" wrote:

Steved wrote...
....
Can your formula devoloped to display
6 columns across and 10 rows deep please.
....
"Gary''s Student" wrote:
If you need to randomly select 6 numbers from the range 1-40 with no
repeats:

1. Enter the numbers 1-40 into an un-used column
2. Enter =RAND() into the cells of the adjacent column
3. Sort the two columns by the RAND column
....

You don't need steps 1 or 3. All you need is a range of 60 cells (10*6)
all containing =RAND(). If that range were named PRNA and the top-left
cell of your 10 by 6 result range were C5, enter the following formula
in C5.

C5:
=COUNTIF(PRNA,"<="&INDEX(PRNA,ROWS($C$5:C5)*6-COLUMNS($C$5:C5)+1))

Select C5 and fill right into D5:H5. Then select C5:H5 and fill down
into C6:H14.

Hello Ragdyer from Steved

Excellent thankyou.

"Ragdyer" wrote:

You can try this:

Enter
=RAND()
In AA1 and drag across to BN1,
Then down to BN10,
So you have a 40 column by 10 row array of random numbers.

Then, enter this formula anywhere you wish:

=INDEX(ROW($A1:$A40),RANK(AA1,$AA1:$BN1))

Now, copy this formula across 6 columns.
Then, copy down 10 rows.

This should give you what you're looking for.
You'll get a new set of numbers with each hit of <F9.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------



"Steved" wrote in message
...
Hello Harlan from Steve

I think i'm missing something here

I followed what you put below naming the range PNRA

ok =RAND() are in cell A1:F10 I highlighted and then I
Insert Name Define

please I am lost where do I put the below


COUNTIF(PRNA,"<="&INDEX(PRNA,ROWS($C$5:C5)*6-COLUMNS($C$5:C5)+1))

Also do I change your $C$5:$C$5 to $A$1:$A$1

ok also please what is require to reconize the number between 1 to 40.

Thankyou.

"Harlan Grove" wrote:

Steved wrote...
....
Can your formula devoloped to display
6 columns across and 10 rows deep please.
....
"Gary''s Student" wrote:
If you need to randomly select 6 numbers from the range 1-40 with no
repeats:

1. Enter the numbers 1-40 into an un-used column
2. Enter =RAND() into the cells of the adjacent column
3. Sort the two columns by the RAND column
....

You don't need steps 1 or 3. All you need is a range of 60 cells (10*6)
all containing =RAND(). If that range were named PRNA and the top-left
cell of your 10 by 6 result range were C5, enter the following formula
in C5.

C5:
=COUNTIF(PRNA,"<="&INDEX(PRNA,ROWS($C$5:C5)*6-COLUMNS($C$5:C5)+1))

Select C5 and fill right into D5:H5. Then select C5:H5 and fill down
into C6:H14.

"Steved" wrote...
....
I followed what you put below naming the range PNRA

ok =RAND() are in cell A1:F10 I highlighted and then I
Insert Name Define

please I am lost where do I put the below


COUNTIF(PRNA,"<="&INDEX(PRNA,ROWS($C$5:C5)*6-COLUMNS($C$5:C5)+1))

First, you're missing the initial equal sign.

I may have misunderstood your question. Looks like you want 10 samples of 6
numbers each of which is drawn without replacement from 1-40. If so, then
you still don't need anything more than a 40 cell range each cell in which
containing =RAND(), which I'll still call PRNA. I'll further assume that
PRNA is 40 rows in a single column.

Then, in another range WHICH YOU MUST CHOOSE (but for my convenience, I'll
continue to use C5:H14, so in my case, the cells containing the =RAND()
formula don't overlar C5:H14), select C5:H5 and enter the following array
formula.

C5:H5 [array formula]:
=COUNTIF(PRNA,"<="&TRANSPOSE(OFFSET(PRNA,INT(35*RA ND()),0,6,1)))

Select C5:H5 and fill down into C6:H14.

Hello Harlan from Steved

ok put =RAND() in A1:A40
Then Insert, Name, Define, and typed PRNA then add and ok

I then

C5:H5 [array formula]: using Ctrl Shift Enter

=COUNTIF(PRNA,"<="&TRANSPOSE(OFFSET(PRNA,INT(35*RA ND()),0,6,1)))

Select C5:H5 and fill down into C6:H14.

Done all off the above pushed F9 and on the same row I get the same number
twice.

Am I missing something here.

Cheers



"Harlan Grove" wrote:

"Steved" wrote...
....
I followed what you put below naming the range PNRA

ok =RAND() are in cell A1:F10 I highlighted and then I
Insert Name Define

please I am lost where do I put the below


COUNTIF(PRNA,"<="&INDEX(PRNA,ROWS($C$5:C5)*6-COLUMNS($C$5:C5)+1))

First, you're missing the initial equal sign.

I may have misunderstood your question. Looks like you want 10 samples of 6
numbers each of which is drawn without replacement from 1-40. If so, then
you still don't need anything more than a 40 cell range each cell in which
containing =RAND(), which I'll still call PRNA. I'll further assume that
PRNA is 40 rows in a single column.

Then, in another range WHICH YOU MUST CHOOSE (but for my convenience, I'll
continue to use C5:H14, so in my case, the cells containing the =RAND()
formula don't overlar C5:H14), select C5:H5 and enter the following array
formula.

C5:H5 [array formula]:
=COUNTIF(PRNA,"<="&TRANSPOSE(OFFSET(PRNA,INT(35*RA ND()),0,6,1)))

Select C5:H5 and fill down into C6:H14.

"Steved" wrote...
....
=COUNTIF(PRNA,"<="&TRANSPOSE(OFFSET(PRNA,INT(35*R AND()),0,6,1)))
....

Sorry, I screwed this up. Swap the 6 and 1 arguments, so

=COUNTIF(PRNA,"<="&TRANSPOSE(OFFSET(PRNA,INT(35*RA ND()),0,1,6)))

Yes.

In my method we are selectimg 6 random numbers in a column. Select all six
numbers and push copy.

Go to the place in the first row and paste/special with the transpose mark
checked.

This will paste the column into your first row. Now we need to fill rows 2-10
and we will do the same thing
1. re-calculate the random numbers (CNTRL-ALT-F9)
2. re-sort the columns to get fresh random digits
3. copy/paste into the next row

--
Gary''s Student


"Steved" wrote:

Hello Gary From Steved

Can your formula devoloped to display
6 columns across and 10 rows deep please.

Thankyou.

"Gary''s Student" wrote:

If you need to randomly select 6 numbers from the range 1-40 with no repeats:

1. Enter the numbers 1-40 into an un-used column
2. Enter =RAND() into the cells of the adjacent column
3. Sort the two columns by the RAND column

This will jumble the numbers 1-40 randomly. Just pick the first six numbers.
--
Gary''s Student


"Steved" wrote:

Hello from Steved

ok I am no thinking straight

What do I need to do please to have a number in a row 1 to 40
as an example below

1,23,9,25,33,40 each row needs to be 1 to 40 but number no more than once
six rows deep yes the other rows can have the same numbers as above or below

=INDEX(ROW($A$1:$F$10),RANK($H1,$H$1:$H$60))

=RAND() copied H1:H60