How to get excel to auto-outline hierarchy into groups?

Question

So I have a bunch of data in an excel spreadsheet that looks something like this:

Level   Hierarchy       Name
0       1               Sam
1       1.1             Bill
2       1.1.1           George
3       1.1.1.1         Fred
4       1.1.1.1.1       Richard
4       1.1.1.1.2       Steve
4       1.1.1.1.3       Max
4       1.1.1.1.4       Sven
3       1.1.1.2         Mike
4       1.1.1.2.1       John
4       1.1.1.2.2       Isaac
4       1.1.1.2.3       Zack
2       1.1.2           James
3       1.1.2.1         Henry
4       1.1.2.1.1       Greg
            .
            .
            .

I'd like to automatically create groups in the worksheet such that Richard, Steve, Max, and Sven are grouped under Fred; John, Isaac, and Zack are under Mike; etc...; and they all roll up under Sam.

I tried to use the Excel auto-outline feature, but I get the message "Cannot create an outline." Can anyone tell me how to make this work or suggest another way of doing this?

The data comes from another system, but I can transform the data before it's imported into Excel -- if that makes it easier.

Answer 1

If you have a finite number of levels, then I would do the following. If your data ever has to be refreshed, it has the advantage of being a simple pivot refresh (i.e. you set it up just once):

1. Create new columns ( number=max level) to represent the person's boss at each level of the hierarchy (note, it assumes it is sorted by hiearchy field)

2. Formula in D3 and copied over and down: =IF($B3=D$2,$A3,IF($B3<D$2,"",D2))

3. Create a pivot table (compact or outline view depending on your preference), setting each level boss as a row label
4. Filter blank level bosses out at each level

Answer 2

In order to use the auto-outline grouping feature, you need to (manually) insert summary rows with the Subtotal() function. Excel will then recognize this as the break point for the grouping. For details see the Microsoft article Outline (group) data in a worksheet.

Answer 3

I would load this table into Power Pivot and flatten the hierarchy into levels, usi the DAX functions e.g. PATHITEM

http://www.powerpivotblog.nl/powerpivot-denali-parent-child-using-dax/

If you cant use Power Pivot then I would get the source system to flatten the hierarchy into level columns in a similar way.

The presentation would be in a Pivot Table, where you can expand the nodes. This will automatically adjust to new data as it comes in, without fiddling with formulas.

The big caveat is that you have to fix the maximum number of levels.

Answer 4

Below linked macro groups (auto-outlines) rows based on hierarchy column. Just select level numbers (formatted with full stops[Hierarchy column]) and run the macro. Works in a range and in a table. No total, count etc rows will be added.

Grouped rows

VBA Code - auto group rows

Answer 5

A VBA code to auto group rows by the number in the hierarchy column

For i = 3 To RowLength 
    On Error Resume Next
    levelNo = Len(Cells(i, WBSColNo)) - Len(Replace(Cells(i, WBSColNo), ".", "")) + 1
    Rows(i).OutlineLevel = levelNo  
 Next

Sincerely