

LinkBack  Thread Tools  Display Modes 
#1




Formula to populate column with text prefix then next number (ascending)
Please see attached.
I'm looking for a formula I can filldown in ColumnA that will allow me to autonumber ColumnA based upon values in ColumnB. If ColumnB contains the word "Section" then the corresponding row in A is blank (or could be "Section #", if that is easier). However, if ColumnB does not contain the word "Section" then I need to:  Prefix the value in A with "REQ"  then follow it with the section number  then follow it with a three digit autonumber value For a simple example:  B1 equals "Section 1  more text here".  B2 equals "some text".  B3 equals "more text".  B4 equals "Section 2  with more text".  B5 equals "yet more text".  A1 would show "" (or it could show "Section 1").  A2 would show "REQ1001".  A3 would show "REQ1002".  A4 would show "" (or it could show "Section 2").  A5 would show "REQ2001" I can do it using a couple of cheater columns, but I'd really like to do it just using ColumnA. Also, what I'm doing in the attached example seems to be way more complicated than needed. Any guidance or example solutions will be appreciated. Thanks in advance, Andrew 
#2




If you can insert a blank row above (and hide it, if desired), then you can add formula in C2, copied down:
=IF(B2="","",IF(LEFT(B2,7)="Section",TRIM(LEFT(B2, FIND("",B2)1)),"REQ"&SUBSTITUTE(LOOKUP(2,1/(LEFT(C$1:C1,7)="Section"),C$1:C1),"Section","")& TEXT(IF(LEFT(C1,7)="Section",1,RIGHT(LOOKUP(2,1/(C$1:C1<>""),C$1:C1),3)+1),"000"))) 
#3




Thank you!
Thank you! That is exactly what I needed. From here I can tweak it for other exceptions I'll run across.
I'd experimented with the LOOKUP function and even went so far as to think that I might use it with SUBSTITUTE to get where I needed. But I could never get there. Not even close. I was making it too complicated trying to parse out just the section number instead of just using the LEFT(7) of the cell and going from there. I follow the strategy you've used, but I'm going to need to study the solution a little more to gain a full understanding of exactly how it is working. I was able to modify your formula (using INDIRECT) to get it to work on Row 1, but it calculates slow as molasses. And not populating B1 with a section number isn't an issue anyway, so I'll use your solution as presented. Thank you again. I don't believe I would have gotten this on my own. Andrew 
#4




With this 'autonumber' calculated with formula, whenever the table is sorted, those 'autonumbers' get attached to different set of data!
I have had a couple of cases where there was need to have such unique ID column, and my approach was: On your entry sheet, add a hidden column, where the numeric part of your autonumber is calculated, like Code:
=IF(LEFT($A1,5) = "REQ",1*MID($A1,5,254),"") Code:
nNextNumber = "REQ" & RIGHT("0000" & (MAX(YourDataSheet!TheColumnWithEntryNumber) +1),4) Code:
lAutoNumber=IF(YourDataSheet!$A1 <> "", YourDataSheet!$A1, IF(OR(YourDataSheet!$B1 = "", LEFT(YourDataSheet!$B1,7)="Section"),"",nNextNumber) Code:
=lAutoNumber This setup will work best, when you define your data entry table as a Table  then all formulas and data validation lists are applied automatically to new rows added to Table. 
#5




Great idea!
Aaah, that's a great idea Arvil. (Yes, of course, a requirement number once assigned should never change.)
For this particular project, we're pasting in finalized text from a Word document to create a numbered Requirements Traceability Matrix. The formula is just a quick and easy method for populating numbers to thousands of lines. Once numbered, we'll copy/pastespecial/values and be done. If we add any requirements later then it will only be a handful. Those we can handle manually. However, your solution will work very well for other projects. On many we commonly add requirements after the fact. I love your solution. It's definitely going in my arsenal of solutions  and it will be used. Thank you very much, Andrew 
Tags 
autonumbered 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Help me create a button to generate an ascending sequential reference number on a worksheet  Blue Owl  Excel Programming  1  05242018 05:01 PM 
If text in column then formula in other  Grubermen  Excel  1  09062017 06:35 AM 
Find entries lacking prefix and then add prefix.  gsteel  Excel  2  08282017 11:36 AM 
Shortcut to haven ascending values in row/column of cells  Straitsfan  Excel  1  08092016 05:28 AM 
Word 2007 TOC Section Number w/Alpha Prefix?  Gwen Butler  Office  2  09292011 06:10 AM 