+ Reply to Thread
Results 1 to 4 of 4

TIP: Sorting with grouped rows

  1. #1
    Registered User
    Join Date
    10-01-2020
    Location
    London, England
    MS-Off Ver
    365
    Posts
    71

    Thumbs up TIP: Sorting with grouped rows

    Hi Community,

    I have been getting rather frustrated by this 'minor' issue.
    When working on a sheet with some rows grouped and others not grouped, sometimes when sorting the columns, the grouped rows don't maintain their grouped status if a particular sort is performed.

    See the attached workbook, it contains two sheets with the same data with one minor (turns out to be fundamental) difference, wonderfully colour coded to boot.

    Sheet 3 will maintain grouped rows when applying a sort to columns A or B, where sheet 2 will not.
    To see this, on Sheet 2, sort Column B, Z > A and then Column A, A > Z. All the rows will completely lose their grouping and will be impossible to set straight unless you 'Undo'.
    Sheet 3 however, sort all you like and the groups will maintain where set.

    In sheet 3, I have added in 4 additional rows. (Squished the first two small to not visually impact the sheet too much)
    One is using the value '-99' in the sortable columns, and grouped with a blank row beneath.
    Another is using the value 'zzz', again with a blank grouped row beneath.
    This technique ensures that the first row has a grouping when applying a sort to the columns. (Make sure '-99' is the LOWEST value and 'zzz' is the HIGHEST value in your range)

    Regardless of how big your data is and you can't maintain the grouping of rows, ensure the First row to be sorted is grouped.

    Hope this helps someone and saves time and a few more questions like this being asked.

    If there is a 'proper' technique that I have completely bypassed, let me know
    Attached Files Attached Files
    Last edited by AliGW; 12-15-2021 at 06:45 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,644

    Re: Q & A sorting with grouped rows

    You have marked this thread as solved - is it?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Registered User
    Join Date
    10-01-2020
    Location
    London, England
    MS-Off Ver
    365
    Posts
    71

    Re: Q & A sorting with grouped rows

    Hi Ali,

    Yes, it is a Q & A. I tried to find out why the sorting couldn't do what I wanted it to do but I didn't find any answer.
    So I discovered the reason and explained how I sorted it.
    Posted it here if anyone else finds it useful.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,644

    Re: Q&A Sorting with grouped rows

    OK - I've moved the thread to the correct forum section.

    Don't post this sort of thing in the main areas, please, or they will be treated as questions.

    Thanks for the contribution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 7
    Last Post: 07-01-2021, 04:31 AM
  2. Sorting multiple grouped rows
    By evancharles in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-13-2016, 12:11 AM
  3. Sorting grouped rows ?
    By Kariej1004 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-07-2014, 01:32 PM
  4. [SOLVED] Keep grouped rows together while sorting by a value in a cell
    By cbrandonsmith in forum Excel General
    Replies: 2
    Last Post: 04-06-2014, 06:45 PM
  5. Replies: 2
    Last Post: 01-10-2014, 09:30 PM
  6. [SOLVED] Sorting Grouped Rows and Formatted Cells
    By sonny.thind in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-29-2012, 01:47 PM
  7. [SOLVED] Sorting with multiple rows grouped
    By roguethirteen@hotmail.com in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-09-2006, 09:50 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1