Help with formula in Excel to Count Records in a set. 1

[awaria]

Here's an example of what I am trying to do.

Col 1. Col. 2 Col.3
abc x234
abc x578

def v400

ghi y700
ghi z801
ghi a309
ghi q400

Desired result is in Col. 3, where I would like to order the records in Col. 2 (1,2 for abc; 1 for def; and 1,2,3,4 for ghi and so-forth where there could be an unlimited number of record groupings in Col.2).

Any help would be greatly appreciated.

aw

 

[SkipVought]

hi,

ASSUMING that your DATA starts in ROW 2 and NOT ROW 1...
[tt]
=if(a2=a1,c1+1,1)
[/tt]
and copy down thru all rows of data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lionelhill]

an alternative, of which you should be aware:
=COUNTIF(A$1:A2, A2)
Put this in C2, and copy down as far as desired.

This is different to Skip's formula, in that it continues numbering after a row of abc's is interrupted by something else, while Skip's version will restart at 1.
i.e., the countif version will give you:
abc 1
abc 2
def 1
abc 3
The if(a2=a1) version will give you:
abc 1
abc 2
def 1
abc 1
Which behaviour you want depends on your situation.

From your posting it's not clear what role column B might play. If you wish to sort by column B, but retain groupings in column A, you need to use Data - Sort - Sort By column A, then by column B (the exact locations of the options vary from Excel2003 to Excel2010).