?
Solved

Unique Number Schema

Posted on 2014-01-11
10
Medium Priority
?
185 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 

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

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

601 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