Thread: How to sort Grouped data in Excel

How about a custom sort...

First by column B, then A, and then C

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.

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.

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

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.

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

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.

@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

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)

Glad to help.