Using Excel to find number of a duplicate in column

I'm trying to show the count of a duplicate within a gi开发者_运维技巧ven column. I can count how MANY duplicates there are within the column, but I want to know if this row is the first duplicate found, second, etc. Here's some sample data (sorted) and how I'd like it to appear:

1: ALPHA | 1
2: ALPHA | 2
3: DELTA | 1
4: ECHO  | 1
5: ECHO  | 2
6: ECHO  | 3
7: HOTEL | 1
8: HOTEL | 2

It doesn't matter if VBA is needed or it can be done in a formula, I just can't seem to make it work.

The 'duplicate number' of a given row is the number of entries equal to the current entry, in the range from the top of the column to the current row. This is actually easier to say in a formula than in English :)

If your example data is in two columns, and starts at A1, insert

=COUNTIF(A$1:A8, A8)

into B8, and copy all the way up.

You can use indirect to generate a reference from the results of other formulas, e.g.

=COUNTIF(INDIRECT("R1C"&(COLUMN()-1)&":R"&ROW()&"C"&(COLUMN()-1),FALSE), A1)