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?
Another sort question
-
- Administrator
- Posts: 12650
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Another sort question
StuartR
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Another sort question
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.
Next to the data add, 1,2, 3, 4, ...
Then sort that in descending order to reverse your data.
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Another sort question
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.
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.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 12650
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Another sort question
Thanks Rudi and Hans. I did think of doing that, but for some reason I assumed there might be a simpler way
StuartR
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Another sort question
You could copy the following macro into a module in your finance workbook, or in your personal macro workbook Personal.xlsb:
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.
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
The macro acts on the selected range.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 12650
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Another sort question
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.
StuartR