FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


How to reverse rows in Excel?





infinisa
Hello All

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!
Fire Boar
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.
infinisa
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!
rickylau
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
Fire Boar
@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.
tingkagol
infinisa wrote:
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!

The faster way to do this is to press Ctrl while clicking and dragging down (or sideways) the cell containing the number '1'. Increments that number by 1 automatically for each row (or column) of your desired range.
Related topics
Frankfurt Motor Show, 2005
HTML tables with irregular rows and/or columns
How do i make "_derived/nortbots.htm"??
Working with Excel, PHP & MySQL. Any Ideas
Exporting to Excel and formatting textarea
Excel macros
from excel to php ?
phpBB 2.0.20 RELEASED
[help] Pregunta sobre Microsoft Excel
Adding blank rows
Reading MySQL values in reverse
Excel SpreadSheet to Website
Design question on database
Excel question
Reply to topic    Frihost Forum Index -> Computers -> Computer Problems and Support

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.