How to add sequence number within groups of repeating values

[esprit_y2k]

The Problem


SomeColumn
---------
A B C
1 101
2 101
3 102
4 102
5 102
6 102
7 103
8 104
9 104


and you want to apply a sequence number within each group of numbers, starting at 1 again for each instance of a new group, so your resulting data will look like this;


Some Column Sequence
------------------
A B C
1 101 1
2 101 2
3 102 1 )
4 102 2 ) An ascending sequence number is created
5 102 3 ) for each group of numbers e.g. '102'
6 102 4 )
7 103 1
8 104 1
9 104 2

Question.
How do I use a formulae to create the sequence number rather than typing one by one or dragging it down?

 

[BrianB]

Try this.
Say your numbers are in cell A1 down.
Copy this into another column from Row 1 down
=COUNTIF($A$1:A1,A1)

 

[daniels012]

BrianB,
That is so awesome and yet so simple. I had a similar issue on somethng I was working on.

I am very impressed!
Michael

 

[esprit_y2k]

thx

thx a lot... took me almost one week to figure it out...

 

[dggrlrch]

@BrianB - what a lifesaver! Such a simple formula to get group by sequential numbering. It would've taken multiple lines of SAS code to get this!