Group Pivot Data Based On Row Values In One Column

**mrmmickle1** · 09-20-2012, 06:52 AM

I Have cross posted this on the Mr. Excel Forum. It is unanswered: Problem with data Grouping On this thread I have attempted to manipulate the below code unsuccessfully. I have data that I want to group based on values in column C. My data set has spaces (blanks "") in it in column C. I would like to group all data after "" values (blank) based on this occurring in column C (3). I have the following code which i adapted in the past however, I was unsure of the actually meaning of the code. And at this point I am more confused than ever. After trying to manipulate it for hours and having been unsuccessful I decided to seek help. The code I used in the past did the same thing based on "" values in column B. I have changed different things in the code and have realized that I was lucky to get it to work the first time. The code I have is as follows:

Code:

Sub GroupData()
Dim i As Integer, LastRow As Integer

LastRow = Cells(Rows.Count, 2).End(xlUp).Row

For i = 3 To LastRow

    If Not Left(Cells(i, 2), 3) = "" Then
        Cells(i, 1).EntireRow.Group
    End If

Next i
End Sub

Can someone offer code to fix my current dilemma? and explain what exactly the code does so that I can understand it and further my knowledge base. Thank you for any advice or solutions you can offer. If a solution is reached here I will also update the Mr. Excel Board Thread so that people's time is not wasted.

**Rick Rothstein** · 09-20-2012, 10:34 PM

Originally Posted by mrmmickle1

I have data that I want to group based on values in column C. My data set has spaces (blanks "") in it in column C. I would like to group all data after "" values (blank) based on this occurring in column C (3).

Clarification please - What is in Column C, actual data or formulas? If actual data, do you really have "" in the blank cells or is the cell empty (nothing in it at all)?

**mrmmickle1** · 10-09-2012, 08:42 PM

Rick,

First of all I want to apologize for the lapse of time for my response. This project was moved aside for a little bit. Here is the data that I currently have.

A.......................B......................... ........C....................D
1. Empty............Veneer SKU....................7/5/2011..........7/6/2011
2. Empty............Face............................. Empty..............Empty
3. Face.............1258 red oak...................3190................78956
4. Face.............2582 Cherry....................5658................5865 69
5. Empty...........Back.............................. Empty..............Empty
6. Back.............423185 NRO....................2635................28621
7. Back.............1564621 Oak...................55628..............4891689
8. Back.............348349 Green..................526542............852855623
9. Empty...........Core.............................. Empty..............Empty
10 Core..............565894 walnut................28568...............55235

Column C is actual data. Empty cells are actually empty (no value cells). No formulas exist in column C. I hope this helps. Please let me know if you need additional information. I tried to use an HTML maker but was unsuccessful getting it to work. Any explanation you could provide as to how the code works would be icing on the cake. I am currently facing a number of grouping problems. I am being pressured to use formatting with grouping on other projects as well. If I could understand the code, then I could better utilize it for these other projects. I appreciate you taking time to look at this issue.

**Excel Fox** · 10-09-2012, 09:09 PM

Do you want to attach a sample of the input and an expected output after the macro would be run? It would help in easily deciphering what is required, and provide a solution quickly.

**Excel Fox** · 10-09-2012, 09:19 PM

Seems you've given the expected output there already

.

Why don't you try a pivot table for this. Would be the easiest way out

**mrmmickle1** · 10-09-2012, 09:19 PM

Originally Posted by Excel Fox

Do you want to attach a sample of the input and an expected output after the macro would be run? It would help in easily deciphering what is required, and provide a solution quickly.

........A.......................B................. ........ ........C....................D
1. Empty............Veneer SKU....................7/5/2011..........7/6/2011
2. Empty............Face............................. Empty...............Empty
5. Empty............Back............................. . Empty..............Empty
9. Empty............Core............................. . Empty...............Empty

Rick,

It would have the data grouped like above. This is an example of the collapsed data. It would group all data between empty cells in column C and leave the actually rows containing the empty cells as a "Sub-Header" of Sorts. Column A will eventually be hidden in further code.

**mrmmickle1** · 10-09-2012, 09:58 PM

Rick,

First of all I must admit that I am not the best with Pivot Tables however, I have certainly used them on a number of occassions for OverTime Reports. Maybe I am missing something... I am not sure if this is quite what I am looking for. I have attached a file of a report that have created that has a similar final format for my needs, Except where Customers are at there would be Face, Core, Crossband, Core etc.... I have additional code to complete the rest of the formatting I am just having specific trouble with grouping.

Is this possible with the Pivot Table. Am I just not skilled at manipulating it? I apologize for my lack of expertise.

-Matt

**Excel Fox** · 10-09-2012, 10:58 PM

A more specific term for this kind of grouping in Excel is Outlining

Yes, this can be done.

**Excel Fox** · 10-09-2012, 11:12 PM

Try this

Code:

Sub Consolidator()

    Dim obj As Range
    
    'Since there is a possibility that there are already Outlines made in the used range, we'd want to ensure it is removed
    On Error Resume Next
    Do Until Err.Number <> 0
        Worksheets("Data").UsedRange.Rows.Ungroup'This is where we remove the outlining
    Loop
    Err.Clear: On Error GoTo 0: On Error GoTo -1'Clear up and reset the error handling
    For Each obj In Worksheets("Data").UsedRange.Columns(3).Cells.SpecialCells(xlCellTypeBlanks)'We use the specialcells method and pick only the blank cells, and then we loop through each blank cell
        If Not IsEmpty(obj.Offset(2)) Then'If the cell 2 rows below the blank cell is not empty, then that means there are at least 2 rows to be grouped, in which case End(xlDown) will take use to the last row for that section
            obj.Parent.Range(obj.Offset(1), obj.Offset(1).End(xlDown)).Rows.Group' so the section of the range that we want to group starts from the first cell below the blank cell, all the way down to the last cell in that group before the next blank, which can be located using End(xlDown) because there are at least 2 rows
        Else
            obj.Offset(1).Rows.Group'If the cell 2 rows below the blank cell is empty, we can assume that the section to be grouped has only 1 row, so we just group that row
        End If

    Next obj
    
End Sub

where "Data" is the name of the sheet where you have the data to be outlined. Change as suited.

**mrmmickle1** · 10-09-2012, 11:33 PM

Rick,

You never fail to amaze me. I just tested this and it works perfectly on my current set of data. I know that you have spent some time on this already but, would it be possible to further explain the workings of this code with a few comments? I am ofcourse trying to get better with vba :D I appreciate your efforts. I will post the solution on the Mr. Excel Thread as well.