Another sort question

[StuartR]

When I download data from my bank it is sorted like this

21/10/2014 description1 amount1
15/10/2014 description2 amount2
15/10/2014 description3 amount3
14/10/2014 description4 amount4

etc.

I need to sort this into the reverse order, before I paste it into the spreadsheet that I use for tracking my finances.

If I just sort it on the date column then (for example) the two entries for 15/10/2014 come out in the wrong order. This matters because the intermediate balances in my spreadsheet then don't match the ones on the bank statement.

What would be the easiest way to sort this data?

[Rudi]

How about adding a sequence column and sorting on that...
Next to the data add, 1,2, 3, 4, ...
Then sort that in descending order to reverse your data.

1.jpg

[HansV]

Same idea as Rudi's: In the column next to the data, enter 1 in the first cell, and 2 in the second cell:

21/10/2014 description1 amount1 1
15/10/2014 description2 amount2 2
15/10/2014 description3 amount3
14/10/2014 description4 amount4

Select those two cells, then fill down the last row with data.
Select a single cell in the column with the numbers, and sort from highest to lowest.

[StuartR]

Thanks Rudi and Hans. I did think of doing that, but for some reason I assumed there might be a simpler way

[HansV]

You could copy the following macro into a module in your finance workbook, or in your personal macro workbook Personal.xlsb:

Code: Select all

Sub ReverseSelection()
    Dim arr
    Dim r As Long
    Dim c As Long
    Dim m As Long
    Dim n As Long
    Dim tmp
    m = Selection.Rows.Count
    n = Selection.Columns.Count
    arr = Selection.Value
    For r = 1 To m \ 2
        For c = 1 To n
            tmp = arr(r, c)
            arr(r, c) = arr(m, c)
            arr(m, c) = tmp
        Next c
        m = m - 1
    Next r
    Selection.Value = arr
End Sub

You can assign it to a Quick Access Toolbar button, ribbon button and/or keyboard shortcut for ease of use.
The macro acts on the selected range.

[StuartR]

Thanks Hans. I may do that, I'm still thinking on whether it's worth doing. I may just go for the add a column each time I download the data.