CODEWITHSUNDEEP
excelexcel-formula
AlexB
AlexB

Reputation: 2174

Creating hierarchical sequence in Excel using several functions in formula

I need to be able to create hierarchy in the rows to separate tasks from sub tasks and sub sub tasks. Basically the task is Level 1 the sub task is level 2 sub sub task is level 3 and sub sub sub task is level 4.

enter image description here

So the cell A1 should contain formula that when copied to cell G1 should strip value of the cell F1 and then +1 so that the value of the cell G1 will become 2. The cell B1 should contain formula that will take value of the cell A1 and add .1 so that the value becomes 1.1 and if copied further down will increase the value by .1 so that the next cell is 1.2, 1.3 and so on. The rest of the steps for level 3 and level 4 should be almost similar the formula in level 2.

I am trying to do this without VBA just simple formulas that can be placed as template rows in my spreadsheet and can be copied to any place of the spreadsheet when needed.

Upvotes: 2

Views: 4350

Answers (2)

David
David

Reputation: 21

I had this problem too. I solved it like this:

  1. I focused on the dots. Each dot is a hierarchy. At cell A2, to find hierarchy I used this formula. 

    =LEN(c2)-LEN(SUBSTITUTE(c2;".";""))

    The C column is where i have the tree. After this formula populate the whole column. For each dot found it increases/decreases one number, so you have the levels set.

  2. For 4 levels I have made this formula, but you can increase it accordingly to your needs: In cell B2 set this:

    =IF(A2=4;"level4"&C2&" "&D2; IF(A2=3;"level3"&C2&" "&D2; IF(A2=2;"level2"&C2&" "&D2; IF( A2=1;"level1"&C2&" "&D2;IF(A2=0;""&C2&" "&D2)))))

    The cell D2 is the name the hierarchy refers to: eg. C2= 1.1.1 D2= Arthur

This web form "ate" all my spaces so I substituted them by level4,level3,level2,level1,level0. To achieve the tree style please substitute each level0,1,2,3,4, with 5 spaces multiplied by the level number.

After this, populate the whole column. I used spaces so it can show clearly the hierarchy.

It worked for me. Hope it is useful to you.

Upvotes: 2

Max Leps
Max Leps

Reputation: 479

Ok, first of check this article out here, you should be able to find everything you need to create what you need. Now here is all formulas per every level in your example

Level 1

=IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(A1,-1,0,1,1),".",""))),1,IF(ISERROR(FIND("@",SUBSTITUTE(OFFSET(A1,-1,0,1,1),".","@",1))),VALUE(OFFSET(A10,-1,0,1,1))+1,VALUE(LEFT(OFFSET(A1,-1,0,1,1),FIND("@",SUBSTITUTE(OFFSET(A1,-1,0,1,1),".","@",1))-1))+1))

Level 2

=IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(A2,-1,0,1,1),".",""))),"0.1",IF(ISERROR(FIND("@",SUBSTITUTE(OFFSET(A2,-1,0,1,1),".","@",1))),OFFSET(A2,-1,0,1,1)&".1",LEFT(OFFSET(A2,-1,0,1,1),FIND("@",SUBSTITUTE(OFFSET(A2,-1,0,1,1),".","@",1)))&IF(ISERROR(FIND("@",SUBSTITUTE(OFFSET(A2,-1,0,1,1),".","@",2))),VALUE(RIGHT(OFFSET(A2,-1,0,1,1),LEN(OFFSET(A2,-1,0,1,1))-FIND("@",SUBSTITUTE(OFFSET(A2,-1,0,1,1),".","@",1))))+1,VALUE(MID(OFFSET(A2,-1,0,1,1),FIND("@",SUBSTITUTE(OFFSET(A2,-1,0,1,1),".","@",1))+1,(FIND("@",SUBSTITUTE(OFFSET(A2,-1,0,1,1),".","@",2))-FIND("@",SUBSTITUTE(OFFSET(A2,-1,0,1,1),".","@",1))-1)))+1)))

Level 3

=IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(A3,-1,0,1,1),".",""))),"0.0.1",IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A3,-1,0,1,1),".","`",2))),OFFSET(A3,-1,0,1,1)&".1",LEFT(OFFSET(A3,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A3,-1,0,1,1),".","`",2)))&IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A3,-1,0,1,1),".","`",3))),VALUE(RIGHT(OFFSET(A3,-1,0,1,1),LEN(OFFSET(A3,-1,0,1,1))-FIND("`",SUBSTITUTE(OFFSET(A3,-1,0,1,1),".","`",2))))+1,VALUE(MID(OFFSET(A3,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A3,-1,0,1,1),".","`",2))+1,(FIND("`",SUBSTITUTE(OFFSET(A3,-1,0,1,1),".","`",3))-FIND("`",SUBSTITUTE(OFFSET(A3,-1,0,1,1),".","`",2))-1)))+1)))

Level 4

=IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(A4,-1,0,1,1),".",""))),"0.0.0.1",IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A4,-1,0,1,1),".","`",3))),OFFSET(A4,-1,0,1,1)&".1",LEFT(OFFSET(A4,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A4,-1,0,1,1),".","`",3)))&IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A4,-1,0,1,1),".","`",4))),VALUE(RIGHT(OFFSET(A4,-1,0,1,1),LEN(OFFSET(A4,-1,0,1,1))-FIND("`",SUBSTITUTE(OFFSET(A4,-1,0,1,1),".","`",3))))+1,VALUE(MID(OFFSET(A4,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A4,-1,0,1,1),".","`",3))+1,(FIND("`",SUBSTITUTE(OFFSET(A4,-1,0,1,1),".","`",4))-FIND("`",SUBSTITUTE(OFFSET(A4,-1,0,1,1),".","`",3))-1)))+1)))

Of course there may be some other way, but this works too.

Let me know if you have problem with this.

Upvotes: 5

Related Questions