how to reverse date order of bank statement

Question

how to reverse date order of bank statement

I have .CSV documents from my bank which list transactions and a running balance with the most recent date at the top. For example, the first date would be 30th March with the 1st of March on the bottom.

There are often multiple entries for any given date, which I think creates problems if I just change the ascending/descending order in the date column - it certainly seems to mess up the figures.

So how do I reverse the order of all the transactions so the running total remains accurate? My bank does not allow me the option of downloading .csv docs in the order I want.

I do have Excel if that can do something Numbers can't but I much prefer working in Numbers!!

many thanks in advance!

edited to add:

Another way to ask this question is say I have 50 rows, how do I make row 50 become row 1, row 49 become row 2, while row 1 becomes row 50 etc.

cheers

Posted on Jan 9, 2013 3:47 AM

Reply

Answer 1

Yellowbox

Level 7

21,963 points

Jan 9, 2013 4:21 AM in response to Julian Morgan

Hi Julian,

You said:

"if I just change the ascending/descending order in the date column - it certainly seems to mess up the figures."

A thought:

Make sure that you sort the entire table, not just the date column!

Do not select any column before sorting. The Numbers default is to sort the whole table. Click on the "Reorganise" button in the toolbar and be sure, very, very sure, that you are telling Numbers exactly what to do!

Sorting can be risky. I know of many sets of data that have been turned into garbage.

Running totals are a different problem. After sorting, you will need to enter formulas to recalculate these. CSV (Comma Separated Values) is just a text file, no fomulas. I will have a think...

Regards,

Ian.

P.S. Save before sorting. Create another copy (File > Duplicate) under a different name before sorting.

Reply

Answer 2

Jan 9, 2013 4:45 AM in response to Yellowbox

Thanks for the suggestions Ian - however the problem with a bank statement is that if I sort by date when there are several entries for the same date, some entries are "sorted" while others are not. This is what seems to mess things up.

You're right - having chosen to "view formulae" with the idea of deleting them before sorting, I realise there aren't any - so the "mess up" I'm seeing is down to the fact that the row order is not being completely reversed when the date column is sorted - this happens whether or not I sort by the date column or the whole table, presumably because of how Numbers interprets "ascending/descending"

For one month ( I have another 11 to do!) I solved it by simply grabbing the top row in its entirety and dragging it to the bottom - everything moves up, so now I drag the new top row to the row just one up from the bottom etc.

I really do not want to have to do that for all the other spreadsheets - what about numbering the rows inside a column and then reversing the order of that column? Would that work?

Reply

Answer 3

Yellowbox

Level 7

21,963 points

Jan 9, 2013 5:43 AM in response to Yellowbox

Hi Julian,

Table before sorting:

Date	Detail	Transaction	Balance
8 Jan 2012	a	-100	whatever
7 Jan 2012	b	-24	whatever
6 Jan 2012	c	-33	whatever
5 Jan 2012	d	-450	whatever
4 Jan 2012	e	-115	whatever
3 Jan 2012	Salary	4999	whatever	I wish
2 Jan 2012	f	-457	whatever
1 Jan 2012	g	-10	whatever

I haven't bothered with the balance, because you will recalculate after sorting.

Here is the table after sorting Entire Table and **then** entering formulas:

Date	Detail	Transaction	Balance
1 Jan 2012	g	-10	1500
2 Jan 2012	f	-457	1043
3 Jan 2012	Salary	4999	6042	I wish
4 Jan 2012	e	-115	5927
5 Jan 2012	d	-450	5477
6 Jan 2012	c	-33	5444
7 Jan 2012	b	-24	5420
8 Jan 2012	a	-100	5320

Balance is Column E in this example.

E1 is opening balance (check with closing balance from previous month).

In Row 2, the formulas begin. In Cell E2, type this:

=E2+D3

Then Fill Down.

Regards,

Ian.

P.S. No. that is wrong. I missed the transaction on 1 January. Must go to bed (12:45 am here).

Reply

Answer 4

Yellowbox

Level 7

21,963 points

Jan 9, 2013 6:21 AM in response to Yellowbox

Hi Julian,

Couldn't sleep, but I now see my mistake.

E1 is the balance **after** the transaction on 1 January. (So, our pretend closing balance in the previous month was 1510).

That does not mean that the formulas in Column E are now correct, but now I can sleep.

Goodnight. Zzz

Ian.

Reply

Answer 5

Jan 9, 2013 10:14 AM in response to Julian Morgan

Julian,

I wonder if the problem may be that the Dates you imported aren't being recognized as dates and are being sorted by alphanumeric order rather than by date order. It's usually obvious if you look at the sorted data with an eye out for that problem.

Jerry

Reply

Answer 6

Jan 10, 2013 2:09 AM in response to Jerrold Green1

Jerrold and Ian - many thanks for all the ideas and insights - I think I have a way forward with the other eleven spreadsheets now. Not had time to test things out as yet.

Jerrold - your idea about the dates is good, but again, for the running balance to make sense then Numbers would somehow have to distinguish between two identical dates to get the order right when its reversed.

Will do some experimenting and report back.

Thanks!

Reply

Answer 7

Yellowbox

Level 7

21,963 points

Jan 10, 2013 2:22 AM in response to Julian Morgan

Julian,

I think your idea of adding another column (original order) before sorting in reverse should solve the problem of multiple transactions on the one date, and also should simply reverse the running balance - so no need to recalculate it.

Original order is also a good way to restore (provided you always sort entire table!).

Ian.

Reply

Answer 8

Jan 10, 2013 4:35 AM in response to Julian Morgan

Julian Morgan wrote:

Jerrold and Ian - many thanks for all the ideas and insights - I think I have a way forward with the other eleven spreadsheets now. Not had time to test things out as yet.

Jerrold - your idea about the dates is good, but again, for the running balance to make sense then Numbers would somehow have to distinguish between two identical dates to get the order right when its reversed.

Will do some experimenting and report back.

Thanks!

Julian,

If you are happy, so am I. If you'd like more help with this it would be good to see an example of the statement.

Good luck,

Jerry

Reply

Answer 9

Yellowbox

Level 7

21,963 points

Jan 9, 2013 4:55 AM in response to Julian Morgan

Julian,

Brilliant!

Add another column (call it "Sort") and fill down with numbers. Then sort Entire Table by that column.

Ian.

We all learn something every day.

Reply