How to sort Grouped data in Excel

**bool** · 06-30-2011, 03:23 PM

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?

**jeffreybrown** · 06-30-2011, 03:36 PM

How about a custom sort...

First by column B, then A, and then C

**bool** · 06-30-2011, 05:30 PM

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

**JBeaucaire** · 06-30-2011, 09:18 PM

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.

**MarvinP** · 06-30-2011, 09:24 PM

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.

**bool** · 06-30-2011, 10:04 PM

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

**bool** · 06-30-2011, 10:18 PM

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!

**JBeaucaire** · 06-30-2011, 10:34 PM

Try this:

Option Explicit

Sub SortSpecial()
Dim LR As Long

LR = Range("A" & Rows.Count).End(xlUp).Row

With Range("A1:C" & LR)
    .Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers
    .Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End With
    
With Range("A2:A" & LR)
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=COUNTIF($B$1:$B1,$B1)=1"
    With .FormatConditions(1).Font
        .Bold = True
        .Italic = False
    End With
End With
End Sub

**MarvinP** · 06-30-2011, 11:32 PM

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.

**JGravitas** · 09-08-2016, 09:48 AM

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.

**martindwilson** · 07-01-2011, 03:46 AM

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

**romperstomper** · 07-01-2011, 03:48 AM

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.

**MarvinP** · 07-01-2011, 09:38 AM

@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

**romperstomper** · 07-01-2011, 09:41 AM

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)

**bool** · 07-01-2011, 09:52 AM

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!

**romperstomper** · 07-01-2011, 09:52 AM

Glad to help.

**corrosion** · 06-29-2012, 01:47 PM

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!

**MarvinP** · 07-01-2011, 10:06 AM

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

I would have made mine easier if I knew.

**Noshiza** · 01-18-2012, 01:42 AM

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

**jeffreybrown** · 01-18-2012, 09:01 AM

Hi Blake,

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

**jeffreybrown** · 09-08-2016, 08:24 PM

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.

How to sort Grouped data in Excel

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How to sort Grouped data in Excel

Re: How to sort Grouped data in Excel

Re: How to sort Grouped data in Excel

Re: How to sort Grouped data in Excel

Re: How to sort Grouped data in Excel

Re: How to sort Grouped data in Excel

Re: How to sort Grouped data in Excel

Re: How to sort Grouped data in Excel

Re: How to sort Grouped data in Excel

Re: How to sort Grouped data in Excel

Re: How to sort Grouped data in Excel

Re: How to sort Grouped data in Excel

Re: How to sort Grouped data in Excel

Re: How to sort Grouped data in Excel

Re: How to sort Grouped data in Excel

Re: How to sort Grouped data in Excel

Excel Issue

Re: How to sort Grouped data in Excel

Re: How to sort Grouped data in Excel

Re: How to sort Grouped data in Excel

Re: How to sort Grouped data in Excel

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions