Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 02-04-2019, 01:11 PM
SerenityNetworks SerenityNetworks is offline Windows 10 Office 2016
Advanced Beginner
 
Join Date: May 2005
Location: Allen, Texas, USA
Posts: 33
SerenityNetworks
Question 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
Attached Files
File Type: xlsx Auto-Number.xlsx (11.2 KB, 4 views)
Reply With Quote
  #2  
Old 02-04-2019, 02:35 PM
NBVC's Avatar
NBVC NBVC is offline Windows 10 Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 199
NBVC is on a distinguished road
Default

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")))
Reply With Quote
  #3  
Old 02-04-2019, 03:56 PM
SerenityNetworks SerenityNetworks is offline Windows 10 Office 2016
Advanced Beginner
 
Join Date: May 2005
Location: Allen, Texas, USA
Posts: 33
SerenityNetworks
Thumbs up 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
Reply With Quote
  #4  
Old 02-05-2019, 12:16 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 386
ArviLaanemets will become famous soon enough
Default

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),"")
Define a name like
Code:
nNextNumber = "REQ-" & RIGHT("0000" & (MAX(YourDataSheet!TheColumnWithEntryNumber) +1),4)
Activate some cell on your data entry sheet (e.g. A1), and define a name
Code:
lAutoNumber=IF(YourDataSheet!$A1 <> "", YourDataSheet!$A1, IF(OR(YourDataSheet!$B1 = "", LEFT(YourDataSheet!$B1,7)="Section"),"",nNextNumber)
On your data entry sheet, for column A define data validation list with source
Code:
=lAutoNumber
When user enters anything into column B which doesn't start with "Section", and there is no autonumber in column A, then user can select new autonumber.
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.
Reply With Quote
  #5  
Old 02-05-2019, 07:22 AM
SerenityNetworks SerenityNetworks is offline Windows 10 Office 2016
Advanced Beginner
 
Join Date: May 2005
Location: Allen, Texas, USA
Posts: 33
SerenityNetworks
Thumbs up 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
Reply With Quote
Reply

Tags
auto-numbered

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 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


All times are GMT -7. The time now is 05:59 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft