Create Dynamic Outline with multiple levels based on Outline Hierarchy

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,461
I'm trying to Outline an Organizational Hierarchy. I'm having trouble getting my code to work properly... It will work for the first two levels and then it fails. I was hoping that someone may have run into a similar issue in the past. While this example only goes 4 layers deep I would like to be able to achieve 7 layers as things can get more complex than what I have listed. If anyone has any suggestions or a complete answer that would be helpful I would much appreciate it. The idea is to have them roll up to one another... in the below example all columns that are labeled as Lvl 1, Lvl 2, Lvl 3 & Lvl 4 do not actually exist. I have just added this to help with clarification/visualization.

Raw Data:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D5D9E2;;">Organizational Order</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D5D9E2;;">Level from Top</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D5D9E2;;">Lvl 1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D5D9E2;;">Lvl 2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D5D9E2;;">Lvl 3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D5D9E2;;">Lvl 4</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1.1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">1.1.1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">1.1.2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">1.1.3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">1.2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">1.2.1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">1.2.1.1</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">1.2.1.1.1</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">1.2.1.1.2</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">1.2.1.2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">1.2.1.3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">1.2.1.3.1</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">1.2.1.3.2</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">1.2.1.3.3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">1.2.2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">1.2.2.1</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style=";">1.2.2.1.1</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style=";">1.2.2.1.2</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style=";">1.2.2.1.3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style=";">1.2.2.1.4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style=";">1.2.2.1.5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style=";">1.2.2.1.6</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style=";">1.2.2.1.7</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style=";">1.2.2.1.8</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style=";">1.2.2.2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style=";">1.2.2.2.1</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style=";">1.2.2.2.2</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style=";">1.2.2.2.3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style=";">1.2.2.2.4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style=";">1.2.2.2.5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br />







Here is the code that I currently have.....


Code:
[COLOR=#0000cd]Sub[/COLOR] OutlineData()


    [COLOR=#0000cd]Dim[/COLOR] grpArr()     [COLOR=#0000cd] As Variant[/COLOR]
    [COLOR=#0000cd]Dim[/COLOR] grpTtl       [COLOR=#0000cd] As Integer[/COLOR]
    [COLOR=#0000cd]Dim[/COLOR] arrLp         [COLOR=#0000cd]As Integer[/COLOR]
    [COLOR=#0000cd]Dim [/COLOR]grpRowTop     [COLOR=#0000cd]As Long[/COLOR]
    [COLOR=#0000cd]Dim[/COLOR] grpRowBot     [COLOR=#0000cd]As Long[/COLOR]
   [COLOR=#0000cd] Dim[/COLOR] lRow          [COLOR=#0000cd]As Long[/COLOR]
    [COLOR=#0000cd]Dim[/COLOR] cycleLp       [COLOR=#0000cd]As Integer[/COLOR]


    lRow = Cells(Rows.Count, "B").End(xlUp).Row[COLOR=#008000] 'Get Last Row[/COLOR]
    grpTtl = Application.Max(Range("B2:B" & lRow)) [COLOR=#008000]'Get Max Level[/COLOR]
    
[COLOR=#008000]    'Set collapsable buttons adjacent to org level[/COLOR]
    Sheets("Sheet2").Outline.SummaryRow = xlAbove

[COLOR=#0000cd]    ReDim[/COLOR] grpArr(grpTtl)     [COLOR=#008000]'Create Array[/COLOR]
    
[COLOR=#008000]    'Update Array Values and set outlines[/COLOR]
 [COLOR=#0000ff]   For[/COLOR] arrLp = 0 [COLOR=#0000ff]To[/COLOR] grpTtl
    
         grpArr(arrLp) = arrLp
    
[COLOR=#0000ff]         Do[/COLOR]
            [COLOR=#0000ff]If [/COLOR]cycleLp = 0 [COLOR=#0000ff]Then[/COLOR]
               [COLOR=#0000ff]Set [/COLOR]fndItmTop = Sheets("Sheet2").Range("B2:B" & lRow).Find(grpArr(arrLp), , xlValues, , xlByRows, xlNext, False)
               grpRowTop = fndItmTop.Row + 1
[COLOR=#0000ff]            Else[/COLOR]
              [COLOR=#0000ff]  Set [/COLOR]fndItmTop = Sheets("Sheet2").Range("B" & grpRowBot + 2 & ":B" & lRow).Find(grpArr(arrLp), , xlValues, , xlByRows, xlNext, [COLOR=#0000ff]False[/COLOR])
              [COLOR=#0000ff]  If[/COLOR] fndItmTop [COLOR=#0000ff]Is Nothing Then[/COLOR]
                    grpRowTop = grpRowBot + 2
[COLOR=#0000ff]                Else[/COLOR]
                    grpRowTop = fndItmTop.Row + 1
[COLOR=#0000ff]                End If[/COLOR]
    
[COLOR=#0000ff]            End If[/COLOR]
            
         [COLOR=#0000ff]   Set[/COLOR] fndItmBot = Sheets("Sheet2").Range("B" & grpRowTop & ":B" & lRow).Find(grpArr(arrLp), , xlValues, , xlByRows, xlNext,[COLOR=#0000ff] False[/COLOR])
          [COLOR=#0000ff]  If[/COLOR] fndItmBot [COLOR=#0000ff]Is Nothing Then[/COLOR]
               grpRowBot = lRow
               grpDone = [COLOR=#0000ff]True[/COLOR]
[COLOR=#0000ff]            Else[/COLOR]
               grpRowBot = fndItmBot.Row - 1
[COLOR=#0000ff]            End If[/COLOR]
[COLOR=#008000]            'Group Rows[/COLOR]
            Rows(grpRowTop & ":" & grpRowBot).Rows.Group
            cycleLp = cycleLp + 1
         Loop Until grpDone = [COLOR=#0000ff]True[/COLOR]
[COLOR=#008000]         'Reset Counters[/COLOR]
         grpDone = [COLOR=#0000ff]False[/COLOR]
         cycleLp = 0
   [COLOR=#0000ff]  Next[/COLOR] arrLp
   
[COLOR=#0000ff]End Sub[/COLOR]

I appreciate any help someone can offer.
 
Last edited:

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Don't you just need to count the number of dots? All your Organisational Order data should be text. Some appear to be numbers as they are right aligned.
 

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,461
It appears that after the first level I could count the dots. Should I just go down column A and check cells one at a time for the number of dots? and look for when the gap occurs.... i.e. the gap between dot count.

I think I'll use this formula to accomplish this....

Code:
=LEN(A2)-LEN(SUBSTITUTE(A2,".",""))

I think I may have been looking at this particular issue to long.... hahahaahah

Seems easy enough. I'll post back when I have the solution.
 

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,461
Way Simpler than I was making it out to be......

Code:
[COLOR=#0000ff]Sub[/COLOR] OutlineData()

    [COLOR=#0000ff]Dim[/COLOR] grpTtl        [COLOR=#0000ff]   As Integer[/COLOR]
    [COLOR=#0000ff]Dim[/COLOR] LvlLp           [COLOR=#0000ff] As Integer[/COLOR]
 [COLOR=#0000ff]   Dim[/COLOR] lRow             [COLOR=#0000ff]As Long[/COLOR]
    [COLOR=#0000ff]Dim[/COLOR] IntLp         [COLOR=#0000ff]   As Integer[/COLOR]
    [COLOR=#0000ff]Dim[/COLOR] DotCount        [COLOR=#0000ff] As Integer[/COLOR]

    lRow = Cells(Rows.Count, "A").End(xlUp).Row [COLOR=#008000]'Get Last Row[/COLOR]
    grpTtl = Application.Max(Range("B2:B" & lRow)) [COLOR=#008000]'Get Max Level[/COLOR]
    ActiveSheet.Outline.SummaryRow = xlAbove [COLOR=#008000]'Set collapsable buttons adjacent to org level[/COLOR]
  
[COLOR=#008000]    'Outline Data[/COLOR]
   [COLOR=#0000ff] For [/COLOR]LvlLp = 0 [COLOR=#0000ff]To[/COLOR] grpTtl
        [COLOR=#0000ff] For[/COLOR] IntLp = 2 [COLOR=#0000ff]To[/COLOR] lRow
         DotCount = Len(Cells(IntLp, "A")) - Len(Replace(Cells(IntLp, "A"), ".", ""))
         [COLOR=#0000ff]   If [/COLOR]DotCount > LvlLp [COLOR=#0000ff]Then[/COLOR]
               Rows(IntLp).Rows.Group
[COLOR=#0000ff]            End If[/COLOR]
      [COLOR=#0000ff]  Next[/COLOR] IntLp
  [COLOR=#0000ff]   Next[/COLOR] LvlLp
   
[COLOR=#0000ff]End Sub[/COLOR]
 
Last edited:

Forum statistics

Threads
1,141,415
Messages
5,706,306
Members
421,441
Latest member
VapesRub

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top