Conditional numbering

[razaas]

What formula can I use to get the result in column A, as shown below:


Col-A Col-B

1 B-001 if it is unique

2 B-001 if it is repeated

1 B-003 if it is unique

2 B-003 if it is repeated

3 B-003 if repeated again


Thanks in advance.

 

[oldchippy]

Hi razaas,


Put this in column A and copy downwards


=COUNTIF($B$1:B1,B1)

 

[razaas]

Thanks a lot oldchippy.

 

[cyrilz]

Another related question :


What if I need sum count against 2 columns ?

[pre]
Col1 Col2 ID
A B AB001
A C AC001
A B AB002
...
[/pre]

Is there any cross-product function doing the job ?


Thanks in advance


Cyril Z.

 

[Hui]

Cyrilz

try this in C1 and copy down

=+CONCATENATE(A1,B1,TEXT(+SUMPRODUCT(1*(($A$1:A1)=A1)*(($B$1:B1)=B1)),"00#"))

 

[Clarity]

An alterntive if you have 2007 is the COUNTIFS formula:


=A1&B1&TEXT(COUNTIFS($A$1:A1,A1,$B$1:B1,B1),"00#")