#1
|
|||
|
|||
Formula to populate column with text prefix then next number (ascending)
Please see attached.
I'm looking for a formula I can fill-down in Column-A that will allow me to auto-number Column-A based upon values in Column-B. If Column-B contains the word "Section" then the corresponding row in A is blank (or could be "Section #", if that is easier). However, if Column-B 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 auto-number 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 "REQ-1001". - A3 would show "REQ-1002". - A4 would show "" (or it could show "Section 2"). - A5 would show "REQ-2001" I can do it using a couple of cheater columns, but I'd really like to do it just using Column-A. 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 set-up 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/paste-special/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 |
auto-numbered |
|
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 | 05-24-2018 05:01 PM |
If text in column then formula in other | Grubermen | Excel | 1 | 09-06-2017 06:35 AM |
Find entries lacking prefix and then add prefix. | gsteel | Excel | 2 | 08-28-2017 11:36 AM |
Shortcut to haven ascending values in row/column of cells | Straitsfan | Excel | 1 | 08-09-2016 05:28 AM |
Word 2007 TOC Section Number w/Alpha Prefix? | Gwen Butler | Office | 2 | 09-29-2011 06:10 AM |