Increment alphanumeric column value by 1

Teckel_luv

New Member
Joined
May 23, 2012
Messages
4
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!
:confused:
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,668
How far down column A do you want to autofill? Will the start always be in A4?
 

RonB1111

Well-known Member
Joined
Nov 28, 2011
Messages
2,277
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

New Member
Joined
May 23, 2012
Messages
4
@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! :mad:

@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

Well-known Member
Joined
Nov 28, 2011
Messages
2,277
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

New Member
Joined
May 23, 2012
Messages
4
@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………:eek:
 

RonB1111

Well-known Member
Joined
Nov 28, 2011
Messages
2,277
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.
 

Jon3sy

Board Regular
Joined
Jan 30, 2013
Messages
81
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

Well-known Member
Joined
Nov 28, 2011
Messages
2,277
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?
 

Forum statistics

Threads
1,082,360
Messages
5,364,931
Members
400,815
Latest member
Joaquin Phoenix

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top