Derive Heirearchy Level from Range

[prab76]

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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Task[/TD]
[TD]SubTask[/TD]
[TD]ParentTask[/TD]
[/TR]
[TR]
[TD]T1[/TD]
[TD]T2[/TD]
[TD]T0[/TD]
[/TR]
[TR]
[TD]T5[/TD]
[TD]T3,T4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]T8[/TD]
[TD][/TD]
[TD]T2[/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Task[/TD]
[TD]Level[/TD]
[/TR]
[TR]
[TD]T0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]T5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]T1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]T3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]T4[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]T2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]T8[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]


Can anyone please provide direction in Excel how to achieve this

 

[Eric W]

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:

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]

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 , 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]

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]

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

 

[Eric W]

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]

I don't understand how you can have/allow orphan tasks.

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'