How can i to return the row number of the last repeated text in a column?

Hello,

I want to get the following:

B C
John
Maria
Nelson
John 1
Alex
George
John 4

In other words: I need an formula on Cx that display the row number of last occurence of text in Bx prior to Bx.

Can anyone help me?

Affirmative.

I'm using an Portuguese version of Excel. I made a few modifications on your formula that worked fine: =SE(CONT.SE($B$1:B2;B2)>1;CORRESP(B2;$B$1:B2;0)+1;"") .

Thank You very much!

Originally Posted by Andrew-R

In C1:

Edited to add: That will always return the row of the first occurrence, sorry, what you probably want is:

=IF(COUNTIF($B$1:B1,B1)>1,MAX(INDEX(ROW($B$1:B1)*($B$1:B1=B1)*(ROW($B$1:B1)<ROW(B1)),0)),"")

Affirmative, in my previous post I was wrong. Seeing your edit now I found the correct answer:

a) Excel 2010 PT: =SE(CONT.SE($A$1:A1;A1)>1;MÁXIMO(ÍNDICE(LIN($A$1:A1)*($A$1:A1=A1)*(LIN($A$1:A1)<LIN(A1));0));"")

b) Excel 2010 EN: =IF(COUNTIF($A$1:A1,A1)>1,MAX(INDEX(ROW($A$1:A1)*($A$1:A1=A1)*(ROW($A$1:A1)<ROW(A1)),0)),"")

All correct??

Thanks!

Looks good, although my Portuguese is ... well, non-existent

Originally Posted by Andrew-R

Looks good, although my Portuguese is ... well, non-existent

Looks bad, Brazil is offering many job & business opportunities for foreigners.

Marking topic as solved.

Thanks!

There's a slim chance I'll get to visit Brazil for the 2016 Olympics. In the meantime, happy to help.