Help: Find the last appearence of a number ?

[Mr.Nob0OOdy]

Hello !

Let's say I have ten rows filled with numbers on six columns, which represent the last ten lottery draws.
I need a formula to find out when the present number appeared last time in these draws.

Let's say the 11 draws is 23-37-45-9-1-39
On the other table I will get something like this:
0-3-1-8-7-2 which means:
23 has not been drawn at all in the last existent 10 draws;
37 has been drawn in the draw number 7 (or 3 draws ago);
45 has been drawn just in the last draw;
9 has been drawn in the second draw of those 10 existent draws;
1 has been drawn in the 3rd draw of those existent 10 draws;
and
39 has been drawn not in the last draw but in the one before the last one draw.

Moreover, the formula I need to do this should not count for the number position inside the rows but it should cont for the number's position from a row to another because the row itself represents a draw.

If someone could help me, this would be appreciated.

Thank you so much !

[martindwilson]

oh no not another lottery thing..there are plenty of on line lottery checkers

[Mr.Nob0OOdy]

I really need that. It is not lottery checker it is about frequency. Any help, please ?

[jason.b75]

Your thread title is misleading, please change it so that it describes your problem more accurately.

Frequency would be how often a number appeared, not when it last appeared.

[Mr.Nob0OOdy]

Just changed it. Could you help m, please ?

Thank you !

[jason.b75]

Kinda difficult to say exactly what will work without seeing the layout of your sheet.

Looking at the table with the last 10 draws, would the most recent be at the top or bottom of the table?

[Mr.Nob0OOdy]

The more recent should be at the bottom of the table.

[jason.b75]

Assuming that your table for last 10 draws is in A2:F11 and the 1st number of the 11th draw is in A13, if not adjust the ranges to suit, noting that the table range is absolute.

=MIN(IF($A$2:$F$11=A13,{10;9;8;7;6;5;4;3;2;1}))

Note that this is an array formula, copy the formula into the cell then hold down Shift and Ctrl and press enter. If the formula returns an error of #VALUE! then it means this has not been done correctly.

Once the formula is entered correctly, use the fill handle to copy it to the right for the remaining numbers.