Sequence Number

[daniel]

Does anybody know of a way I can out a sequence number in
a column based on a code in another coumn and reverts to
1 everytime it comes across a new ID code for instance
xxxxx 1
xxxxx 2
xxxxx 3
xxxxx 4
zzzzz 1
zzzzz 2
ccccc 1
ccccc 2
ccccc 3

Can anbody help?

 

[Frank Kabel]

Hi
try the following (column A has your code and column B stores your
sequence numbers):
1. In cell B1 enter '1' (as number)
2. in Cell B2 enter the following array formula (entered with
CTRL+SHIFT+ENTER):
=IF(A2<>"",MAX(IF($A$1:A1=A2,$B$1:B1))+1)
copy this down

 

[Bob Phillips]

Or an alternative,

in B1 enter

=COUNTIF($A$1:A1,A1)

aand just copy down

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Michael Bednarek]

Does anybody know of a way I can out a sequence number in
a column based on a code in another coumn and reverts to
1 everytime it comes across a new ID code for instance
xxxxx 1
xxxxx 2
xxxxx 3
xxxxx 4
zzzzz 1
zzzzz 2
ccccc 1
ccccc 2
ccccc 3

The obvious answer:
in cell B2: =IF(A2=A1,B1+1,1)
assuming your data starts in A2, and A1 <> A2.
Then Fill Down (Ctrl+D) as required.