How to sort an accounting general ledger

Hi, welcome to the forum.

Getting your second column, with the debits and credits all together, can be done with a new column and a formula. I'm going to assume that your screenshots show columns A-D. If not, you'll need to adjust the formula below to match the correct column labels. First, insert a new column. Now, in the row which contains the words 'Accounts' and 'USD', enter this:

Formula:

Please Login or Register  to view this content.

Then drag the formula down as far as you need to - this will move all the debits and credits into one column, also making all the credits into negative amounts.
To format the numbers as you've shown, use this number format: #,##0.00;(#,##0.00)
Now select the column, copy it, then use 'Paste Special' to 'Paste Values' (this replaces all the formulae with the values you want).
You can now delete the original columns.


Filling in the account names in the first column can't be done with a formula, but you can use this method to fill in all the blanks:

In the column containing 'Accounts', select from 'Accounts' to the last row containing any debit/credit amounts (e.g. A2:A13).
Press F5 (which opens the Go To window).
Click 'Special' then select 'Blanks' then click 'OK' (this selects all the blank cells in your range).
Without clicking anywhere else, type '=' then press the up arrow.
Now press Ctrl and Enter together (this will fill in every blank with the text from the line above).

(say thank you to ChemistB for this little trick (see http://www.excelforum.com/excel-form...ml#post4161770))

As above, select the column, then copy and 'paste values' to replace the formulae with the required text.

There are undoubtedly folk on this forum who can write you a macro to do all of this, but I hope this helps in the meantime.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

Thanks for the formula.. And I've figured out the way to fill in the accounts names..

Untitled.jpg

You're welcome - glad you got what you needed - and thanks for the rep.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.