Sorting regardless of whether a value is positive or negative

[Steve]

In column P of my spreadsheet I have a series of values
which I would like to sort in descending order regardless
of whether or not the number is a positive or a negative
number. The sort must display the nubers as they
originally appeared (i.e.with the negative attached if it
is a negative number) so the following list of numbers:

2346.70
-8246.06
360.00
-100.00
-6140.00

would appear as follows:
-8246.06
-6140.00
2346.70
360.00
-100

Is this possible ?

The only other thing to mention is that Row A of my
spreadsheet is populated with column headings.

Thanks in advance.

Steve

 

[Norman Jones]

Hi Steve,

Assume your numeric data to be sorted start in P2.

Perhaps you could insert a helper column, say column Q. In Q2 enter:

=abs(P2)

and copy down.

Then sort on the helper column.

The helper column could be hidden.

 

[Steve]

Norman

thanks for your reply.

I am trying to do this in a macro as the spreadheet which
I am sorting is different in length each week.

Is it possible to incorporate what you are saying in a
macro for all active rows ?

Steve

 

[Norman Jones]

Hi Steve,

Try something like:

Sub Tester()
Dim LastNum As Range

Application.ScreenUpdating = False

Set LastNum = Range("P1").End(xlDown)
Columns("Q").Insert
Range("Q2", LastNum(1, 2)).FormulaR1C1 _
= "=ABS(RC[-1])"
Range("P2", LastNum(1, 2)).Sort Key1:=Range("Q2"), _
Order1:=xlDescending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Columns("Q").Delete
Application.ScreenUpdating = True
End Sub

 

[Guest]

Norman

That suggestion nearly works, however, it purely sorts
column P (i.e. it does not move all the other values in
the row . It just moves column P, thus mixing up data
from different rows.

Anyway around this ?

Thanks
Steve

-----Original Message-----
Hi Steve,

Try something like:

Sub Tester()
Dim LastNum As Range

Application.ScreenUpdating = False

Set LastNum = Range("P1").End(xlDown)
Columns("Q").Insert
Range("Q2", LastNum(1, 2)).FormulaR1C1 _
= "=ABS(RC[- 1])"
Range("P2", LastNum(1, 2)).Sort Key1:=Range("Q2"), _
Order1:=xlDescending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Columns("Q").Delete
Application.ScreenUpdating = True
End Sub

---
Regards,
Norman

Norman

thanks for your reply.

I am trying to do this in a macro as the spreadheet which
I am sorting is different in length each week.

Is it possible to incorporate what you are saying in a
macro for all active rows ?

Steve

.

 

[Norman Jones]

Hi Steve,

You merely need to enlarge the sort range.

Assuming your data to have a header row and to be a contiguous range, try:

Sub Tester()
Dim LastNum As Range

Application.ScreenUpdating = False

Set LastNum = Range("P1").End(xlDown)
Columns("Q").Insert
Range("Q2", LastNum(1, 2)).FormulaR1C1 _
= "=ABS(RC[-1])"

Range("P1").CurrentRegion.Sort Key1:=Range("Q2"), _
Order1:=xlDescending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Columns("Q").Delete
Application.ScreenUpdating = True
End Sub

---
Regards,
Norman

Norman

That suggestion nearly works, however, it purely sorts
column P (i.e. it does not move all the other values in
the row . It just moves column P, thus mixing up data
from different rows.

Anyway around this ?

Thanks
Steve

-----Original Message-----
Hi Steve,

Try something like:

Sub Tester()
Dim LastNum As Range

Application.ScreenUpdating = False

Set LastNum = Range("P1").End(xlDown)
Columns("Q").Insert
Range("Q2", LastNum(1, 2)).FormulaR1C1 _
= "=ABS(RC[- 1])"
Range("P2", LastNum(1, 2)).Sort Key1:=Range("Q2"), _
Order1:=xlDescending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Columns("Q").Delete
Application.ScreenUpdating = True
End Sub

---
Regards,
Norman

Norman

thanks for your reply.

I am trying to do this in a macro as the spreadheet which
I am sorting is different in length each week.

Is it possible to incorporate what you are saying in a
macro for all active rows ?

Steve
-----Original Message-----
Hi Steve,

Assume your numeric data to be sorted start in P2.

Perhaps you could insert a helper column, say column Q.
In Q2 enter:

=abs(P2)

and copy down.

Then sort on the helper column.

The helper column could be hidden.

---
Regards,
Norman



message
In column P of my spreadsheet I have a series of values
which I would like to sort in descending order
regardless
of whether or not the number is a positive or a negative
number. The sort must display the nubers as they
originally appeared (i.e.with the negative attached if
it
is a negative number) so the following list of numbers:

2346.70
-8246.06
360.00
-100.00
-6140.00

would appear as follows:
-8246.06
-6140.00
2346.70
360.00
-100

Is this possible ?

The only other thing to mention is that Row A of my
spreadsheet is populated with column headings.

Thanks in advance.

Steve



.

.