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!

On Jun 7, 9:15*am, WA <W...@discussions.microsoft.com> wrote:
> 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

PERFECT! Thank you so much!
--
WA


"Javed" wrote:

> On Jun 7, 9:15 am, WA <W...@discussions.microsoft.com> wrote:
> > 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!
>
> =+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
> .
>