HELP: Find last appearance of a number in excel ?

[Mr.Nob0OOdy]

Hi !

I have this file in excel test.xlsx. As you can see it search for every given number (in column H) and returns its last appearance in column I.
For doing this it uses this formula:

Please Login or Register  to view this content.

and it works perfectly.

Now I am trying to get the last appearance of each number but this time horizontally and not vertically, and a little different.

Let's say I have these numbers which represents lottery draws:

31 27 19 12 38 13
15 5 40 32 3 25
40 19 36 3 25 33
6 35 11 37 5 21
38 25 36 18 6 28
25 7 39 38 4 21
20 35 8 29 5 28

What I want is to get another table with the last appearance for each number drawn in each lottery draw.

e.g. let's say for table above i should get this table:

11 2 15 81 71 32
37 13 73 88 25 99
76 40 34 23 83 2
72 27 62 31 91 2
6 82 60 39 98 85
9 12 15 78 24 72
55 80 50 30 55 15

Which means that the number 31 from above first table appeared last time 11 draws ago (valued shown in this last table). After that we see value 2 which means that number 27 from the first table appeared last time just 2 draws ago. And so on, for each number. It shows just when each number appeared last time and that's all.

I tried to do this into the table I added as attachment but it doesn't work. I used this formula:

Please Login or Register  to view this content.

Which means it looks for the number below the present draw and in each draw row by row. It is not working.
Please check attachments and please give to me some help with this problem.

Thank you so much !

[NBVC]

Are those samples in your results table correct? I don't see a 31 11 rows prior to first row?

If I understand correctly try:

=MIN(IF($B3:$G$995=B2;ROW($A3:$A$995)-ROW($A3)+1))

confirmed with CTRL+SHIFT+ENTER and copied across and down

[Mr.Nob0OOdy]

It is working.
Thank you o much !

[Mr.Nob0OOdy]

Actually I lost that Excel file and I can't make it to work as I wanted to. Could someone re-create that working Excel file for me and re-upload it here, please ?

Thank you so much !

[Mr.Nob0OOdy]

Marked as unsolved (temporary).

[FDibbins]

you can just click on the lick here and download it yourself

[Mr.Nob0OOdy]

you can just click on the lick here and download it yourself

Not that one uploaded by myself, I need the modified working version of that which NBVC says above using that formula. I tried that formula which he said but I can't make it to work at all. Maybe someone will upload that working version, please.

Thanks in advance !

[FDibbins]

That's the only uploaded file on this thread?

edit: NBVC's formula is an array formula and need's to be entered with CTRL SHIFT enter

Also, change the ; to , (its a regional thing)

I tried his formula on your file and got an asnwer of 21

[Mr.Nob0OOdy]

That's the only uploaded file on this thread?

Both files uploaded by myself in the first post do not work.
I want this one:

Are those samples in your results table correct? I don't see a 31 11 rows prior to first row?

If I understand correctly try:

=MIN(IF($B3:$G$995=B2;ROW($A3:$A$995)-ROW($A3)+1))

confirmed with CTRL+SHIFT+ENTER and copied across and down

[bebo021999]

Upload again with NVBC' formula

[Mr.Nob0OOdy]

Thanks !
[thread solved now] :D