+ Reply to Thread
Results 1 to 9 of 9

Multilevel Numbering automation in excel

  1. #1
    Registered User
    Join Date
    07-15-2016
    Location
    Germany
    MS-Off Ver
    at Mac version 15.24
    Posts
    34

    Multilevel Numbering automation in excel

    Hello Guys,

    I have question regarding multilevel list in Excel.

    I have numbered the column as a Tree structure so for example:
    T1
    T1.1
    T1.1.1
    T1.1.1.1
    T1.1.1.1.1
    T1.1.1.1.2
    T1.1.1.1.3
    T1.1.1.2
    T1.1.1.3
    T1.1.1.3.1
    .......

    Every leaf node (the one which has no children further), so in my case T1.1.1.1.1-3 is calculated via a specific formula, lets say F1.
    And every node is Max of Leaf nodes, for example T 1.1.1.1= Max(T1.1.1.1.1;T1.1.1.1.2,T1.1.1.1.3) , T1.1.1 =Max(T1.1.1.1; T1.1.1.2)...and so on

    For now it is working manually. But what I am trying to do is , to automate it via VBA.
    So I want to wrote a Macro which somehow could detect which one is leaf node and which one is Node or parent node and apply formulas on..

    An additional feature would be if I could write this list automatically also instead of writing each number manually.

    I am not very good with VBA. so i someone could help me with this would be a great help.

    Thanks in advance!
    Kim

  2. #2
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,762

    Re: Multilevel Numbering automation in excel

    Maybe :

    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    07-15-2016
    Location
    Germany
    MS-Off Ver
    at Mac version 15.24
    Posts
    34

    Re: Multilevel Numbering automation in excel

    Hi,

    Thanks for your amazing expert code. But I would need further help from you
    Because of my lack of knowledge in VBA, its hard for me to modify the code to apply it on my scenario.

    Therefore I have attached an example sheet to have a detailed view for you.

    As you can see in the sheet: Column G is the SCORE column, where I need all scores. Right now all formulas applied are manual but I want these formulas to be automatic. so this should go like this:

    1. Find out who is Leaf? Then apply formula =(VLOOKUP(D6;Sheet2!$A$2:$B$4;2;FALSE)+VLOOKUP(E6;Sheet2!$A$2:$B$4;2;FALSE)+VLOOKUP(F6;Sheet2!$A$2:$B$4;2;FALSE)) on it.

    2. Find out parent of the leaf and apply formula =MAX(Leaves) - E.g G4 will be =MAX(G5;G9;G10)
    The output should be in Column G - not in separate column, means the scoring should be automatic in the entire column.

    3. Additional check will be on Column C ("Relevant")
    If value in Column C is "NO" - Ignore it during Calculation
    ((In my original sheet - I am marking "not relevant" rows as grey ) via Data Validation and hiding them via Macro but I couldn't write a macro to totally grey them out in the sense that they are useless and no one can type anything in them and they are not considered during calculation. I am just manually not writing anything in it))


    It will be great help, if you could help me in solving this .

    Thanks again in advance for your time.

    Greetings
    Kamini
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,762

    Re: Multilevel Numbering automation in excel

    You are welcome.

    In your example file, you don't put the effect of rule 3 : Additional check will be on Column C ("Relevant"),
    so I will set the value of column G to "NO" for column C = "No".

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    07-15-2016
    Location
    Germany
    MS-Off Ver
    at Mac version 15.24
    Posts
    34

    Re: Multilevel Numbering automation in excel

    Hi,

    Thanks for the Genius code. Its perfect.

    The only point where it fails is, if I modify the formula, for example, Instead of D+E+F, I add two additional Column and apply formula - ((E+F)*(D+G+H)) with same vlookup functionality then the code fails- The excel just quits--doesn't even tell me whats wrong with code.

    Any suggestions.

    Thanks!

  6. #6
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,762

    Re: Multilevel Numbering automation in excel

    Hard to tell without seeing the actual data, including your code.
    Please post the workbook (including the code).

  7. #7
    Registered User
    Join Date
    07-15-2016
    Location
    Germany
    MS-Off Ver
    at Mac version 15.24
    Posts
    34

    Re: Multilevel Numbering automation in excel

    Thanks for your help!
    It works, there were some small changes, which I had to do to fix it.

    Thanks again for your help!

    The last question I have is- is it possible to automate the creation of numbering in Tree Structure and if not then atleast sort them if something is not in place..

  8. #8
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,762

    Re: Multilevel Numbering automation in excel

    You are welcome.


    Quote Originally Posted by kimjaggi88 View Post
    The last question I have is- is it possible to automate the creation of numbering in Tree Structure and if not then atleast sort them if something is not in place..
    Unless there is identifiable pattern on the data, this should be done manually.
    For example, if on level 1 of tree structure there is word "region", on level 2 there is word "subregion", on level 3 is the rest of data which doesn't have words "region" and "subregion" like this :
    North region
    A subregion
    XXX1
    XXX2
    B subregion
    XXX3
    South region
    C subregion
    XXX4
    D subregion
    XXX5
    XXX6
    If there is a pattern like this, the automation is possible, if there isn't then it is impossible.

  9. #9
    Registered User
    Join Date
    07-15-2016
    Location
    Germany
    MS-Off Ver
    at Mac version 15.24
    Posts
    34

    Re: Multilevel Numbering automation in excel

    Hi, Thanks for answer.

    No there is no pattern. It could be anything.
    Thanks for explaining.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Flat bom to multilevel bom
    By kcoates in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-21-2014, 06:06 PM
  2. Centering multilevel numbering list with text below
    By western_gunner in forum Word Formatting & General
    Replies: 5
    Last Post: 08-22-2013, 01:28 AM
  3. Using Multilevel Lists in VBA
    By drstrings in forum Word Programming / VBA / Macros
    Replies: 0
    Last Post: 08-07-2013, 08:45 PM
  4. MultiLevel BOM Comparison
    By gopinathtt in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-27-2012, 07:33 PM
  5. Excel 2003 multilevel subtotals in wrong positions
    By JMA7450 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-05-2006, 05:20 PM
  6. Multilevel Ranges
    By Cheer-Phil-ly in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-30-2005, 05:20 PM
  7. Multilevel Sorting?
    By Mike in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-18-2005, 11:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1