Grouping of Hierarchy

[NickvdB]

Hi all,

Can someone help to have the following table auto-grouped? VBA would be easiest I think.
The goal is to have the hierarchy in a good overview people can get to the desired level and fill something in on the columns I will paste next to the table. And therefore Pivot will not work because of change rows when collapsing and expanding.

Level 1	Level 2	Level 3
1
1	1
1	1	1
1	1	2
1	1	3
1	2
1	2	1
1	2	2
2
2	1
2	2
2	2	1
2	2	2
2	2	3

<tbody>
</tbody>

Thanks

 

[Peter_SSs]

Can someone help to have the following table auto-grouped?

Care to show us what the expected results would look like for that sample data & explain in relation to those results?

 

[NickvdB]

Care to show us what the expected results would look like for that sample data & explain in relation to those results?

Level 1	Level 2	Level 3
+1
1	+1
1	1	1
1	1	2
1	1	3
1	+2
1	2	1
1	2	2
+2
2	+1
2	+2
2	2	1
2	2	2
2	2	3

<tbody>
</tbody>

So basically being able to collapse and expand with the plusses on these levels above. Just a normal grouping.

 

[Peter_SSs]

I'm not quite sure whether those '+' cells actually conatin that symbol. For the code below, I have assumed that they do not and the data looks like post 1.
To collapse/expand rows, you would double-click that top cell in a group (effectively a '+' cell) and the code should toggle hiding/showing that group of cells, always leaving the top row of the group visible.
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim s As String
  Dim r As Long, i As Long
  
  s = Target.Text
  r = Target.Row
  If Target.Column < 4 And r > 1 And Len(s) > 0 Then
    If Target.Offset(-1).Value <> s Then
      Cancel = True
      Do
        i = i + 1
      Loop Until Target.Offset(i).Text <> s
      If i > 1 Then
        Rows(r + 1).Resize(i - 1).Hidden = Not Rows(r + 1).Hidden
      End If
    End If
  End If
End Sub