Conditional Sorting

[Remotruker]

I have an inventory stock report that I am attempting to sort b
priority of what needs to be done daily. I am trying to avoid a doubl
sort of the entire worksheet by writing a formula. In the attached fil
is my sample data. If possible i want to be able to sort the entire fil
by the %Neg collum.

The formula for the %Neg collum is
=IF(D12>0,(D12/E12),IF(D12<0,((D12/E12)/1),))


What I am attempting to accomplish is: If I2 is Negative then sort fro
Highest to Lowest. If I2 is positive then sort from Lowest to Highest.

Any help is appreciated

+-------------------------------------------------------------------
|Filename: Sample.doc
|Download: http://www.excelforum.com/attachment.php?postid=4410
+-------------------------------------------------------------------

 

[Pete_UK]

I haven't opened your file, but I assume column I is the same as %Neg
that you refer to. When you say:

If I2 is Negative then sort from Highest to Lowest.

do you mean "most negative" for Highest? i.e. is -100 "higher" than -10
? If so, can't you just sort on this column in ascending order, so you
go from -100 to -10 to +10 to +100?

Or, do you mean that you want the sort order to go -10, -100, +10, +100
?

Please clarify.

Pete

 

[Remotruker]

I haven't opened your file, but I assume column I is the same as %Neg
that you refer to. When you say:


do you mean "most negative" for Highest? i.e. is -100 "higher" than
-10
? If so, can't you just sort on this column in ascending order, so you
go from -100 to -10 to +10 to +100?

Or, do you mean that you want the sort order to go -10, -100, +10,
+100
?

Please clarify.

Pete

I'd like the sort from top to bottom

-5 , -6, -9, -100, -300, 5, 8, 15, 100, ect

 

[Pete_UK]

Assuming that column I is the one you want to sort on, you can add this
formula to J2:

=IF(i2<0,MIN(i:i) - i2 - 0.00001,i2)

then copy down for as many items as you have in column I. You can now
include column J in your sort range and sort on column J. Once sorted
you can delete column J. This will give you the sort order you require,
i.e. -5, -6, -9, -100, -300, 5, 8, 15, 100 etc.

The reason for the 0.00001 is to ensure that the minimum value does not
get treated as zero.

I don't understand the formula in your opening post - you are dividing
by 1(?)

Hope this helps.

Pete

 

[Remotruker]

Assuming that column I is the one you want to sort on, you can add this
formula to J2:

=IF(i2<0,MIN(i:i) - i2 - 0.00001,i2)

then copy down for as many items as you have in column I. You can now
include column J in your sort range and sort on column J. Once sorted
you can delete column J. This will give you the sort order yo
require,
i.e. -5, -6, -9, -100, -300, 5, 8, 15, 100 etc.

The reason for the 0.00001 is to ensure that the minimum value doe
not
get treated as zero.

I don't understand the formula in your opening post - you are dividing
by 1(?)

Hope this helps.

Pete

Yes this helps so much . The divide by 1 was a mistake
i was dividing by -1 in a previous attempt

 

[Pete_UK]

Ok, thanks for feeding back.

Pete