Solved

Unique Number Schema

Posted on 2014-01-11
10
147 Views
Last Modified: 2014-01-30
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
0
Comment
Question by:Bright01
  • 5
  • 5
10 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 39774186
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
0
 

Author Comment

by:Bright01
ID: 39774891
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.
0
 
LVL 22

Expert Comment

by:Flyster
ID: 39775103
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
0
 

Author Comment

by:Bright01
ID: 39775174
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.
0
 
LVL 22

Accepted Solution

by:
Flyster earned 500 total points
ID: 39775524
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
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Closing Comment

by:Bright01
ID: 39786616
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.
0
 
LVL 22

Expert Comment

by:Flyster
ID: 39786626
You're welcome. I'm glad I could help you out with this!
0
 

Author Comment

by:Bright01
ID: 39786791
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.
0
 
LVL 22

Expert Comment

by:Flyster
ID: 39786922
Try changing the lr formula to this:

lr = Range("F" & Rows.Count).End(xlUp).Row
0
 

Author Comment

by:Bright01
ID: 39822909
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.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now