Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Group Pivot Data Based On Row Values In One Column

  1. #1
    Member mrmmickle1's Avatar
    Join Date
    Sep 2012
    Posts
    51
    Rep Power
    13

    Group Pivot Data Based On Row Values In One Column

    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.

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by mrmmickle1 View Post
    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)?

  3. #3
    Member mrmmickle1's Avatar
    Join Date
    Sep 2012
    Posts
    51
    Rep Power
    13
    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.
    Last edited by mrmmickle1; 10-09-2012 at 08:53 PM.

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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 dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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
    Attached Files Attached Files
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  6. #6
    Member mrmmickle1's Avatar
    Join Date
    Sep 2012
    Posts
    51
    Rep Power
    13
    Quote Originally Posted by Excel Fox View Post
    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.

  7. #7
    Member mrmmickle1's Avatar
    Join Date
    Sep 2012
    Posts
    51
    Rep Power
    13
    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
    Attached Files Attached Files
    Last edited by mrmmickle1; 10-09-2012 at 10:01 PM.

  8. #8
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    A more specific term for this kind of grouping in Excel is Outlining

    Yes, this can be done.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  9. #9
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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.
    Last edited by Excel Fox; 10-09-2012 at 11:46 PM. Reason: Comments added
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  10. #10
    Member mrmmickle1's Avatar
    Join Date
    Sep 2012
    Posts
    51
    Rep Power
    13
    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.

Similar Threads

  1. Replies: 3
    Last Post: 05-23-2013, 11:17 PM
  2. Replies: 17
    Last Post: 05-22-2013, 11:58 PM
  3. Offset based on Values in Column E
    By mrmmickle1 in forum Excel Help
    Replies: 7
    Last Post: 12-04-2012, 10:06 AM
  4. Autofill the data based on non blank cell in next row?
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 3
    Last Post: 11-29-2012, 04:16 PM
  5. Copy Row To A New Sheet If A Value Found In Adjacent Column
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 4
    Last Post: 08-17-2012, 05:42 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •