Counting When Last Occurance
 

Hi everyone,

I have a table of lottery numbers where 6 balls are drawn each draw.
The table is from D3:i whatever and obviously grows constantly.
The draw number is in column B with the last draw at the bottom.
What I would like is to list the numbers from 1 to 49 starting in S3
and going down and next to each number have the number of draws since
that particular number has been drawn please. So basically, start at
the bottom right cell of the table and work left and then up to find
out how many draws it has been since each of the numbers from 1 to 49
have been drawn.

Thanks in advance.
Paul

Counting When Last Occurance
 

On Jul 15, 2:18*pm, Paul Black wrote:
Hi everyone,

I have a table of lottery numbers where 6 balls are drawn each draw.
The table is from D3:i whatever and obviously grows constantly.
The draw number is in column B with the last draw at the bottom.
What I would like is to list the numbers from 1 to 49 starting in S3
and going down and next to each number have the number of draws since
that particular number has been drawn please. So basically, start at
the bottom right cell of the table and work left and then up to find
out how many draws it has been since each of the numbers from 1 to 49
have been drawn.

Thanks in advance.
Paul

Has anyone got any ideas please.

Counting When Last Occurance
 

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

wrote in message
...
On Jul 15, 2:18 pm, Paul Black wrote:
Hi everyone,

I have a table of lottery numbers where 6 balls are drawn each draw.
The table is from D3:i whatever and obviously grows constantly.
The draw number is in column B with the last draw at the bottom.
What I would like is to list the numbers from 1 to 49 starting in S3
and going down and next to each number have the number of draws since
that particular number has been drawn please. So basically, start at
the bottom right cell of the table and work left and then up to find
out how many draws it has been since each of the numbers from 1 to 49
have been drawn.

Thanks in advance.
Paul

Has anyone got any ideas please.

Counting When Last Occurance
 

On Jul 16, 2:47*pm, "Don Guillett" wrote:
* * * If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
wrote in message

...
On Jul 15, 2:18 pm, Paul Black wrote:

Hi everyone,

I have a table of lottery numbers where 6 balls are drawn each draw.
The table is from D3:i whatever and obviously grows constantly.
The draw number is in column B with the last draw at the bottom.
What I would like is to list the numbers from 1 to 49 starting in S3
and going down and next to each number have the number of draws since
that particular number has been drawn please. So basically, start at
the bottom right cell of the table and work left and then up to find
out how many draws it has been since each of the numbers from 1 to 49
have been drawn.

Thanks in advance.
Paul

Has anyone got any ideas please.

Thanks,

I will put something together over the weekend if possible.

Regards,
Paul

Counting When Last Occurance
 

On Wed, 15 Jul 2009 06:18:14 -0700 (PDT), Paul Black
wrote:

Hi everyone,

I have a table of lottery numbers where 6 balls are drawn each draw.
The table is from D3:i whatever and obviously grows constantly.
The draw number is in column B with the last draw at the bottom.
What I would like is to list the numbers from 1 to 49 starting in S3
and going down and next to each number have the number of draws since
that particular number has been drawn please. So basically, start at
the bottom right cell of the table and work left and then up to find
out how many draws it has been since each of the numbers from 1 to 49
have been drawn.

Thanks in advance.
Paul

If you have the number 1,2,3, ..., 49 in cells S3, S4, S5, ... S51 you
may try the following formula in cell T3:

=IF(OR(D$3:I$1000=S3),MAX(ROW(D$3:D$1000)*(D$3:D$1 000<""))-MAX(ROW(D$3:I$1000)*(MMULT(--(D$3:I$1000=S3),{1;1;1;1;1;1})0)),"newer
drawn")

Note: This is an array formula that has to be confirmed by
CTRL+SHIFT+ENTER rather than just ENTER.

Change the 1000 to suit the size of your data in columns D to I.

Copy the formula down until cell T51.

The numbers in column T now show the number of draws since the
corresponding numbers in column S were drawn.
0 means that the number was in the last draw.

If a number has never been drawn, the text "never drawn" is displayed
in column T.

Hope this helps / Lars-Åke

Counting When Last Occurance
 

On Jul 17, 1:26*pm, Lars-Åke Aspelin
wrote:
On Wed, 15 Jul 2009 06:18:14 -0700 (PDT),PaulBlack

wrote:
Hi everyone,

I have a table of lottery numbers where 6 balls are drawn each draw.
The table is from D3:i whatever and obviously grows constantly.
The draw number is in column B with the last draw at the bottom.
What I would like is to list the numbers from 1 to 49 starting in S3
and going down and next to each number have the number of draws since
that particular number has been drawn please. So basically, start at
the bottom right cell of the table and work left and then up to find
out how many draws it has been since each of the numbers from 1 to 49
have been drawn.

Thanks in advance.
Paul

If you have the number 1,2,3, ..., 49 in cells S3, S4, S5, ... S51 you
may try the following formula in cell T3:

=IF(OR(D$3:I$1000=S3),MAX(ROW(D$3:D$1000)*(D$3:D$1 000<""))-MAX(ROW(D$3:I$1*000)*(MMULT(--(D$3:I$1000=S3),{1;1;1;1;1;1})0)),"newer
drawn")

Note: This is an array formula that has to be confirmed by
CTRL+SHIFT+ENTER rather than just ENTER.

Change the 1000 to suit the size of your data in columns D to I.

Copy the formula down until cell T51.

The numbers in column T now show the number of draws since the
corresponding numbers in column S were drawn.
0 means that the number was in the last draw.

If a number has never been drawn, the text "never drawn" is displayed
in column T.

Hope this helps / Lars-Åke

Hi Lars-Åke,

Your formula works great thank you.

Regards,
Paul