Derive Heirearchy Level from Range

prab76

New Member
Joined
Apr 27, 2010
Messages
4
I have a range where I need to derive the level/depth in the hierarchy for each cell where some cells are comma seperated , for example:

TaskSubTaskParentTask
T1T2T0
T5T3,T4
T8T2

<tbody>
</tbody>


In this example range, the level of T0, T5 is 1. The level of T1,T3,T4 is 2. The level of T2 is 3, The level of T8 is 4, I need an output like this

TaskLevel
T01
T51
T12
T32
T42
T23
T84

<tbody>
</tbody>


Can anyone please provide direction in Excel how to achieve this
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,219
Welcome to the Board.

This requires a VBA macro. To try it, open a copy of your workbook, open to the page with data. Right click on the sheet tab on the bottom and select View Code. From the VBA editor menu, click Insert > Module. In the window that opens, paste this code:

Code:
Sub GetLevels()
Dim i As Long, lvl As Long, Tasks As Object, Lvls As Object, MyData As Variant, x As Variant, w As Variant
    
    Set Tasks = CreateObject("Scripting.Dictionary")
    Set Lvls = CreateObject("Scripting.dictionary")
    
    MyData = Range("A2:C" & Cells(Rows.Count, "A").End(xlUp).Row).Value
    For i = 1 To UBound(MyData)
        Tasks(MyData(i, 1)) = MyData(i, 3)
        If Not Tasks.exists(MyData(i, 3)) And MyData(i, 3) <> "" Then Tasks(MyData(i, 3)) = ""
        For Each x In Split(MyData(i, 2), ",")
            Tasks(x) = MyData(i, 1)
        Next x
    Next i
    
    For Each x In Tasks
        lvl = 1
        w = x
        Do While Tasks.exists(w)
            w = Tasks(w)
            If w = "" Then Exit Do
            lvl = lvl + 1
        Loop
        Lvls(x) = lvl
    Next x
    
    Range("E1:F1") = Array("Task", "Level")
    Range("E2").Resize(Tasks.Count).Value = WorksheetFunction.Transpose(Tasks.keys)
    Range("F2").Resize(Lvls.Count).Value = WorksheetFunction.Transpose(Lvls.items)
    
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("F2")
        .SetRange Range("E:F")
        .Header = xlYes
        .Apply
    End With
    
End Sub
Press Alt-Q to close the editor. Press Alt-F8 to open the macro selector. Choose GetLevels and click Run.

This looks down column A as far as there is data. It assumes the only comma delimited cells are in column B. It will place the response table in columns E:F. Hope this helps.
 

Col Delane

Active Member
Joined
Jan 14, 2014
Messages
303
I've tried to develop a formula based solution rather than use VBA like Eric, and have got the correct results for all tasks EXCEPT sub-tasks T3 & T4 which are both shown in one cell of the data table delimited by a comma! That one outlier entry is giving me a headache :banghead:, as I can't find a formula that will return the cell reference or cell position within a range of cells of the cell containing the ID of a task (i.e. Tn)

As an aside, I have one question:
How does a task NOT have a parent task? (i.e A Sub-Task must have a Task, and a Task must have a Parent Task - but not vice versa?)
 

prab76

New Member
Joined
Apr 27, 2010
Messages
4
Thank you Eric , however in reality I know the Level 1 Tasks and the third column is also comma delimited . In the above example , i have the input where T0 and T5 both are level 1. Given this information, is it possible to enhance the code.
 

prab76

New Member
Joined
Apr 27, 2010
Messages
4

ADVERTISEMENT

Hi Col, That particular task may be a level task so it will not have parent task
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,219
It really doesn't make a lot of sense if columns 1 and 3 are comma delimited. If you have a task with 2 parents, you could have discrepancies. What if one parent is level 1 and the other is level 2? Then is the child level 2 or 3? If you want to find every conceivable path up and/or down the tree, that's a much larger task, requiring some kind of recursive algorithm. Would you want the highest level, or least? Or all?

Col has some good questions too. You might want to consider some project management software.
 

Col Delane

Active Member
Joined
Jan 14, 2014
Messages
303
I don't understand how you can have/allow orphan tasks.:confused:

I would expect that a Parent task is mandatory in the hierarchy and the very 1st level to be created, with subordinates (Tasks, Sub-tasks) only added where necessary for the work breakdown. So, as I indicated in my earlier post (#3), every Task and Sub-Task should always have an ultimate Parent task, whereas a Parent won't necessarily have children.

Just sayin' ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,995
Messages
5,526,130
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top