When a number has been drawn last time ?

[Mr.Nob0OOdy]

I want to get the number last draw in excel, as follow:

Please Login or Register  to view this content.

On the other excel sheet I have all the numbers from 1 to 49 and for each one of them I have a number which say to me when each number has been last time drawn.
In this example we should get:
No. 12 ---> drawn last time 2 draws ago.
for No. 25 ---> we should get that it has been drawn last time 3 draws ago (in the first draw if you see better).
and so on.
Basically what the needed formula should do ?
It should search back from the last draw until it find the number it is searching for. Then it should get the row number where it finds that number. Row number is the number of draw itself. (position doesn't matter, it matters only the draw not the position of each numbers) Then it should make the difference between the last draw which is represented by its row number - the row number where it finds the number it searched for. This = to "last time has been drawn".
Please, help me with this.

Thank you so much !

[zbor]

Nob0OOdy can't win a lottery

[Mr.Nob0OOdy]

cmin, please, I need this formula. pleeeease !
Thank you so much !

[martindwilson]

arrange the data with newest first then match down each column and take the minimum of those matches
sort of
=min(iferror(match(49,a1:a1000,0),"0"),iferror(match(49,b1:b1000,0),"0"),iferror(match(49,c1:c1000,0),"0"),iferror(match(49,d1:d1000,0),"0"),iferror(match(49,e1:e1000,0),"0"),iferror(match(49,f1:f1000,0),"0"))

[Mr.Nob0OOdy]

Could you make it to work without to re-arrange the data with newest first, please ?

Thank you !

[martindwilson]

no, why its easy enough, just copy your data to a new sheet
number down in say column g 1.2.3.... then sort by col g descending
and as its for a lottery calculator i really cant be bothered to try
but you can reverse the list on another sheet with a few functions which you can match against

[Mr.Nob0OOdy]

Look, I did like you have said but I don't understand how to use your formula. Look what I did in the file added as attachment, please.
Wat I did wrong ?

Thank you in advance !

[martindwilson]

oops
=min(iferror(match(49,a1:a1000,0),10^99),iferror(match(49,b1:b1000,0),10^99),iferror(match(49,c1:c1000,0 ),10^99),iferror(match(49,d1:d1000,0),10^99),iferror(match(49,e1:e1000,0),10^99),iferror(match(49,f1:f1000 ,0),10^99))
i dont know where "0"came from and it should be a number bigger that your last row!

[Mr.Nob0OOdy]

oops
=min(iferror(match(49,a1:a1000,0),10^99),iferror(match(49,b1:b1000,0),10^99),iferror(match(49,c1:c1000,0 ),10^99),iferror(match(49,d1:d1000,0),10^99),iferror(match(49,e1:e1000,0),10^99),iferror(match(49,f1:f1000 ,0),10^99))
i dont know where "0"came from and it should be a number bigger that your last row!

tested it on the file "test.xlsx" I just uploaded and it still doesn't work...

[martindwilson]

your not having a lot of luck,how do you expect to win the lottery?
works fine for me it shows 1e+99 thats coz 49 is not found
conditional format to hide it would be easiest

[Mr.Nob0OOdy]

It works now.
Thank you !

[thread solved]

[Spencer101]

your not having a lot of luck,how do you expect to win the lottery?

Drink came out my nose when I read that..