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