Excel - Sorting within a Sort

Question

I have an excel spreadsheet of my todos. Each row in the spreadsheet is a todo and each column gives an attribute of that todo (for example, the priority of the todo). I also have three columns that express whether I want to do it today in the morning, in the afternoon, or in the evening.

I want to be able to sort these todos in the following way:

1) at the top, I want all of the todos for the morning. I want these todos sorted by priority,

2) then I want all of the todos for the afternoon. I want these todos sorted by priority,

3) then the todos for the evening, sorted by priority,

4) then all the rest of the todos.

More details:

• some todos have been given a priority from 1-5, but other todos have a blank cell in the priority column.

• If I want a todo for the morning, I will put a 1 in the morning column. For other todos, there might be a 0 in the morning column, or it might be blank. Similarly for the afternoon and evening columns.

Thanks.

EDIT: for example say I have the following data:

  Todo                 Priority    Morning   Afternoon  Evening
Write report               4          1    
Research                   5          1 
Dinner                                                     1
Prepare for meeting        3                     1
Read draft                 
Phone colleague            5                               1

I want it to be sorted as:

  Todo                 Priority    Morning   Afternoon  Evening 
Research                   5          1 
Write report               4          1   
Prepare for meeting        3                     1
Phone colleague            5                               1
Dinner                                                     1
Read draft                 

Answer 1

(Although it's not the ideal solution), a solution is to implement 'sorts' within a sorted table using VBA. The following code does this:

Sub SortByToday()
'
' SortByToday Macro
'

'
    Dim row As Long
    Dim rows As Long
    Dim topRow As Long
    Dim bottomRow As Long

    For row = 2 To 1000
        If Range("A" & row).Value = "" Then
            rows = row
            Exit For
        End If
    Next

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(Range("L2"), Range("L" & rows)) _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(Range("N2"), Range("N" & rows)) _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(Range("P2"), Range("P" & rows)) _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range(Range("A1"), Range("W" & rows))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    'find the number of morning todos
    For row = 2 To 1000
        If Range("L" & row).Value <> 1 Then
            bottomRow = row
            Exit For
        End If
    Next

    'sort on the morning todos only
    If bottomRow <> 2 Then
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(Range("D2"), Range("D" & bottomRow - 1)) _
            , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Sheet1").Sort
            .SetRange Range(Range("A2"), Range("W" & bottomRow - 1))
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End If

    topRow = bottomRow

    For row = topRow To 1000
        If Range("N" & row).Value <> 1 Then
            bottomRow = row
            Exit For
        End If
    Next

    If bottomRow <> topRow Then
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(Range("D" & topRow), Range("D" & bottomRow - 1)) _
            , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Sheet1").Sort
            .SetRange Range(Range("A" & topRow), Range("W" & bottomRow - 1))
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End If

    topRow = bottomRow

    For row = topRow To 1000
        If Range("P" & row).Value <> 1 Then
            bottomRow = row
            Exit For
        End If
    Next

    If bottomRow <> topRow Then
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(Range("D" & topRow), Range("D" & bottomRow - 1)) _
            , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Sheet1").Sort
            .SetRange Range(Range("A" & topRow), Range("W" & bottomRow - 1))
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End If

End Sub

Note:

1) Column A is where the todo text description is written. The first part of the code finds how many rows are in the table of todos.

2) The first sort sorts the todos by morning (column L), then by afternoon (column N) then by evening (column P).

3) The next for loop finds the number of rows that are a morning todo. Then the following sort sorts the morning todos by priority. Then the same thing is done for the afternoon todos and the evening todos.

Thanks to @fixer1234 for the idea.