FIRST & LAST occurence

[hsandeep]

B2:B9999 contains NAMES
C2:C9999 contains values

Output required (D & E)
D2=First Occurrence of the C column (matching B)
E2=Last Occurrence of the C column (matching B)

How to accomplish?
Thanks in advance.
I am using Excel 2007

Sheet1

	B	C	D	E
Peter
James
England
Peter
England
England

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]55[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]88[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]77[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]88[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]88[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]99[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]77[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]77[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]77[/TD]

</tbody>

 

[hsandeep]

If there is a possibility that the range in column B greater (eg. B2:B20), then I suggest the following formula in cell G2

=SUMPRODUCT((B2:B20<>"")/COUNTIF(B2:B20,B2:B20&""))

This formula ignore blank cells

navic, will your formula check for 'complete' B column(B2:B99999) AND also ignore 'blank cells' of B column?

 

[hsandeep]

Can there be a formula for above requirement which checks for 'complete' B column(B2:B99999) AND also ignore 'blank cells' of B column, if any?