Increment alphanumeric column value by 1

[Teckel_luv]

Hello,

I'm using Excel 2007 on a Windows XP box for creating QA test cases. I looked through the forum and haven't found an answer to my question.

I need to be able to auto increment my test case ID in column A by 1 when I hit 'Enter' or click on the next row.
I.E. The test case ID value in A4 = 'VM_TEST_001' and I want it to increment to 'VM_TEST_002' in A5 and 'VM_TEST_003' in A6, etc. w/o having to drag the Autofill cross down several hundred rows every time I need to add a new row for another test case.

Any help would be appreciated!
Thanks in advance!

 

[Jon3sy]

Thanks for that - here is the example of what I would like it to be - scroll down to bottom to see how the numbering changes depending on the type in column B - and then resumes the numbering order by type each time the type is changed.
[TABLE="width: 336"]
<TBODY>[TR]
[TD]A1315-PCN-001</SPAN>[/TD]
[TD]PCN</SPAN>[/TD]
[/TR]
[TR]
[TD]A1315-PCN-002</SPAN>[/TD]
[TD]PCN</SPAN>[/TD]
[/TR]
[TR]
[TD]A1315-PCN-003</SPAN>[/TD]
[TD]PCN</SPAN>[/TD]
[/TR]
[TR]
[TD]A1315-PCN-004</SPAN>[/TD]
[TD]PCN</SPAN>[/TD]
[/TR]
[TR]
[TD]A1315-PCN-005</SPAN>[/TD]
[TD]PCN</SPAN>[/TD]
[/TR]
[TR]
[TD]A1315-PCN-006</SPAN>[/TD]
[TD]PCN</SPAN>[/TD]
[/TR]
[TR]
[TD]A1315-PCN-007</SPAN>[/TD]
[TD]PCN</SPAN>[/TD]
[/TR]
[TR]
[TD]A1315-PCN-008</SPAN>[/TD]
[TD]PCN</SPAN>[/TD]
[/TR]
[TR]
[TD]A1315-PCN-009</SPAN>[/TD]
[TD]PCN</SPAN>[/TD]
[/TR]
[TR]
[TD]A1315-PCN-010</SPAN>[/TD]
[TD]PCN</SPAN>[/TD]
[/TR]
[TR]
[TD]A1315-PCN-011</SPAN>[/TD]
[TD]PCN</SPAN>[/TD]
[/TR]
[TR]
[TD]A1315-PCN-012</SPAN>[/TD]
[TD]PCN</SPAN>[/TD]
[/TR]
[TR]
[TD]A1315-PCN-013</SPAN>[/TD]
[TD]PCN</SPAN>[/TD]
[/TR]
[TR]
[TD]A1315-PCN-014</SPAN>[/TD]
[TD]PCN</SPAN>[/TD]
[/TR]
[TR]
[TD]A1315-PCN-015</SPAN>[/TD]
[TD]PCN</SPAN>[/TD]
[/TR]
[TR]
[TD]A1315-BCN-001</SPAN>[/TD]
[TD]BCN</SPAN>[/TD]
[/TR]
[TR]
[TD]A1315-CCN-001</SPAN>[/TD]
[TD]CCN</SPAN>[/TD]
[/TR]
[TR]
[TD]A1315-PCN-016</SPAN>[/TD]
[TD]PCN</SPAN>[/TD]
[/TR]
[TR]
[TD]A1315-PCN-017</SPAN>[/TD]
[TD]PCN</SPAN>[/TD]
[/TR]
[TR]
[TD]A1315-BCN-002</SPAN>[/TD]
[TD]BCN</SPAN>[/TD]
[/TR]
[TR]
[TD]A1315-BCN-003</SPAN>[/TD]
[TD]BCN</SPAN>[/TD]
[/TR]
[TR]
[TD]A1315-CCN-002[/TD]
[TD]CCN</SPAN>[/TD]
[/TR]
[TR]
[TD]A1315-PCN-018</SPAN>[/TD]
[TD]PCN</SPAN>[/TD]
[/TR]
[TR]
[TD]A1315-PCN-019</SPAN>[/TD]
[TD]PCN</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL></COLGROUP>[/TABLE]

 

[RonB1111]

Use Column A as a helper column and enter this formula in A4 and copy down:
=IF(B4="","",$B$2&"-"&B4)

Then in Column C, enter this formula in C4 and copy down:
=IF(B4="","",A4&"-"&TEXT(COUNTIF(A$4:A4,A4),"000"))

If you need the final numbered results in Col A, then put the 1st formula in any column available, and then put the 2nd formula in Col A but adjust the Col A references in the formula to whatever column you placed the 1st formula.

 

[Jon3sy]

Perfect - you're a legend - thank you so much - this will be a useful formula for future unique incremental auto-numbering key id creation without use of script.

 

[RonB1111]

You're welcome. Thank you for providing feedback.

 

[Jon3sy]

The auto-numbering above works perfect - however I am converting some data over from another system and have to retain the original historic numbers issued which is fine but from the last record in the old system I want to re-start the auto-numbering from that last number so how do I change the formula to do this - below is an example of the data and the column on the far right is the orginal number - so the second instance of EHLH-2337 (in column 3) should be EHLH-2338, then 2339 - the last entry in the 3rd column should be EHLH-2340 - hope I have explained well enough and given good enough example - thanks so much in advance

[TABLE="width: 400"]
<TBODY>[TR]
[TD]Hose</SPAN>[/TD]
[TD]EHLH</SPAN>[/TD]
[TD]EHLH-2335</SPAN>[/TD]
[TD]EHL2335</SPAN>[/TD]
[/TR]
[TR]
[TD]Hose</SPAN>[/TD]
[TD]EHLH</SPAN>[/TD]
[TD]EHLH-2336</SPAN>[/TD]
[TD]EHL2336</SPAN>[/TD]
[/TR]
[TR]
[TD]Hose</SPAN>[/TD]
[TD]EHLH</SPAN>[/TD]
[TD]EHLH-2337</SPAN>[/TD]
[TD]EHL2337</SPAN>[/TD]
[/TR]
[TR]
[TD]Hose</SPAN>[/TD]
[TD]EHLH</SPAN>[/TD]
[TD]EHLH-2337</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Hose</SPAN>[/TD]
[TD]EHLH</SPAN>[/TD]
[TD]EHLH-2337</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Calibration</SPAN>[/TD]
[TD]EHLC</SPAN>[/TD]
[TD]EHLC-0001</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Gauge</SPAN>[/TD]
[TD]EHLG</SPAN>[/TD]
[TD]EHLG-0001</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Hose</SPAN>[/TD]
[TD]EHLH</SPAN>[/TD]
[TD]EHLH-0619</SPAN>[/TD]
[TD] [/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>[/TABLE]

 

[Jim885]

Thanks for that - here is the example of what I would like it to be - scroll down to bottom to see how the numbering changes depending on the type in column B - and then resumes the numbering order by type each time the type is changed.
[TABLE="width: 336"]
<tbody>[TR]
[TD]A1315-PCN-001
[/TD]
[TD]PCN
[/TD]
[/TR]
[TR]
[TD]A1315-PCN-002
[/TD]
[TD]PCN
[/TD]
[/TR]
[TR]
[TD]A1315-PCN-003
[/TD]
[TD]PCN
[/TD]
[/TR]
[TR]
[TD]A1315-PCN-004
[/TD]
[TD]PCN
[/TD]
[/TR]
[TR]
[TD]A1315-PCN-005
[/TD]
[TD]PCN
[/TD]
[/TR]
[TR]
[TD]A1315-PCN-006
[/TD]
[TD]PCN
[/TD]
[/TR]
[TR]
[TD]A1315-PCN-007
[/TD]
[TD]PCN
[/TD]
[/TR]
[TR]
[TD]A1315-PCN-008
[/TD]
[TD]PCN
[/TD]
[/TR]
[TR]
[TD]A1315-PCN-009
[/TD]
[TD]PCN
[/TD]
[/TR]
[TR]
[TD]A1315-PCN-010
[/TD]
[TD]PCN
[/TD]
[/TR]
[TR]
[TD]A1315-PCN-011
[/TD]
[TD]PCN
[/TD]
[/TR]
[TR]
[TD]A1315-PCN-012
[/TD]
[TD]PCN
[/TD]
[/TR]
[TR]
[TD]A1315-PCN-013
[/TD]
[TD]PCN
[/TD]
[/TR]
[TR]
[TD]A1315-PCN-014
[/TD]
[TD]PCN
[/TD]
[/TR]
[TR]
[TD]A1315-PCN-015
[/TD]
[TD]PCN
[/TD]
[/TR]
[TR]
[TD]A1315-BCN-001
[/TD]
[TD]BCN
[/TD]
[/TR]
[TR]
[TD]A1315-CCN-001
[/TD]
[TD]CCN
[/TD]
[/TR]
[TR]
[TD]A1315-PCN-016
[/TD]
[TD]PCN
[/TD]
[/TR]
[TR]
[TD]A1315-PCN-017
[/TD]
[TD]PCN
[/TD]
[/TR]
[TR]
[TD]A1315-BCN-002
[/TD]
[TD]BCN
[/TD]
[/TR]
[TR]
[TD]A1315-BCN-003
[/TD]
[TD]BCN
[/TD]
[/TR]
[TR]
[TD]A1315-CCN-002
[/TD]
[TD]CCN
[/TD]
[/TR]
[TR]
[TD]A1315-PCN-018
[/TD]
[TD]PCN
[/TD]
[/TR]
[TR]
[TD]A1315-PCN-019
[/TD]
[TD]PCN
[/TD]
[/TR]
</tbody>[/TABLE]

To get the above;
Assuming that A1315 is constant (or whatever alpha-numeric code begins the sequence.
Give this formula a try. It doesn't need a helper column.

=IF(B4="","","A1315-"&B4&"-"&TEXT(COUNTIF(B$4:B4,B4),"000"))

 

[RonB1111]

In your new example, the new prefix is 4 characters but the old prefix is 3 characters. Further, the 1st 3 characters of the new prefix are used in more than one 4 character prefix. So if an old part number begins EHL, how do you know that it is associated with only EHLH and not with EHLC or EHLG? Also, in your sample data, I presume that EHLH must have occurred 618 times earlier in the data in order for the last entry to come out to EHLH-0619. Please let me know if I'm misinterpreting this.

 

[Jon3sy]

This is a completely different set of data all together but am using the same formula to create an auto-numbering system code so in this case the prefix may be even 5 characters in some instances but don't believe it impacts on the forumla - and yes EHL will always be the first 3 characters and yes you are correct EHLH has occurred 618 times earlier in the data. EHLH2337 is the last number in the "old" system so I need the new numbering system to start incrementing automatically from that number. I tried different variations to the formula but came unstuck and decided you guys are the experts and extremely efficient and quick at responding. Thanks

 

[RonB1111]

The problem is I don't see a way to associate the old part number EHL2337 with a specific new prefix - eg: the new prefix's are EHLH (which you want it associated to) but also ELHC and ELHG which you don't want it associated to. In your sample data the "description" is hose, but my experience is description fields are never consistent (eg: might be "hose" for most hoses but some might be "hose-3/4 inch" or other variations in spelling). Is there some other field available that could be the key to linking?

 

[Jon3sy]

For the field with hose in, a validation list is used so there are only 5 or 6 different types and I used the "helper" part of your original formula to build the prefix part of the second formula based on that column - so in my mind that part works perfectly, it is the auto-numbering allocation part of the formula where I want it to start numbering from 2338 so I would expect the change I need to make needs to be in that part of the countif formula. I forgot to mention the numbering up to 2337 is not a formula as they are hard copied into the spreadsheet as we cannot change them for historical audit purposes - hence why with the above example it reverts to EHLH-619 after the EHLH2337 (and correctly so for the formula and number of hose items captured). But I actually want the formula to start auto-numbering from that point forward - I just don't know how to write the countif bit to make it start from 2338 (for the hoses only) and continue on, so it may require two countif statements to accomodate everything else starting at 0001. I hope I haven't muddied the waters too much and have clarified for you? Cheers