Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Sorting Grouped Rows

  1. #1
    New Member mombo's Avatar
    Join Date
    Sep 2017
    Posts
    7
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Sorting Grouped Rows

    Using Excel 97, I made a spreadsheet that has groups of names and other information. Each group is preceded by a single row in the group having a date.

    This is an example of the organization:

    Date Name Location Status
    2017 - 04/13
    John Boston active
    David Chicago off
    Paul Detroit active
    Steven Dallas active
    Richard Miami off
    2017 - 02/15
    Donald Boston off
    Anthony Providence off
    Walter Seattle active
    Robert Dallas off
    2017 - 08/26
    Peter New York active
    Charles Orlando active
    Benjamin Sacramento off
    Victor Hartford off
    Jason DC off

    The dates are entered as a string of numbers, and the cells are formatted to display as shown.

    How can I sort the groups by date?

  2. #2
    Board Regular
    Join Date
    Jun 2017
    Location
    Nth Vic, Australia
    Posts
    593
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Sorting Grouped Rows

    This is not ideal!

    I developed a macro which is very specific to your table using the Developer Macro Recorder.
    It takes each specific date and copies it down against the other data belonging to that date.
    Those copy/pasted dates are then 'hidden'.
    When the sort is applied the illusion is that each date block has been separately selected and sorted.
    That also means that the blank row between date blocks is also lost.

    You can follow the development of this by reading the comments within the code.
    Code:
    Sub Blank_Date_sort()
    '
    ' Blank_Date_sort Macro
    '
    
    '
        'Select first date
        Range("A2").Select
        Selection.Copy
        'Copy date down
        Range("A3:A6").Select
        ActiveSheet.Paste
         'Select next date
        Range("A8").Select
        Selection.Copy
        'Copy date down
        Range("A9:A11").Select
        ActiveSheet.Paste
        'Select next date
        Range("A13").Select
        Selection.Copy
         'Copy date down
        Range("A14:A17").Select
        ActiveSheet.Paste
        'Set range to turn off font colour
        Range("A3:A6,A9:A11,A14:A17").Select
        Range("A14").Activate
        Selection.Copy
        With Selection.Font
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
        End With
        'Begin and do sort by date
        Columns("A:D").Select
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A17") _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Sheet1").Sort
            .SetRange Range("A1:D17")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End Sub
    Now, if you are basically comfortable with how that code worked then advise and then either myself or some other can attempt a modification which makes that code applicable to any listing that is structured as you offered; yeah, I reckon a blank row should be possible if needed.
    You may see and know but until you tell me exactly I am blind; I cannot read your mind.
    Attachments? No, but check for on site preferences.
    Cloud sharing or other off-site links? Yes, but off-site links are fragile into the future.

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    14,150
    Post Thanks / Like
    Mentioned
    259 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Sorting Grouped Rows

    @BrianJN1
    The OP is using Xl97, which does not support the modern colour formats you've used. Nor does it support the type of sorting you've used.
    @mombo
    you said
    The dates are entered as a string of numbers, and the cells are formatted to display as shown.
    What are the string of numbers?
    Last edited by Fluff; Dec 3rd, 2017 at 10:10 AM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  4. #4
    New Member mombo's Avatar
    Join Date
    Sep 2017
    Posts
    7
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sorting Grouped Rows

    Quote Originally Posted by Fluff View Post
    @BrianJN1
    The OP is using Xl97, which does not support the modern colour formats you've used. Nor does it support the type of sorting you've used.
    @mombo
    you said What are the string of numbers?
    I simply enter the four-digit year immediately followed by the two-digit month and two-digit day. The column is formatted to separate the year from the month and day by a " - " and the month and day are separated by a slash.

    I enter "20170413"
    and the formatting changes it to "2017 - 04/13"

    Does it matter how I do that, and if so, should I be doing something different?

  5. #5
    New Member mombo's Avatar
    Join Date
    Sep 2017
    Posts
    7
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sorting Grouped Rows

    Thank you for the code, Brian. I haven't tried it yet, but if it needs tweaking, I'll consult my daughter, who's a web application developer. I'm sure she'll be able to help me make it work for me.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    14,150
    Post Thanks / Like
    Mentioned
    259 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Sorting Grouped Rows

    It doesn't particularly matter how you do it. It's just a matter of knowing the cell values, for sorting purposes.
    I'll have a look at this later. I'm currently running something on Xl, that will take a long time to complete.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  7. #7
    New Member mombo's Avatar
    Join Date
    Sep 2017
    Posts
    7
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sorting Grouped Rows

    Okay. No rush. Thanks for your help.

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    14,150
    Post Thanks / Like
    Mentioned
    259 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Sorting Grouped Rows

    This should work in Xl 97
    Code:
    Sub SortGroupedRows()
    
        With Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlBlanks)
            .FormulaR1C1 = "=r[-1]c"
            .Font.Color = vbWhite
        End With
        
        With Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)
            .Value = .Value
        End With
    
        With Range("A" & Rows.Count).End(xlUp).Offset(1)
            .Value = .Offset(-1).Value
            .Font.Color = vbWhite
        End With
        
        Range("A1:D" & Range("A" & Rows.Count).End(xlUp).Row).Sort Key1:=Range("A1"), _
            Order1:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
            Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
        
        With Application.FindFormat
            .Clear
            .Font.Color = vbWhite
        End With
        Columns(1).Replace What:="", Replacement:="", SearchFormat:=True, ReplaceFormat:=False
        Application.FindFormat.Clear
        
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  9. #9
    Board Regular
    Join Date
    Jun 2017
    Location
    Nth Vic, Australia
    Posts
    593
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Sorting Grouped Rows

    @mombo, (#5) that code certainly would have needed tweaking even it hadn't failed the color parameters as pointed out by Fluff.
    My code merely looked at three specific blocks of data. My intent would have been to condense my code to something more general but still would not have been as efficient as Fluff has done.
    You may see and know but until you tell me exactly I am blind; I cannot read your mind.
    Attachments? No, but check for on site preferences.
    Cloud sharing or other off-site links? Yes, but off-site links are fragile into the future.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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