Sequential Numbering Based on Data in Cells

[jharbold]

Hello,

I am attempting to do the following and would appreciate any help:

I have a spreadsheet that looks like the following
[TABLE="width: 500"]
<tbody>[TR]
[TD]ABC[/TD]
[TD]ABC-001[/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD]DEF-001[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]ABC-002[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]ABC-003[/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD]DEF-002[/TD]
[/TR]
</tbody>[/TABLE]

I manually enter in the information in the first column. In the second column, I need to create an equation that will automatically pull over the data in the first column, add a "-", and then create sequential numbering based on the values in column A. For example, in the third row, I need the equation to know that "ABC" is entered in the first column, so it needs to search above in the second column to see what the last "ABC" value was (in this case ABC-001), and then return "ABC-002" (001+1).

To make matters a bit more complicated, the values in the first column may not always be 3 characters as shown here, but the numbers and the letters in the second column will always be separated by a "-".

I appreciate the help!

 

[Fluff]

How about
=A2&"-"&TEXT(COUNTIF(A$2:A2,A2),"000")
and copied down

 

[jharbold]

This worked perfectly - thanks!

 

[Fluff]

You're welcome & thanks for the feedback