sort data on custom format

S

shaji

Hi !

I am exporting data from my accounting software to excel. The column in
which the amount is showing is custom formatted to ''''0.00" Cr" for Credit
amounts and ""0.00" Dr" for debit amounts. I want sort all debit amounts in
the top of the list and Credit amounts in bottom. How can I sort the data
based on custom formatting.

thanks in advance.

shaji
 
F

Fred Smith

You can't. Sort sorts the values, not the formats.

When you simply sort the values, the credits and debits will be grouped
together because one's negative and the other is positive. Is this good
enough for you?

Regards,
Fred.
 
L

Luke M

First, insert a blank row to the right of your data. Next, right click on
sheet tab, view code, and paste this in:

'========
Sub DetermineFormat()

For Each cell In Selection
If cell.NumberFormat = """""0.00"" Cr""" Then
cell.Offset(0, 1).Value = "Credit"
ElseIf cell.NumberFormat = """""0.00"" Dr""" Then
cell.Offset(0, 1).Value = "Debit"
End If
Next cell
End Sub
'=======

Now, back in your workbook, select all the cells with your data in them, and
then run this macro (You can press Alt+F8 to bring up the macro menu). Your
data now has labels, and you can use the labels to sort, by descending, your
data.
 
S

shaji

thanks, it works fine.

shaji

Luke M said:
First, insert a blank row to the right of your data. Next, right click on
sheet tab, view code, and paste this in:

'========
Sub DetermineFormat()

For Each cell In Selection
If cell.NumberFormat = """""0.00"" Cr""" Then
cell.Offset(0, 1).Value = "Credit"
ElseIf cell.NumberFormat = """""0.00"" Dr""" Then
cell.Offset(0, 1).Value = "Debit"
End If
Next cell
End Sub
'=======

Now, back in your workbook, select all the cells with your data in them, and
then run this macro (You can press Alt+F8 to bring up the macro menu). Your
data now has labels, and you can use the labels to sort, by descending, your
data.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top