# Multilevel Numbering automation in excel

1. ## 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.

Kim

Maybe :

3. ## 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 .

Greetings
Kamini

4. ## 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".

5. ## 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. ## 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. ## Re: Multilevel Numbering automation in excel

It works, there were some small changes, which I had to do to fix it.

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. ## Re: Multilevel Numbering automation in excel

You are welcome.

Originally Posted by kimjaggi88
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. ## Re: Multilevel Numbering automation in excel

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

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