# Derive Heirearchy Level from Range

#### prab76

##### New Member
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:

<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

 Task Level T0 1 T5 1 T1 2 T3 2 T4 2 T2 3 T8 4

<tbody>
</tbody>

Can anyone please provide direction in Excel how to achieve this

### 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
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 Lvls = CreateObject("Scripting.dictionary")

MyData = Range("A2:C" & Cells(Rows.Count, "A").End(xlUp).Row).Value
For i = 1 To UBound(MyData)
If Not Tasks.exists(MyData(i, 3)) And MyData(i, 3) <> "" Then Tasks(MyData(i, 3)) = ""
For Each x In Split(MyData(i, 2), ",")
Next x
Next i

lvl = 1
w = x
If w = "" Then Exit Do
lvl = lvl + 1
Loop
Lvls(x) = lvl
Next x

Range("F2").Resize(Lvls.Count).Value = WorksheetFunction.Transpose(Lvls.items)

With ActiveSheet.Sort
.SortFields.Clear
.SetRange Range("E:F")
.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
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:

#### prab76

##### New Member
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

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

#### Eric W

##### MrExcel MVP
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
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'

Replies
2
Views
77
Replies
6
Views
457
Replies
8
Views
36
Replies
2
Views
371
Replies
1
Views
364