Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

LinkBack Thread Tools Display Modes
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
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,
Attached Files
File Type: xlsx Auto-Number.xlsx (11.2 KB, 4 views)
Reply With Quote
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: 200
NBVC is on a distinguished road

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

Reply With Quote
Old 02-05-2019, 12:16 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Join Date: May 2017
Posts: 424
ArviLaanemets will become famous soon enough

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
=IF(LEFT($A1,5) = "REQ-",1*MID($A1,5,254),"")
Define a name like
nNextNumber = "REQ-" & RIGHT("0000" & (MAX(YourDataSheet!TheColumnWithEntryNumber) +1),4)
Activate some cell on your data entry sheet (e.g. A1), and define a name
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
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
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
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,
Reply With Quote


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 03:21 PM.

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