Increment alphanumeric column value by 1

T

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:
 
Sort by date Sort by votes
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
RonB1111 said:
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.
Click to expand...

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?

[TABLE="width: 413"]
<TBODY>[TR]
[TD]Certification Type</SPAN>[/TD]
[TD]Test Type Code</SPAN>[/TD]
[TD]Test Report #</SPAN>[/TD]
[TD]Expected result</SPAN>[/TD]
[/TR]
[TR]
[TD]Hose</SPAN>[/TD]
[TD]EHLH</SPAN>[/TD]
[TD]EHLH-2335</SPAN>[/TD]
[TD]EHLH-2335</SPAN>[/TD]
[/TR]
[TR]
[TD]Hose</SPAN>[/TD]
[TD]EHLH</SPAN>[/TD]
[TD]EHLH-2336</SPAN>[/TD]
[TD]EHLH-2336</SPAN>[/TD]
[/TR]
[TR]
[TD]Hose</SPAN>[/TD]
[TD]EHLH</SPAN>[/TD]
[TD]EHLH-2337</SPAN>[/TD]
[TD]EHLH-2337</SPAN>[/TD]
[/TR]
[TR]
[TD]Hose</SPAN>[/TD]
[TD]EHLH</SPAN>[/TD]
[TD]EHLH-0617</SPAN>[/TD]
[TD]EHLH-2338</SPAN>[/TD]
[/TR]
[TR]
[TD]Hose</SPAN>[/TD]
[TD]EHLH</SPAN>[/TD]
[TD]EHLH-0618</SPAN>[/TD]
[TD]EHLH-2339</SPAN>[/TD]
[/TR]
[TR]
[TD]Metal Fitting</SPAN>[/TD]
[TD]EHLMF</SPAN>[/TD]
[TD]EHLMF-0001</SPAN>[/TD]
[TD]EHLMF-0001</SPAN>[/TD]
[/TR]
[TR]
[TD]Metal Fitting</SPAN>[/TD]
[TD]EHLMF</SPAN>[/TD]
[TD]EHLMF-0002</SPAN>[/TD]
[TD]EHLMF-0002</SPAN>[/TD]
[/TR]
[TR]
[TD]Metal Fitting</SPAN>[/TD]
[TD]EHLMF</SPAN>[/TD]
[TD]EHLMF-0003</SPAN>[/TD]
[TD]EHLMF-0003</SPAN>[/TD]
[/TR]
[TR]
[TD]Metal Fitting</SPAN>[/TD]
[TD]EHLMF</SPAN>[/TD]
[TD]EHLMF-0004</SPAN>[/TD]
[TD]EHLMF-0004</SPAN>[/TD]
[/TR]
[TR]
[TD]Metal Fitting</SPAN>[/TD]
[TD]EHLMF</SPAN>[/TD]
[TD]EHLMF-0005</SPAN>[/TD]
[TD]EHLMF-0005</SPAN>[/TD]
[/TR]
[TR]
[TD]Metal Fitting</SPAN>[/TD]
[TD]EHLMF</SPAN>[/TD]
[TD]EHLMF-0006</SPAN>[/TD]
[TD]EHLMF-0006</SPAN>[/TD]
[/TR]
[TR]
[TD]Metal Fitting</SPAN>[/TD]
[TD]EHLMF</SPAN>[/TD]
[TD]EHLMF-0007</SPAN>[/TD]
[TD]EHLMF-0007</SPAN>[/TD]
[/TR]
[TR]
[TD]Metal Fitting</SPAN>[/TD]
[TD]EHLMF</SPAN>[/TD]
[TD]EHLMF-0008</SPAN>[/TD]
[TD]EHLMF-0008</SPAN>[/TD]
[/TR]
[TR]
[TD]Safety Valve</SPAN>[/TD]
[TD]EHLSV</SPAN>[/TD]
[TD]EHLSV-0001</SPAN>[/TD]
[TD]EHLSV-0001</SPAN>[/TD]
[/TR]
[TR]
[TD]Valve</SPAN>[/TD]
[TD]EHLV</SPAN>[/TD]
[TD]EHLV-0001</SPAN>[/TD]
[TD]EHLV-0001</SPAN>[/TD]
[/TR]
[TR]
[TD]Valve</SPAN>[/TD]
[TD]EHLV</SPAN>[/TD]
[TD]EHLV-0002</SPAN>[/TD]
[TD]EHLV-0002</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>[/TABLE]
 
Upvote 0
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.
Code: 
=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")
 
Upvote 0
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.
[TABLE="width: 413"]
<TBODY>[TR]
[TD]Certification Type</SPAN>[/TD]
[TD]Test Type Code</SPAN>[/TD]
[TD]Test Report #</SPAN>[/TD]
[TD]Expected result</SPAN>[/TD]
[/TR]
[TR]
[TD]Hose</SPAN>[/TD]
[TD]EHLH</SPAN>[/TD]
[TD]EHLH-2335</SPAN>[/TD]
[TD]EHLH-2335</SPAN>[/TD]
[/TR]
[TR]
[TD]Hose</SPAN>[/TD]
[TD]EHLH</SPAN>[/TD]
[TD]EHLH-2336</SPAN>[/TD]
[TD]EHLH-2336</SPAN>[/TD]
[/TR]
[TR]
[TD]Hose</SPAN>[/TD]
[TD]EHLH</SPAN>[/TD]
[TD]EHLH-2337</SPAN>[/TD]
[TD]EHLH-2337</SPAN>[/TD]
[/TR]
[TR]
[TD]Hose</SPAN>[/TD]
[TD]EHLH</SPAN>[/TD]
[TD]EHLH-0617</SPAN>[/TD]
[TD]EHLH-2338</SPAN>[/TD]
[/TR]
[TR]
[TD]Hose</SPAN>[/TD]
[TD]EHLH</SPAN>[/TD]
[TD]EHLH-0618</SPAN>[/TD]
[TD]EHLH-2339</SPAN>[/TD]
[/TR]
[TR]
[TD]Metal Fitting</SPAN>[/TD]
[TD]EHLMF</SPAN>[/TD]
[TD]EHLMF-0001</SPAN>[/TD]
[TD]EHLMF-0001</SPAN>[/TD]
[/TR]
[TR]
[TD]Metal Fitting</SPAN>[/TD]
[TD]EHLMF</SPAN>[/TD]
[TD]EHLMF-0002</SPAN>[/TD]
[TD]EHLMF-0002</SPAN>[/TD]
[/TR]
[TR]
[TD]Metal Fitting</SPAN>[/TD]
[TD]EHLMF</SPAN>[/TD]
[TD]EHLMF-0003</SPAN>[/TD]
[TD]EHLMF-0003</SPAN>[/TD]
[/TR]
[TR]
[TD]Metal Fitting</SPAN>[/TD]
[TD]EHLMF</SPAN>[/TD]
[TD]EHLMF-0004</SPAN>[/TD]
[TD]EHLMF-0004</SPAN>[/TD]
[/TR]
[TR]
[TD]Metal Fitting</SPAN>[/TD]
[TD]EHLMF</SPAN>[/TD]
[TD]EHLMF-0005</SPAN>[/TD]
[TD]EHLMF-0005</SPAN>[/TD]
[/TR]
[TR]
[TD]Metal Fitting</SPAN>[/TD]
[TD]EHLMF</SPAN>[/TD]
[TD]EHLMF-0006</SPAN>[/TD]
[TD]EHLMF-0006</SPAN>[/TD]
[/TR]
[TR]
[TD]Metal Fitting</SPAN>[/TD]
[TD]EHLMF</SPAN>[/TD]
[TD]EHLMF-0007</SPAN>[/TD]
[TD]EHLMF-0007</SPAN>[/TD]
[/TR]
[TR]
[TD]Metal Fitting</SPAN>[/TD]
[TD]EHLMF</SPAN>[/TD]
[TD]EHLMF-0008</SPAN>[/TD]
[TD]EHLMF-0008</SPAN>[/TD]
[/TR]
[TR]
[TD]Safety Valve</SPAN>[/TD]
[TD]EHLSV</SPAN>[/TD]
[TD]EHLSV-0001</SPAN>[/TD]
[TD]EHLSV-0001</SPAN>[/TD]
[/TR]
[TR]
[TD]Valve</SPAN>[/TD]
[TD]EHLV</SPAN>[/TD]
[TD]EHLV-0001</SPAN>[/TD]
[TD]EHLV-0001</SPAN>[/TD]
[/TR]
[TR]
[TD]Valve</SPAN>[/TD]
[TD]EHLV</SPAN>[/TD]
[TD]EHLV-0002</SPAN>[/TD]
[TD]EHLV-0002</SPAN>[/TD]
[/TR]
[TR]
[TD]Valve</SPAN>[/TD]
[TD]EHLV</SPAN>[/TD]
[TD]EHLV-0003</SPAN>[/TD]
[TD]EHLV-0003</SPAN>[/TD]
[/TR]
[TR]
[TD]Valve</SPAN>[/TD]
[TD]EHLV</SPAN>[/TD]
[TD]EHLV-0004</SPAN>[/TD]
[TD]EHLV-0004</SPAN>[/TD]
[/TR]
[TR]
[TD]Valve</SPAN>[/TD]
[TD]EHLV</SPAN>[/TD]
[TD]EHLV-0005</SPAN>[/TD]
[TD]EHLV-0005</SPAN>[/TD]
[/TR]
[TR]
[TD]Valve</SPAN>[/TD]
[TD]EHLV</SPAN>[/TD]
[TD]EHLV-0006</SPAN>[/TD]
[TD]EHLV-0006</SPAN>[/TD]
[/TR]
[TR]
[TD]Valve</SPAN>[/TD]
[TD]EHLV</SPAN>[/TD]
[TD]EHLV-0007</SPAN>[/TD]
[TD]EHLV-0007</SPAN>[/TD]
[/TR]
[TR]
[TD]Valve</SPAN>[/TD]
[TD]EHLV</SPAN>[/TD]
[TD]EHLV-0008</SPAN>[/TD]
[TD]EHLV-0008</SPAN>[/TD]
[/TR]
[TR]
[TD]Valve</SPAN>[/TD]
[TD]EHLV</SPAN>[/TD]
[TD]EHLV-0009</SPAN>[/TD]
[TD]EHLV-0009</SPAN>[/TD]
[/TR]
[TR]
[TD]Valve</SPAN>[/TD]
[TD]EHLV</SPAN>[/TD]
[TD]EHLV-0010</SPAN>[/TD]
[TD]EHLV-0010</SPAN>[/TD]
[/TR]
[TR]
[TD]Valve</SPAN>[/TD]
[TD]EHLV</SPAN>[/TD]
[TD]EHLV-0011</SPAN>[/TD]
[TD]EHLV-0011</SPAN>[/TD]
[/TR]
[TR]
[TD]Valve</SPAN>[/TD]
[TD]EHLV</SPAN>[/TD]
[TD]EHLV-0012</SPAN>[/TD]
[TD]EHLV-0012</SPAN>[/TD]
[/TR]
[TR]
[TD]Valve</SPAN>[/TD]
[TD]EHLV</SPAN>[/TD]
[TD]EHLV-0013</SPAN>[/TD]
[TD]EHLV-0013</SPAN>[/TD]
[/TR]
[TR]
[TD]Valve</SPAN>[/TD]
[TD]EHLV</SPAN>[/TD]
[TD]EHLV-0014</SPAN>[/TD]
[TD]EHLV-0014</SPAN>[/TD]
[/TR]
[TR]
[TD]Valve</SPAN>[/TD]
[TD]EHLV</SPAN>[/TD]
[TD]EHLV-0015</SPAN>[/TD]
[TD]EHLV-0015</SPAN>[/TD]
[/TR]
[TR]
[TD]Valve</SPAN>[/TD]
[TD]EHLV</SPAN>[/TD]
[TD]EHLV-0016</SPAN>[/TD]
[TD]EHLV-0016</SPAN>[/TD]
[/TR]
[TR]
[TD]Valve</SPAN>[/TD]
[TD]EHLV</SPAN>[/TD]
[TD]EHLV-0017</SPAN>[/TD]
[TD]EHLV-0017</SPAN>[/TD]
[/TR]
[TR]
[TD]Metal Fitting</SPAN>[/TD]
[TD]EHLMF</SPAN>[/TD]
[TD]EHLMF-0009</SPAN>[/TD]
[TD]EHLMF-0009</SPAN>[/TD]
[/TR]
[TR]
[TD]Metal Fitting</SPAN>[/TD]
[TD]EHLMF</SPAN>[/TD]
[TD]EHLMF-0010</SPAN>[/TD]
[TD]EHLMF-0010</SPAN>[/TD]
[/TR]
[TR]
[TD]Safety Valve</SPAN>[/TD]
[TD]EHLSV</SPAN>[/TD]
[TD]EHLSV-0002</SPAN>[/TD]
[TD]EHLSV-0002</SPAN>[/TD]
[/TR]
[TR]
[TD]Safety Valve</SPAN>[/TD]
[TD]EHLSV</SPAN>[/TD]
[TD]EHLSV-0003</SPAN>[/TD]
[TD]EHLSV-0003</SPAN>[/TD]
[/TR]
[TR]
[TD]Valve</SPAN>[/TD]
[TD]EHLV</SPAN>[/TD]
[TD]EHLV-0018</SPAN>[/TD]
[TD]EHLV-0018</SPAN>[/TD]
[/TR]
[TR]
[TD]Hose</SPAN>[/TD]
[TD]EHLH</SPAN>[/TD]
[TD]EHLH-0619</SPAN>[/TD]
[TD]EHLH-2340</SPAN>[/TD]
[/TR]
[TR]
[TD]Hose</SPAN>[/TD]
[TD]EHLH</SPAN>[/TD]
[TD]EHLH-0620</SPAN>[/TD]
[TD]
EHLH-2341
</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>[/TABLE]
 
Upvote 0
Hello Jon,

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

[TABLE="width: 540"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]G Column[/TD]
[TD]H Column[/TD]
[TD]Formula to be dragged down[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14th row[/TD]
[TD]A[/TD]
[TD="align: right"]2336[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15th row[/TD]
[TD]A[/TD]
[TD="align: right"]2337[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16th row[/TD]
[TD]A[/TD]
[TD="align: right"]2338[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17th row[/TD]
[TD]B[/TD]
[TD="align: right"]2660[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18th row[/TD]
[TD]A[/TD]
[TD="align: right"]2339[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19th row[/TD]
[TD]C[/TD]
[TD="align: right"]2999[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20th row[/TD]
[TD]A[/TD]
[TD="align: right"]2340 [/TD]
[TD]MAX(IF(G20=$G$14:G19,$H$14:H19,0))+1[/TD]
[TD]CSE formula[/TD]
[/TR]
[TR]
[TD]21th row[/TD]
[TD]C[/TD]
[TD="align: right"]3000[/TD]
[TD]MAX(IF(G21=$G$14:G20,$H$14:H20,0))+1[/TD]
[TD]CSE formula[/TD]
[/TR]
[TR]
[TD]22th row[/TD]
[TD]A[/TD]
[TD="align: right"]2341[/TD]
[TD]MAX(IF(G22=$G$14:G21,$H$14:H21,0))+1[/TD]
[TD]CSE formula[/TD]
[/TR]
</tbody>[/TABLE]


Jon3sy said:
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[/TD]
[TD]EHLH[/TD]
[TD]EHLH-2335[/TD]
[TD]EHL2335[/TD]
[/TR]
[TR]
[TD]Hose[/TD]
[TD]EHLH[/TD]
[TD]EHLH-2336[/TD]
[TD]EHL2336[/TD]
[/TR]
[TR]
[TD]Hose[/TD]
[TD]EHLH[/TD]
[TD]EHLH-2337[/TD]
[TD]EHL2337[/TD]
[/TR]
[TR]
[TD]Hose[/TD]
[TD]EHLH[/TD]
[TD]EHLH-2337[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hose[/TD]
[TD]EHLH[/TD]
[TD]EHLH-2337[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Calibration[/TD]
[TD]EHLC[/TD]
[TD]EHLC-0001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gauge[/TD]
[TD]EHLG[/TD]
[TD]EHLG-0001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hose[/TD]
[TD]EHLH[/TD]
[TD]EHLH-0619[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Using Vlookup for Alphanumeric and numeric values. It is not recognising the numeric
Replies
8
Views
194
coulombevin
coulombevin
J
  • Solved
Drag down formula in row an increment value by more than 1
Replies
6
Views
2K
StephenCrump
S
D
  • Question Question
VBA to transfor set of data from column to rows
Replies
0
Views
717
danielalvz
D
C
  • Question Question
Search with values from one row in another column and then return value
Replies
0
Views
615
Corne89
C
L
  • Question Question
Create worksheet tabs from a range of cells but do NOT create duplicates
Replies
0
Views
495
lgronowski
L

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top