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

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

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" <bangbanjo@discussions.microsoft.com> wrote in message
news:38D9B50A-22E7-4E9E-8195-691228782F16@microsoft.com...
>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
>

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