Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 187
  • Last Modified:

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
0
Bright01
Asked:
Bright01
  • 5
  • 5
1 Solution
 
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
0
 
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.
0
 
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
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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.
0
 
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
0
 
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.
0
 
FlysterCommented:
You're welcome. I'm glad I could help you out with this!
0
 
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.
0
 
FlysterCommented:
Try changing the lr formula to this:

lr = Range("F" & Rows.Count).End(xlUp).Row
0
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now