Grouping Large Amounts Of Data in a hierarchy in Excel

Question

Hi this is my first post so please be gentle. I have a large amount of data being exported from Access into Excel. Access reports exported into Excel don't behave nicely otherwise I would use that method. All the calculations are being done in Access due to complexity and size of the data involved. I need to have a user friendly way of drilling down the data hence grouping in Excel would be perfect if I could get it to work.

The data has a hierarchy as per the below:

State
Store
Category
Item

At a top level you would just see the states with all the data so you can compare performance. You would then click the plus in the state you wanted to analyse which would show all the stores in the state, then you would click on a store to show the categories and then click on the category to show the items in the category.

I can add a Group index if required as per the below (I have included the data format also if that helps).

There are a number of states with up to 100 stores in each state 10 categories and then 100 items per category - so a lot of data and hence a macro.

Any help would be greatly appreciated as I am tearing my hair out.

Group   Description     Data Format
  1      State            AA
  2      Store            1
  3      Sub category     Text
  4      Item             2222
  4      Item             2223
  3      Sub category     Text A
  4      Item             2225
  4      Item             2226
  3      Sub category     Text B
  4      Item             2228
  4      Item             2229
  3      Sub category     Text C
  4      Item             2231
  4      Item             2232
  2      Store            2
  3      Sub category     Text
  4      Item             2222
  4      Item             2223
  3      Sub category     Text A
  4      Item             2225
  4      Item             2226
  3      Sub category     Text B
  4      Item             2228
  4      Item             2229
  3      Sub category     Text C
  4      Item             2231
  4      Item             2232
  1      State            BB
  2      Store            3
  3      Sub category     Text
  4      Item             2222
  4      Item             2223
  3      Sub category     Text A
  4      Item             2225
  4      Item             2226
  3      Sub category     Text B
  4      Item             2228
  4      Item             2229
  3      Sub category     Text C
  4      Item             2231
  4      Item             2232
  2      Store            4
  3      Sub category     Text
  4      Item             2222
  4      Item             2223
  3      Sub category     Text A
  4      Item             2225
  4      Item             2226
  3      Sub category     Text B
  4      Item             2228
  4      Item             2229
  3      Sub category     Text C
  4      Item             2231
  4      Item             2232   

Answer 1

From my answer in a different post:

Sub subGroupTest()
    Dim sRng As Range, eRng As Range
    Dim groupMap() As Variant
    Dim subGrp As Long, i As Long, j As Long
    Dim startRow As Range, lastRow As Range
    Dim startGrp As Range, lastGrp As Range

    ReDim groupMap(1 To 2, 1 To 1)
    subGrp = 0
    i = 0
    Set startRow = Range("A1")

    ' Create a map of the groups with their cell addresses and an index of the lowest subgrouping
    Do While (startRow.Offset(i).Value <> "")
        groupMap(1, i + 1) = startRow.Offset(i).Address
        groupMap(2, i + 1) = UBound(Split(startRow.Offset(i).Value, "."))
        If subGrp < groupMap(2, i + 1) Then subGrp = groupMap(2, i + 1)
        ReDim Preserve groupMap(1 To 2, 1 To (i + 2))

        Set lastRow = Range(groupMap(1, i + 1))
        i = i + 1
    Loop

    ' Destroy already existing groups, otherwise we get errors
    On Error Resume Next
    For k = 1 To 10
        Rows(startRow.Row & ":" & lastRow.Row).EntireRow.Ungroup
    Next k
    On Error GoTo 0

    ' Create the groups
    ' We do them by levels in descending order, ie. all groups with an index of 3 are grouped individually before we move to index 2
    Do While (subGrp > 0)
        For j = LBound(groupMap, 2) To UBound(groupMap, 2)
            If groupMap(2, j) >= CStr(subGrp) Then
            ' If current value in the map matches the current group index

                ' Update group range references
                If startGrp Is Nothing Then
                    Set startGrp = Range(groupMap(1, j))
                End If
                Set lastGrp = Range(groupMap(1, j))
            Else
                ' If/when we reach this loop, it means we've reached the end of a subgroup

                ' Create the group we found in the previous loops
                If Not startGrp Is Nothing And Not lastGrp Is Nothing Then Range(startGrp, lastGrp).EntireRow.Group

                ' Then, reset the group ranges so they're ready for the next group we encounter
                If Not startGrp Is Nothing Then Set startGrp = Nothing
                If Not lastGrp Is Nothing Then Set lastGrp = Nothing
            End If
        Next j

        ' Decrement the index
        subGrp = subGrp - 1
    Loop
End Sub