Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

In excel, is there an excel formula I can use to populate Column B (in my example below) that will return the last row number that contains a specific string, before the current row?

The data is not contiguous.
For example, the data may be:

   |  A (value)    B (prev recent instance)
----------------------------------------------
 1 |  Mike
 2 |  John
 3 |  Mike         1
 4 |  Tony         
 5 |  Mike         3
 6 |  John         2

I'll then want to expand it to have Column C to show the earliest instance.

It seems very similar to this question: What is an excel formula that will return me the last row number of a cell within a range that contains specific data?, however that question and answer requires the data to be contiguous.

Edit: I would like the answer to be a Worksheet Formula, however if you think it's more appropriate to implement via VBA, please include that also in your answer with pros and cons of each approach.

share|improve this question
    
This would be much easier (as well as more intuitive) to do using VBA. –  Breakthrough Jan 30 '12 at 12:46
    
Thanks for the suggestion Breakthrough, I've edited the question slightly to allow for this as an option so they can be compared. –  Turgs Jan 30 '12 at 13:24

2 Answers 2

up vote 1 down vote accepted

To find earliest instance, mentioned below is the formula:-

=LOOKUP(2,1/(A:A=A2),ROW(A:A))

For your better understanding, below is the screenshot

enter image description here

share|improve this answer

Assuming your data starts at A1 try this "array formula" in B2

=IF(COUNTIF(A$1:A1,A2),MATCH(2,1/(A$1:A1=A2)),"")

confirmed with CTRL+SHIFT+ENTER and copied down

or you can use this non-array version

=IF(COUNTIF(A$1:A1,A2),MATCH(2,INDEX(1/(A$1:A1=A2),0)),"")

In Excel 2007 or later you can simplify with IFERROR, i.e. array version becomes

=IFERROR(MATCH(2,1/(A$1:A1=A2)),"")

For "1st instance" row in column C try this formula in C1 copied down

=MATCH(A1,A$1:A1,0)

Note: the formulas don't strictly return "row numbers" but the relative row numbers for your data, i.e. row 1 of your data returns 1 etc.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.