Counting When Last Occurance

[Paul Black]

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

 

[paul_black27]

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.

 

[Don Guillett]

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
(e-mail address removed)

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.

 

[paul_black27]

      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





Has anyone got any ideas please.

Thanks,

I will put something together over the weekend if possible.

Regards,
Paul

 

[Lars-Åke Aspelin]

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$1000)*(D$3$1000<>""))-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

 

[paul_black27]

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$1000)*(D$3$1000<>""))-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