I need to reverse the order of rows in an Excel 2007 spreadsheet, but I don't how.
A bit of background here:
I am downloading my bank statement (in Excel format), which used to appear with the oldest transactions first, but now suddenly appears with the newest transactions first. In order to add the new records to my existing Excel spreadsheet, I need to reverse the order.
You might think I could just order on date ascending, but there can be several records with the same date, which could get out of order, and so mess up the running balance.
Any ideas welcome!
I remember someone else having a problem like this... you can solve it by creating a macro to move down one space and enter a 1 in the box. That's the initial macro, anyway, after that you need to edit the macro manually to change it from "enter 1" to "enter a number that is 1 more than in the previous box". This way, you can quickly fill out a column with numbers ascending by typing 1 where you want to start, then running the macro a number of times equal to the number of rows you have (you could also put a loop in the macro). This will then be sortable. You can't do it with a formula unfortunately, because the columns would then cease to be sortable. Unless there's some way of converting calculated values to absolute values.
Hi Fire Boar
Thanks for the solution!
In fact, I did do it using a formula (I'm not used to macros):
- Create a column for the row number
- Put 1 in the cell for the first row
- In the cell below, put a formula to add 1 to the cell above (which of course gives 2)
- Copy this formula down the rest of the column, to fill the row number correctly
- Do a copy & paste special of this column onto itself, with the option "paste values" (this converts the formulae into actual numbers - this seems to be the bit you missed)
- Now use the row number column to order the rows
And there you are!
Hmm, if you have an ordered column and you have the header on the spreadsheet, you can simply use the sorting & filter function (or filter & sorting or something like that), which you can sort the whole sheet in ascending/descending order of your reverse-ordered column.
And actually for your way, if you entered 1 in first row, just drag the black dot at bottem right corner of the selection to lower rows, Excel will fill 2, 3, 4, 5, 6 etc. for the coming rows for you. No macros or formulae are needed.
Hope this helps you in further tasks like that
@rickylau: That's exactly what the OP did, if you read the post.
@infinisa: Glad you found a solution - that way is a lot easier than what I suggested. You're right, I missed the hidden "paste special" option... didn't occur to me to give that a go.