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.