Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
bangbanjo
 
Posts: n/a
Default Sort ignoring negatives and positives

I need to sort a column of number values by the value ignoring negatives and
positives. Example take the list:

-5000
-4000
-1500
1500
4000
5000

and sort into this list
-5000
5000
-4000
4000
-1500
1500

  #2   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

You would need a help column, you can insert a temporary help column and use
a formula like


=ABS(A1)


copy down, select both columns and sort by the help column descending


--
Regards,

Peo Sjoblom

(No private emails please)


"bangbanjo" wrote in message
...
I need to sort a column of number values by the value ignoring negatives
and
positives. Example take the list:

-5000
-4000
-1500
1500
4000
5000

and sort into this list
-5000
5000
-4000
4000
-1500
1500


  #3   Report Post  
dominicb
 
Posts: n/a
Default


Good evening BangBanjo

The only way I can think of doing this using the Excel sort function is
to have an additional column inserted to use a sort key. If your data
is in column A, starting at A1, put in column B1 =ABS(A1) and copy it
down. This will give the absolute value (ie ignore minuses) and use
this as your sort key. As this column is a formula it will change as
the data in column A changes and could be hidden if required.

Or you could write a macro to do this without the intermediate step...

HTH

DominicB


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=376939

  #4   Report Post  
bangbanjo
 
Posts: n/a
Default

Thanks I will try that

"dominicb" wrote:


Good evening BangBanjo

The only way I can think of doing this using the Excel sort function is
to have an additional column inserted to use a sort key. If your data
is in column A, starting at A1, put in column B1 =ABS(A1) and copy it
down. This will give the absolute value (ie ignore minuses) and use
this as your sort key. As this column is a formula it will change as
the data in column A changes and could be hidden if required.

Or you could write a macro to do this without the intermediate step...

HTH

DominicB


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=376939


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 01:22 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"