0

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                 
7
  • Can anyone explain the down vote? Happy to try and explain myself better. If there is already an answer for this please let me know. I had a good look and couldn't find anything, but wasn't sure what search terms to use. – James Sep 9 '16 at 4:00
  • Have you looked at the built-in multi-column sort? You can do it in several steps if you have too many columns. – fixer1234 Sep 9 '16 at 4:47
  • 1
    You can do several columns at once. Sort all of priority in the first sort, then it will already be in order when you sort the remaining columns. That will save several steps. You should be able to do this in two steps. – fixer1234 Sep 9 '16 at 5:22
  • 1
    I don't have ready access to Excel, and I forget how many sort selections you can do in a pass, but I know it's at least two. So the steps would be: always select the entire block of data. 1st pass: 1st sort col=Morning, 2nd sort col=priority. 2nd pass: 1st sort col=evening, 2nd sort col=afternoon. – fixer1234 Sep 9 '16 at 5:38
  • 1
    Select the entire block of data, then select Sort from the menu. I forget whether it immediately includes several places to enter sort columns or you click on something to add additional sort columns, but you can specify several columns to sort on. The top entry window is labeled first sort column or similar. Enter the column letter and specify sort direction. The next entry window is labeled second sort column or similar; enter the column letter there and select sort direction. Click OK, it does the sort. That's a pass. Do that again with two more columns for the second pass. – fixer1234 Sep 9 '16 at 6:13
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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.