Grouping data together using Pivot Tables

[nexus]

Simple question. As you can see in Row A, I have the profit center with all the internal order numbers in Row B and comments on those orders in Row C. How do I make it so that I can collapse and expand both Row B and C when I click on the profit center in Row A? I know I can use pivot tables but when I create it, I can't get the data working like I want it too.

 

[SydneyGeek]

Try building an outline based on values in Column A. That will let you collapse and expand the rows.

Denis

 

[nexus]

Try building an outline based on values in Column A. That will let you collapse and expand the rows.

Denis

Thank you for the response. Auto-outline does not work for me when I go to the group button, but when I select and highlight the data from A2 till C100, the grouping hides the name of the profit center in A2. It happens when I select B2 and highlight down to C100 as well. How can I fix this problem?

 

[SydneyGeek]

Here's a VBA solution. It assumes that the headings in Column A are constants, not formulas.
Place the code in a new module, navigate to the sheet that you want to outline, and run the macro (Alt+F8 and double-click the macro name).

Sub SimpleOutline()
    Dim Dic As Object
    Dim b As Long, _
        rwLast As Long
    Dim c As Range
    Dim arRows()
    Dim i As Integer, _
        j As Integer
    Dim strTemp As String
    
    'clear the outline if it exists
    On Error Resume Next
    Columns("A:A").ClearOutline
    On Error GoTo 0
    
    rwLast = Range("B" & Rows.Count).End(xlUp).Row + 1
    Set Dic = CreateObject("Scripting.Dictionary")
    'use the Dictionary object to build an array of cell addresses
    For Each c In Range("A:A").SpecialCells(xlCellTypeConstants)
        b = b + 1
        Dic.Add c.Row, b
    Next c
    Dic.Add rwLast, b + 1
    arRows = Dic.keys
    Set Dic = Nothing
'    'testing
'    Range("J1").Resize(b, 1) = WorksheetFunction.Transpose(arRows)
    For i = LBound(arRows) To UBound(arRows) - 1
        j = i + 1
        strTemp = "A" & arRows(i) + 1 & ":A" & arRows(j) - 1
'        Debug.Print strTemp
        Range(strTemp).Rows.Group
    Next i    
End Sub

Denis