+ Reply to Thread
Results 1 to 21 of 21

How to sort Grouped data in Excel

  1. #1
    Registered User
    Join Date
    06-30-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    19

    Smile How to sort Grouped data in Excel

    I am working with a huge MS Excel 2010 spreadsheet to track projects and tasks within a project. Each task may have a different delivery date. I would like to group the data by project then sort the projects by earliest delivery date of any task within that project.

    So a simple sample of the data looks as follows:

    COLUMN A COLUMN B COLUMN C
    7/28/2011 Deploy Project Task a
    7/14/2011 AD Project Task b
    7/30/2011 AD Project Task a
    7/18/2011 Deploy Project Task d
    7/20/2011 Condolidation Project Task f
    7/28/2011 Deploy Project Task c

    etc etc

    So, what I am trying to acomplish is how to Group the data in COLUMN B (Project) then sort the Project Groups and each task within that Group by COLUMN A (Delivery date of task) while respecting the groupings. In other words the end result would look something like this:

    7/14/2011 AD Project Task b
    7/30/2011 AD Project Task a

    7/18/2011 Deploy Project Task d
    7/28/2011 Deploy Project Task a
    7/28/2011 Deploy Project Task c

    7/20/2011 Condolidation Project Task f

    Any advice on how to easily acomplish this?

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,054

    Re: How to sort Grouped data in Excel

    How about a custom sort...

    First by column B, then A, and then C
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    06-30-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: How to sort Grouped data in Excel

    That wouldn't work. It would sort projects by name A to Z or Z to A and then sort the tasks in the projects by delivery but it would not for example bring a project to the top of the list based on delivery date of one of it's tasks. Again I need to group each task row by Project Name THEN sort by earliest task delivery date while respecting the groupings.

    In the example above your solution would bring the Consolidation Project ahead of the Deploy Project because it starts with C even though the Deploy Project has a task due earlier

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to sort Grouped data in Excel

    The only way I can see this as manageable is probably with a macro (VBA). Are you ok with a VBA solution?

    To be crystal clear, you should upload a workbook with a BEFORE and an AFTER sheet showing fully what you want to accomplish. Your small dataset above "seems" to present clearly, but think about it now. If we're going to write a macro to reformat your BEFORE dataset into your AFTER dataset, think about it fully now and make sure all the issues that need to be overcome are represented in that data. Expand as needed, then mockup that AFTER sheet.

    Click GO ADVANCED and use the paperclip icon to post up your workbook.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,266

    Re: How to sort Grouped data in Excel

    Hi bool and welcome to the forum,

    I think Jeff may have been right but made a sample file to check his answer. Find attached what I think you are asking. If youi sort by date and then by Project you get the following.

    Let us know if we don't undersand the question.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  6. #6
    Registered User
    Join Date
    06-30-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    19

    Smile Re: How to sort Grouped data in Excel

    Hi MarvinP,

    Not what I am looking for. In your file, how can Project 1 with its earliest task due on 9/12/11 be sorted first than Project 9 with its earliest task due on 7/211 ?

    The desired outcome in your speadsheet example would be:

    7/2/11 Project 9 Task 49
    8/18/11 Project 9 Task 9
    9/24/11 Project 9 Task 39
    9/28/11 Project 9 Task 29
    11/19/11 Project 9 Task 19
    7/4/11 Project 6 Task 36
    8/13/11 Project 6 Task 16
    8/29/11 Project 6 Task 6
    10/17/11 Project 6 Task 46
    12/12/11 Project 6 Task 26
    7/9/11 Project 7 Task 37
    9/24/11 Project 7 Task 27
    11/24/11 Project 7 Task 47
    12/31/11 Project 7 Task 7
    1/1/12 Project 7 Task 17

    etc, etc

    Notice that I am looking to sort the projects by earliest task due date

  7. #7
    Registered User
    Join Date
    06-30-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    19

    Smile Re: How to sort Grouped data in Excel

    Hello JBeaucaire,

    I am up for any method that will provide an easy solution.

    I am attaching MarvinP's spreadsheet with two tabs that represent the sample data and the desired result after grouping and sorting.

    I am not familiar with macros or VBA

    Thank you!
    Attached Files Attached Files

  8. #8
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to sort Grouped data in Excel

    Try this:

    Please Login or Register  to view this content.
    Attached Files Attached Files

  9. #9
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,266

    Re: How to sort Grouped data in Excel

    Ok bool,

    See the attached. I've created a pivot table of the data where the rows are Project first. Then the summation is the MIN of the date for each Project. Then I added Task in Row area.

    To test this - Change any date or field in the Data (Cols A to C) and see the Pivot Refresh with an Event Macro behind sheet 1.

    This was a very tough problem but I think this solves it for you.
    Attached Files Attached Files

  10. #10
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How to sort Grouped data in Excel

    in marvins example to get the result you show
    sort the lot by column a
    in d2 put this array entered formula
    =MIN(IF($B$2:$B$51=B2,$A$2:$A$51,""))&B2
    confirmed with ctrl+ shift+enter
    drag down to last entry in col a
    now sort by column d
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  11. #11
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How to sort Grouped data in Excel

    Simple non-code method:
    1. Sort the data by Project.
    2. Data - subtotals, at change in Project use Min of Delivery date.
    3. Collapse data so only subtotals visible.
    4. Select and sort by Delivery date
    5. Remove subtotals.
    Remember what the dormouse said
    Feed your head

  12. #12
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,266

    Re: How to sort Grouped data in Excel

    @romperstomper -
    I've never worked with the Subtotal dialog. It looks like a winner for this problem. Is there a way to do this using the Subtotal Function in a helper column?
    Finding the Subtotal Dialog was like finding another blade on my swiss army knife of tools. Thanks for showing it to me/us.

    Marv

  13. #13
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How to sort Grouped data in Excel

    Not easily no as you would have to work out what ranges to apply it to (in which case you might as well use Min)

  14. #14
    Registered User
    Join Date
    06-30-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: How to sort Grouped data in Excel

    Thanks to Everyone for the solutions. I set to try all three solutions from simplest to more complex: Sub-totals, Pivot Table, VBA Script

    I give romperstomper the lead as his solution was the simplest (1-2 mins to implement) and provides the desired results. Thanks romperstomper!

  15. #15
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How to sort Grouped data in Excel

    Glad to help.

  16. #16
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,266

    Re: How to sort Grouped data in Excel

    I didn't know we were getting graded on our answers.

    I would have made mine easier if I knew.

  17. #17
    Registered User
    Join Date
    01-18-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: How to sort Grouped data in Excel

    Hello Everyone,

    I have just run in to this problem myself. I have a few groups or "Layers" as I like to call them of information. I have a master header which explains the level 2 information and then another header which explains level 3 information. What I am trying to do is to sort by the Level 2 headers and keep all the information underneath it linked to that header. That way I could pull the grouped row out and put it somewhere else and the information it had below it will stick with it. This isn't working for me in Excel 2003, I have read the replies and tried doing it the Subtotals way but that is no good. I am up for any suggestions!

    Thank you,
    Blake

  18. #18
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,054

    Re: How to sort Grouped data in Excel

    Hi Blake,

    Please follow the forum rules and post your question in your own thread...Thanks

  19. #19
    Registered User
    Join Date
    06-29-2012
    Location
    aliquippa
    MS-Off Ver
    Excel 2010
    Posts
    1

    Excel Issue

    I have a similar problem in Excel. I have two tasks in another row under a job and I grouped those 2 tasks to that job but when I try to do a sort it will not sort how I need it too. I tried ungrouping the cells but when i do that the tasks go to the bottom of the spreadsheet and group together but everything else groups the way i need it too. If anyone knows how to help me it would be much appreciated. Thank you!

  20. #20
    Registered User
    Join Date
    09-07-2016
    Location
    Connecticut
    MS-Off Ver
    10
    Posts
    1

    Re: How to sort Grouped data in Excel

    MarvinP, I know this topic was five years ago, but I found it after extensive Google searching for a solution to a problem similar to the original poster's. Your solution is almost exactly what I need. Actually, I think it IS exactly what I need, but I'm having a hard time re-creating it. I've created a pivot table that sorts the items in the correct order, but for the life of me I can't get it to actually list the "due date" as an actual DATE. Instead, it simply lists either "0" or "1"; if I try to format the cell as a date, it simply lists "1/0/1900." Any guidance is appreciated; I know it's been a long time.

  21. #21
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,054

    Re: How to sort Grouped data in Excel

    Hi JGravitas,

    As you can see from our Forum Rules, we ask you don't post your question in the thread of another member. In this case, with this thread being so old, it's especially better to create your own thread. If Marvin is not available right now, maybe another member can help out, plus, most members are not going to show too much interest in a thread with 20+ posts.

    2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread. You may include up to 3 links to other URLs in a single post, no more, so only link to the relevant pages.
    Last edited by jeffreybrown; 09-08-2016 at 08:26 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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