Unique Number Schema

EE Pros,

I have a WB that has 4 levels of outline form with specific designators. The three that matter most are;

FA = "Functional Area"
VP = "Value Proposition"
UC = "Use Case"

All designators under a UC is simply calculations and output for that specific UC.

So the important Items I am trying to sequence are FA, VP and UC.  VP is a subset of FA and UC is a subset of VP.  By clicking the buttons at the top, the outline levels become visible.

Over time, I will be adding additional FAs, VPs and UCs by inserting lines.   Here's what I need..... I'm trying to find a way to create unique numerical, ascending ordered ID number schema that will allow me to always sequence the designators in the correct order based on lines I've added.

I know this sounds complex, but if you step back, it's about creating a unique ID based on a hierarchy and then sequencing the numbers correctly/in order.

Thank you in advance.

B.
Unique-Identifier-Macro.xlsm
Bright01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FlysterCommented:
Please see column I in attached. That number is based off the fomulas in columns T, U and V. Level 1, (FA), starts at 1 and goes up after each new FA. The other 2 levels do the same, but then goes back to 1 after a new FA is reached. If you add new rows, you will have to copy all the the formulas.
Error-in-Macro-on-Check-Box.xlsm
Bright01Author Commented:
Flyster,

Thanks for the schema idea with formulas.  I did as you suggested above and added another FA line and copied the formulas.  It did add the new value, but did not change the sequencing for the other numbers below the inserted, new FA Line.

To be more specific, I am trying to have a schema that allows for me to identify all unique occurrences of FA, VP and UCs that are in the WS at any point in time.  As I add FA, VP or UC items, I need the numbering system to re-sequence the order of the numbers to reflect the addition of any of those new elements.

Does that make sense?

B.
FlysterCommented:
I inserted 3 new FA's, one in the beginning, one in the middle and 1 at the bottom. They all renumbered accordingly. What I did was to insert new rows, copied the entire rows of existing FA, VP and UCs and pasted them into the new rows and renamed them. So each level has its own unique number. Does this work for you or are you looking for something different!
Error-in-Macro-on-Check-Box.xlsm
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Bright01Author Commented:
Flyster,

Not sure what's going on but here are my comments:

1.) When I download the file, and try to use the macros (collapsing the outline), it "hangs".  I think it's trying to "contact the server" due to some unknown "link".

2.) When I did get it to work, I still can't add lines and have it re-sequence.  However, it needs a different approach.  The formula(s) for calculating the numbers need to be void of dashes. When inserting a row, a macro should automatically calculate and insert a new line based on the identifier (FA, VP, UC) and then sequence the other numbers below in order to maintain the integrity of the schema.

I don't want to expand the scope beyond what I originally asked for so I'm ok with asking an additional question if you think this needs to be  broken up into several questions.

Please advise,

Thank you,


B.
FlysterCommented:
Try this one. Run the UniqueNumber macro. It will place a unique number in column H for each level. If you add or remove rows, just run the macro again and it will make the adjustments.
Unique-Identifier-Macro.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
Flyster,

Thank you!  The math works perfectly and so does the associated macro!  Really appreciate you taking the time on this.

All the best,

B.
FlysterCommented:
You're welcome. I'm glad I could help you out with this!
Bright01Author Commented:
Flyster,

One quick question....... can you give me the quick change for how I identify the last row that should be counted?  I would use the end of the designators.  When I put your code into my model, it continues to count below the end of my designators.  I think it is because of this line;

For i = 15 To lr

Any simple fix here?

B.
FlysterCommented:
Try changing the lr formula to this:

lr = Range("F" & Rows.Count).End(xlUp).Row
Bright01Author Commented:
Flyster,

Greetings!  I needed a small "fix" to your code.  It's at:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28352889.html

If you're interested.

Thank you!

B.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.