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!

 

[RonB1111]

Here's where I'm confused. Based on your description and sample data, it sounds like in the old numbering system, any part # with an "EHL" prefix is a "hose" (and the EHL prefix is only hoses). In the new part numbering system, all hoses have the prefix "EHLH" (and EHLH is only hoses). Also in the new system the 1st 618 parts beginning with EHLH are to use a sequential numbers from 001 to 618. Then the next 3 instances will get the old numbers of 2335, 2336 & 2337 respectively, and the next 2 instances after that get 2338 & 2339, and then the last instance reverts back to the new sequence which would be 619. How did you determine that 5 part numbers in the middle of the list get the old numbering scheme while the ones before and after those 5 get the new numbering scheme? Is there perhaps another column with information that tells you that?

 

[Jon3sy]

Sorry, I only gave you a small sample of the data so the 1st 618 rows in the spreadsheet are in the old numbering format and only relate to hoses (that has gaps and is not sequential at the beginning so hence why I just copied and pasted as "hard-wired" numbers) but so as not to end up with double-up numbers down the track, want to start the numbering in the new format (for hoses only) at EHLH2338. Does that help?

 

[RonB1111]

Unfortunately that doesn't help. Maybe if you posted a larger sample of data. And enter the expected results from the formula you're looking for in the last column.

 

[Jon3sy]

Unfortunately that doesn't help. Maybe if you posted a larger sample of data. And enter the expected results from the formula you're looking for in the last column.

Below is the data set again - this time I have added the expected result in the last column - the first 616 rows of data up to EHLH-2337 have the test report # hard-coded, (instead of by your formula) so the formula now needs to start the counting and auto-increment from EHLH-2338 (this effectively becoming the "1st" number in the hose sequence, regardless of the row number) - hope this helps and makes sense this time?

Certification Type</SPAN>	Test Type Code</SPAN>	Test Report #</SPAN>	Expected result</SPAN>
Hose</SPAN>	EHLH</SPAN>	EHLH-2335</SPAN>	EHLH-2335</SPAN>
Hose</SPAN>	EHLH</SPAN>	EHLH-2336</SPAN>	EHLH-2336</SPAN>
Hose</SPAN>	EHLH</SPAN>	EHLH-2337</SPAN>	EHLH-2337</SPAN>
Hose</SPAN>	EHLH</SPAN>	EHLH-0617</SPAN>	EHLH-2338</SPAN>
Hose</SPAN>	EHLH</SPAN>	EHLH-0618</SPAN>	EHLH-2339</SPAN>
Metal Fitting</SPAN>	EHLMF</SPAN>	EHLMF-0001</SPAN>	EHLMF-0001</SPAN>
Metal Fitting</SPAN>	EHLMF</SPAN>	EHLMF-0002</SPAN>	EHLMF-0002</SPAN>
Metal Fitting</SPAN>	EHLMF</SPAN>	EHLMF-0003</SPAN>	EHLMF-0003</SPAN>
Metal Fitting</SPAN>	EHLMF</SPAN>	EHLMF-0004</SPAN>	EHLMF-0004</SPAN>
Metal Fitting</SPAN>	EHLMF</SPAN>	EHLMF-0005</SPAN>	EHLMF-0005</SPAN>
Metal Fitting</SPAN>	EHLMF</SPAN>	EHLMF-0006</SPAN>	EHLMF-0006</SPAN>
Metal Fitting</SPAN>	EHLMF</SPAN>	EHLMF-0007</SPAN>	EHLMF-0007</SPAN>
Metal Fitting</SPAN>	EHLMF</SPAN>	EHLMF-0008</SPAN>	EHLMF-0008</SPAN>
Safety Valve</SPAN>	EHLSV</SPAN>	EHLSV-0001</SPAN>	EHLSV-0001</SPAN>
Valve</SPAN>	EHLV</SPAN>	EHLV-0001</SPAN>	EHLV-0001</SPAN>
Valve</SPAN>	EHLV</SPAN>	EHLV-0002</SPAN>	EHLV-0002</SPAN>

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

 

[RonB1111]

This formula assumes that each Test Code Type in Col B is grouped together (ie: all EHLH's are in sequential rows, all EHLMF's in sequential rows, etc). Also assumes data starts in row 2.

=B2&"-"&TEXT(IF(COUNTIF(B$2:B2,B2)=VALUE(RIGHT(C2,4)),COUNTIF(B$2:B2,B2),IF(B2=B1,VALUE(RIGHT(F1,4))+1,RIGHT(C2,4))),"0000")

 

[Jon3sy]

Thanks RonB1111 but I don't think will work because things will not be grouped together in sequential rows going forward - this is only for the first block of the different groupings because I am merging the data from 5 different spreadsheets into one and inspections numbers up to what is in this example have been issued in the "old" system but now need it to start auto-numbering from this point forward - here is another sample of data
- thanks for your patience.

Certification Type</SPAN>	Test Type Code</SPAN>	Test Report #</SPAN>	Expected result</SPAN>
Hose</SPAN>	EHLH</SPAN>	EHLH-2335</SPAN>	EHLH-2335</SPAN>
Hose</SPAN>	EHLH</SPAN>	EHLH-2336</SPAN>	EHLH-2336</SPAN>
Hose</SPAN>	EHLH</SPAN>	EHLH-2337</SPAN>	EHLH-2337</SPAN>
Hose</SPAN>	EHLH</SPAN>	EHLH-0617</SPAN>	EHLH-2338</SPAN>
Hose</SPAN>	EHLH</SPAN>	EHLH-0618</SPAN>	EHLH-2339</SPAN>
Metal Fitting</SPAN>	EHLMF</SPAN>	EHLMF-0001</SPAN>	EHLMF-0001</SPAN>
Metal Fitting</SPAN>	EHLMF</SPAN>	EHLMF-0002</SPAN>	EHLMF-0002</SPAN>
Metal Fitting</SPAN>	EHLMF</SPAN>	EHLMF-0003</SPAN>	EHLMF-0003</SPAN>
Metal Fitting</SPAN>	EHLMF</SPAN>	EHLMF-0004</SPAN>	EHLMF-0004</SPAN>
Metal Fitting</SPAN>	EHLMF</SPAN>	EHLMF-0005</SPAN>	EHLMF-0005</SPAN>
Metal Fitting</SPAN>	EHLMF</SPAN>	EHLMF-0006</SPAN>	EHLMF-0006</SPAN>
Metal Fitting</SPAN>	EHLMF</SPAN>	EHLMF-0007</SPAN>	EHLMF-0007</SPAN>
Metal Fitting</SPAN>	EHLMF</SPAN>	EHLMF-0008</SPAN>	EHLMF-0008</SPAN>
Safety Valve</SPAN>	EHLSV</SPAN>	EHLSV-0001</SPAN>	EHLSV-0001</SPAN>
Valve</SPAN>	EHLV</SPAN>	EHLV-0001</SPAN>	EHLV-0001</SPAN>
Valve</SPAN>	EHLV</SPAN>	EHLV-0002</SPAN>	EHLV-0002</SPAN>
Valve</SPAN>	EHLV</SPAN>	EHLV-0003</SPAN>	EHLV-0003</SPAN>
Valve</SPAN>	EHLV</SPAN>	EHLV-0004</SPAN>	EHLV-0004</SPAN>
Valve</SPAN>	EHLV</SPAN>	EHLV-0005</SPAN>	EHLV-0005</SPAN>
Valve</SPAN>	EHLV</SPAN>	EHLV-0006</SPAN>	EHLV-0006</SPAN>
Valve</SPAN>	EHLV</SPAN>	EHLV-0007</SPAN>	EHLV-0007</SPAN>
Valve</SPAN>	EHLV</SPAN>	EHLV-0008</SPAN>	EHLV-0008</SPAN>
Valve</SPAN>	EHLV</SPAN>	EHLV-0009</SPAN>	EHLV-0009</SPAN>
Valve</SPAN>	EHLV</SPAN>	EHLV-0010</SPAN>	EHLV-0010</SPAN>
Valve</SPAN>	EHLV</SPAN>	EHLV-0011</SPAN>	EHLV-0011</SPAN>
Valve</SPAN>	EHLV</SPAN>	EHLV-0012</SPAN>	EHLV-0012</SPAN>
Valve</SPAN>	EHLV</SPAN>	EHLV-0013</SPAN>	EHLV-0013</SPAN>
Valve</SPAN>	EHLV</SPAN>	EHLV-0014</SPAN>	EHLV-0014</SPAN>
Valve</SPAN>	EHLV</SPAN>	EHLV-0015</SPAN>	EHLV-0015</SPAN>
Valve</SPAN>	EHLV</SPAN>	EHLV-0016</SPAN>	EHLV-0016</SPAN>
Valve</SPAN>	EHLV</SPAN>	EHLV-0017</SPAN>	EHLV-0017</SPAN>
Metal Fitting</SPAN>	EHLMF</SPAN>	EHLMF-0009</SPAN>	EHLMF-0009</SPAN>
Metal Fitting</SPAN>	EHLMF</SPAN>	EHLMF-0010</SPAN>	EHLMF-0010</SPAN>
Safety Valve</SPAN>	EHLSV</SPAN>	EHLSV-0002</SPAN>	EHLSV-0002</SPAN>
Safety Valve</SPAN>	EHLSV</SPAN>	EHLSV-0003</SPAN>	EHLSV-0003</SPAN>
Valve</SPAN>	EHLV</SPAN>	EHLV-0018</SPAN>	EHLV-0018</SPAN>
Hose</SPAN>	EHLH</SPAN>	EHLH-0619</SPAN>	EHLH-2340</SPAN>
Hose</SPAN>	EHLH</SPAN>	EHLH-0620</SPAN>	EHLH-2341 </SPAN>

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

 

[aru_aakash]

Hello Jon,

Please bring below logic in your sheet. It shall work:

	G Column	H Column	Formula to be dragged down
14th row	A	2336
15th row	A	2337
16th row	A	2338
17th row	B	2660
18th row	A	2339
19th row	C	2999
20th row	A	2340	MAX(IF(G20=$G$14:G19,$H$14:H19,0))+1	CSE formula
21th row	C	3000	MAX(IF(G21=$G$14:G20,$H$14:H20,0))+1	CSE formula
22th row	A	2341	MAX(IF(G22=$G$14:G21,$H$14:H21,0))+1	CSE formula

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

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

Hose	EHLH	EHLH-2335	EHL2335
Hose	EHLH	EHLH-2336	EHL2336
Hose	EHLH	EHLH-2337	EHL2337
Hose	EHLH	EHLH-2337
Hose	EHLH	EHLH-2337
Calibration	EHLC	EHLC-0001
Gauge	EHLG	EHLG-0001
Hose	EHLH	EHLH-0619

<tbody>
</tbody>

 

[RonB1111]

Add a helper column with a sequence number (ie(1,2,3,4...up thru last item in your combined data base) - use the Fill-Series function so all sequence numbers are values.
Then sort the database with the TestTypeCode as the primary sort and the Sequence # as the secondary sort.
Now use the suggested formula in my last post.

 

[Jon3sy]

Apologies has been a while since I have been able to return to this - I'm thinking that maybe it is simpler to just create a new number using your original formula and just keep the "legacy" system number in a separate column as a reference to the historical data - this keeps it clean and uncomplicated. Many thanks for all your help.