return row for nth occurrence of text in array

<SOLVED>

Hello,

I'll appreciate any help received. I've attached a small Excel 2007 document to demonstrate what I am trying to do and to provide data to ground a discussion.

I'd like to sort column A and return the row number for the first occurrence of the letter 'a' formulaically (B1). In B2 second cell, I'd like to return the row number for the second occurrence of 'a', and so forth.

So far I have not come up with a satisfying way of doing this and have not been able to find a post that has accomplished it. I've managed to do everything else without VBA so far, and would like to keep it that way if possible. I've tried combinations of Row(), Match(), Vlookup(), Countif(), and Indirect().

Thanks!


Greg

in b1
=MATCH("a",A1:A15,0)
in b2
=MATCH("A",INDIRECT("A"&B1+1&":a15"),0)+B1
drag down to b3 for 3rd a and so on

You mention sorting column A? this formula will work sorted or not to give successive instances, in B1

=SMALL(IF(A1:A100="a",ROW(A1:A100)),ROWS(B$1:B1))

confirmed with CTRL+SHIFT+ENTER

when instances are exhausted you get #NUM! error....you could revise the formula to prevent that if you want....

of course if the data is sorted the second occurence will just be 1 after the first and the third will be 1 after that etc......