Solved

Unique Number Schema

Posted on 2014-01-11
10
156 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

932 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

11 Experts available now in Live!

Get 1:1 Help Now