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!

 

[JoeMo]

How far down column A do you want to autofill? Will the start always be in A4?

 

[RonB1111]

Enter this formula in A4 and copy down to row 1002 (or at least no further than 1002).

=IF(B4="","","VM_TEST_"&TEXT(ROW()-3,"000"))

The id number will display as soon as you enter data in column B of that row.

 

[Teckel_luv]

@JoeMo: It could start in A3, there seems to be at least 2 rows of header info at the top of each worksheet. I don't know how far down I need to go, it depends on the number of test cases I write. However, at the end of each day as I discover more and may need to insert lines to keep certain content together, I'm getting tired of having to renumber the first column using the Autofill option as I may miss a row and that makes my managers unhappy!

@RonB1111: Thanks for your formula, it does work; however, I guess I'll always need to copy the formula to any new row that I insert. I thought there was a script to auto-number when I insert a new row, but that may take a lot more coding than I originally thought. Thanks for your help!

 

[RonB1111]

Will you be inserting new rows between existing rows, or simply be starting on a new row after the last data entry row? In my example you'd copy the formula down 1 time for 999 rows. Then each time you enter data in the next blank row of column B, the ID number will automatically display. If you're going to be inserting rows between existing data rows then you're right, but inserting rows would also change the ID number of any data below the newly inserted row. If you could explain the situations you'd be inserting rows maybe I can come up with another alternative.

 

[Teckel_luv]

@RonB1111: Sorry I should have given you a better example. So, the test cases are grouped together depending on the area of the application we are testing. For example, I have several test cases built around the log-in process. Next in the spreadsheet, I have test cases for data entry for a certain menu feature.

If in the process of testing the application I discover that I need to add another test case for a new test discovered during the log-in process, I would go back to that grouping and insert a row for the new test case pertaining to this feature.

The particular place I’m working at for this project, prefers to keep their test cases grouped together depending on the part of the application we are testing. Thus, the need to add rows in-between existing content which is causing the issues I’m experiencing in trying to keep a sequential number in the test case ID. Hope this helps………

 

[RonB1111]

OK. Not sure I have a "good" solution for you, but here are some thoughts. When you insert a row, could you make the new id a variation of the original id. So if the original id was VM_TEST_123, the new one could be something like VM_TEST_123-01. If so, I'd suggest a conditional formatting solution rather than my earlier formula. Specifically, drag VM_TEST_001 down to fill in the 1st 999 cells in column A, then use conditional formatting to make their font color white (ie: invisible) until something is entered in the cell next to it in Col B. The conditional format formula for Col A would be simply =B1="". Then when you insert a row, copy/paste the id # from the cell above and edit it to add a -01 or the like. This way the id#'s will never change and you'd also be able to do sorting on the database.

 

[Teckel_luv]

OK, thanks for that thought. I'll give that a try!
Cheers!

 

[Jon3sy]

Hi this is first time posting to any forum so hopefully I get this right so someone understands my request. I used the above base formula RonB1111 posted and it worked beautifully for creating an auto-numbering unique key ID - however as part of the formula is a concatenation of 2 fields of alphanumeric data + autonumber when the 2nd category type changes I need the formula to restart the numbering sequence from 1 and also remember the previous last number when a further corresponding category commences again. Here is what I used - formula =IF(B4="","",$B$2&"-"&B4&"-"&TEXT(ROW()-3,"000")) - the B4 (and below) column data is where the category can change at any given time a new category of information is added (at this stage of 3 types) - below is the 2-column list example - appreciate if formula could be written to manage this (or you may recommended to put into macro code) - many thanks in advance.

A1234-PCN-001</SPAN>	PCN</SPAN>
A1234-PCN-002</SPAN>	PCN</SPAN>
A1234-PCN-003</SPAN>	PCN</SPAN>
A1234-PCN-004</SPAN>	PCN</SPAN>
A1234-PCN-005</SPAN>	PCN</SPAN>
A1234-PCN-006</SPAN>	PCN</SPAN>
A1234-PCN-007</SPAN>	PCN</SPAN>
A1234-PCN-008</SPAN>	PCN</SPAN>
A1234-PCN-009</SPAN>	PCN</SPAN>
A1234-PCN-010</SPAN>	PCN</SPAN>
A1234-PCN-011</SPAN>	PCN</SPAN>
A1234-PCN-012</SPAN>	PCN</SPAN>
A1234-PCN-013</SPAN>	PCN</SPAN>
A1234-PCN-014</SPAN>	PCN</SPAN>
A1234-PCN-015</SPAN>	PCN</SPAN>
A1234-BCN-016</SPAN>	BCN</SPAN>
A1234-CCN-017</SPAN>	CCN</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>

 

[RonB1111]

Jon3sy, your formula would produce the example results you've shown. Specifically, if A1234 was keyed into cell B2, and your formula was entered in A4 and then copied down, and you manually type in PCN in B4, PCN in B5, etc., the displayed results beginning in A4 thru A20 would be as you've shown. Would you explain a little further what is not working for you and perhaps show it an example of what you would like the results to look like?