sort data on custom format

[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

 

[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.

 

[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.

 

[shaji]

thanks, it works fine.

shaji

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!*