Grouping data together using Pivot Tables

nexus

New Member
Joined
Apr 13, 2013
Messages
18
7gFIoPS.png



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.
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Try building an outline based on values in Column A. That will let you collapse and expand the rows.

Denis
 

nexus

New Member
Joined
Apr 13, 2013
Messages
18
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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).
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,321
Messages
5,600,950
Members
414,417
Latest member
Nobu

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