How to add a "running count" to cells with matching data

W

WA

I want to copy column A (below) into Column B and add a running count.
As long as column A is the same (repeat letters & numbers) - it keeps adding
the next number (1,2,3 . . . up to 99).
Example - Cols A & B, Rows 1-15. I have Col A. I need to create Col B:
Row ColumnA ColumnB
1 AK-1 AK-1-1
2 AK-1 AK-1-2
3 AK-1 AK-1-3
4 AK-2 AK-2-1
5 AK-2 AK-2-2
6 AK-3 AK-3-1
7 AK-1289 AK-1289-1
8 AK-1289 AK-1289-2
9 CA-1 CA-1-1
10 CA-1 CA-1-2
11 CA-2 CA-2-1
12 CA-2 CA-2-2
13 CA-2 CA-2-3
14 CA-375 CA-375-1
15 CA-375 CA-375-2
....
24 CA-375 CA-375-11

Column A will always be 2 letters, a "-" and 1 to 4 digits (ex: TX-1 up to
TX-9999)
Column B will always be a copy of Column A + a "-" and 1 to 2 digits (ex:
TX-1-1 to TX-9999-99).

What formula can I write to create Column B?

Thank you!
 
J

Javed

I want to copy column A (below) into Column B and add a running count.
As long as column A is the same (repeat letters & numbers) - it keeps adding
the next number (1,2,3 . . . up to 99).  
Example - Cols A & B, Rows 1-15.  I have Col A.  I need to create ColB:
Row     ColumnA ColumnB
1       AK-1    AK-1-1
2       AK-1    AK-1-2
3       AK-1    AK-1-3
4       AK-2    AK-2-1
5       AK-2    AK-2-2
6       AK-3    AK-3-1
7       AK-1289 AK-1289-1
8       AK-1289 AK-1289-2
9       CA-1    CA-1-1
10      CA-1    CA-1-2
11      CA-2    CA-2-1
12      CA-2    CA-2-2
13      CA-2    CA-2-3
14      CA-375  CA-375-1
15      CA-375  CA-375-2
...
24      CA-375  CA-375-11

Column A will always be 2 letters, a "-" and 1 to 4 digits (ex: TX-1 up to
TX-9999)
Column B will always be a copy of Column A + a "-" and 1 to 2 digits (ex:
TX-1-1 to TX-9999-99).

What formula can I write to create Column B?

Thank you!

=+A1&"-"&COUNTIF($A$1:A1,A1)

You need to copy it in b1 cell first then paste in beloww cells from
b1.(This required for right copy of absolute/relative addresss
 
W

WA

PERFECT! Thank you so much!
--
WA


Javed said:
=+A1&"-"&COUNTIF($A$1:A1,A1)

You need to copy it in b1 cell first then paste in beloww cells from
b1.(This required for right copy of absolute/relative addresss
.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top