Sorting a column regardless of whether a value if + or -

[Steve]

Last week I posted a request for assistance on here.
I was looking to sort a spreadsheet by the value that
appeared in column column P regardless of whether or not
the value was a positive or negative number. (Row A
contained the headers).

Norman Jones provided me with a solution which nearly
worked, however, the macro was purely sorting column P and
not taking with it all the other values that appeared in
that row. Norman came back and said that I had to enlarge
the sort range, however, having looked at the code again
it is not exactly clear to me (being a novice) which part
I need to change. FYI there are 17 columns in the
spreadsheet and a maximum of 500 rows. I would appreciate
it if someone could put me back on the right track.

Many thanks
Steve

P.S. The original text has been copied below.


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



.

.

..

 

[Norman Jones]

Hi Steve,

Norman came back and said that I had to enlarge
the sort range, however, having looked at the code again
it is not exactly clear to me (being a novice) which part
I need to change.

Perhaps I was insufficiently clear. I could have said:

"To deal with this it is necessary ro enlarge the sort range and here is my
code to do this ..."

So try the latest code as is, on a *copy* of your workbook Post back with
any problems.

---
Regards,
Norman

Last week I posted a request for assistance on here.
I was looking to sort a spreadsheet by the value that
appeared in column column P regardless of whether or not
the value was a positive or negative number. (Row A
contained the headers).

Norman Jones provided me with a solution which nearly
worked, however, the macro was purely sorting column P and
not taking with it all the other values that appeared in
that row. Norman came back and said that I had to enlarge
the sort range, however, having looked at the code again
it is not exactly clear to me (being a novice) which part
I need to change. FYI there are 17 columns in the
spreadsheet and a maximum of 500 rows. I would appreciate
it if someone could put me back on the right track.

Many thanks
Steve

P.S. The original text has been copied below.


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



.



.

.