Referencing the nth occurence of an entry

[Tom Regensburger]

I'm looking to count the number of rows from the top of a range to the row that is occupied by the nth occurrence of a piece of data.

Example:
I would like to know on which row the 2nd occurrence of Greg occurs.
Row # Entry
1 Greg
2 Diane
3 John
4 Greg

The data that I am working with is approximately 250 rows and the entry "Greg" could easily occur 15 or 20 times.

I've concluded that I can accomplish this by creating some dynamic ranges and using the OFFSET and ROWS functions.

However, it seems to me that there should be an easier way and I'm hoping that someone might be able to offer some guidance.

Thanks.

 

[fairwinds]

Hi,

Try:

=SMALL(IF(A1:A20=D2,ROW(A1:A20)),D3)

Confirmed with Ctrl + shift + enter.

Book1
	A	B	C	D	E
1	Greg
2	Diane		Name	Greg
3	John		Occurance	3
4	Greg		Row	5
5	Greg
6	Diane
7	John
8	Greg
Sheet2

 

[Aladin Akyurek]

I'm looking to count the number of rows from the top of a range to the row that is occupied by the nth occurrence of a piece of data.

Example:
I would like to know on which row the 2nd occurrence of Greg occurs.
Row # Entry
1 Greg
2 Diane
3 John
4 Greg

The data that I am working with is approximately 250 rows and the entry "Greg" could easily occur 15 or 20 times.

I've concluded that I can accomplish this by creating some dynamic ranges and using the OFFSET and ROWS functions.

However, it seems to me that there should be an easier way and I'm hoping that someone might be able to offer some guidance.

Thanks.

Book2
	A	B	C	D	E
1	Entry
2	Greg		Greg	2	4
3	Diane		Greg	3	7
4	John
5	Greg
6	Jon
7	Damon
8	Greg
9	Jon
10	Brian
Sheet1

E2:

=IF(COUNTIF($A$2:$A$10,C2)>=D2,SMALL(IF($A$2:$A$10=C2,ROW($A$2:$A$10)-ROW($A$2)+1),D2),"Does Not Exist")

which is confirmed with control+shift+enter then copied down.

This gives you the position of the target's Nth occurrence relative to the range of interest. If you want the native position, remove the -ROW($A$2)+1 bit from the formula.

 

[PaddyD]

& fwiw:

http://www.mrexcel.com/board2/viewtopic.php?t=158655

...has an explanation on what the row() bits are doing in formulas like the one Aladin posted.