I need a sort to descend and ascend simultaneously

[Guest]

I have a "date column" (Column A) with this formula structure in all of the
rows of that column =IF($M2>0,$M2,IF($M2=0,"NA")). It feeds off of a revision
date column (column M). If there is no date in the "revision date column" it
shows up NA in column A. All of the NA's and dates are recorded in column A
through this formula.

Then I sort column A with a sort command button located at the top of column
A with this formula:
Private Sub CommandButton1_Click()
Range("A2").Sort Key1:=Range("A2"), Order1:= _
xlAscending, Header:=xlGuess, OrderCustom:=6, _
MatchCase:=False, Orientation:=xlTopToBottom
End Sub

This sorts the dates in ascending order with the NA's at the bottom.I need
to know if there is a formula that will allow me to sort column A with all of
the NA's rising to the top but the rest of the dates falling in ascending
order below the NA's. Is this feasible and did I explain it clearly?

 

[Guest]

Here's one way:

Change your formula from

=IF($M2>0,$M2,IF($M2=0,"NA"))

to

=IF($M2>0,$M2,IF($M2=0,1))

Create the following custom format and apply it to the whole column:

[=1]"N/A";m/d/yyyy

Of course the cells won't really contain N/A which may be a problem
depending on what you're doing.

 

[Guest]

It worked perfectly! Thank You

Here's one way:

Change your formula from

=IF($M2>0,$M2,IF($M2=0,"NA"))

to

=IF($M2>0,$M2,IF($M2=0,1))

Create the following custom format and apply it to the whole column:

[=1]"N/A";m/d/yyyy

Of course the cells won't really contain N/A which may be a problem
depending on what you're doing.
--
Cheers,
Shane Devenshire

I have a "date column" (Column A) with this formula structure in all of the
rows of that column =IF($M2>0,$M2,IF($M2=0,"NA")). It feeds off of a revision
date column (column M). If there is no date in the "revision date column" it
shows up NA in column A. All of the NA's and dates are recorded in column A
through this formula.

Then I sort column A with a sort command button located at the top of column
A with this formula:
Private Sub CommandButton1_Click()
Range("A2").Sort Key1:=Range("A2"), Order1:= _
xlAscending, Header:=xlGuess, OrderCustom:=6, _
MatchCase:=False, Orientation:=xlTopToBottom
End Sub

This sorts the dates in ascending order with the NA's at the bottom.I need
to know if there is a formula that will allow me to sort column A with all of
the NA's rising to the top but the rest of the dates falling in ascending
order below the NA's. Is this feasible and did I explain it clearly?

 

[Harlan Grove]

Here's one way:

Change your formula from

=IF($M2>0,$M2,IF($M2=0,"NA"))

to

=IF($M2>0,$M2,IF($M2=0,1))

Create the following custom format and apply it to the whole column:

[=1]"N/A";m/d/yyyy

....

Why not leave the column A formulas as-is and sort on column M?

Or make the column A formulas simple, e.g., A2: =M2 and format using

[=0]"NA";mm/dd/yyyy

? If these records sort correctly with missing column M values as 1 in
column A, they'd also sort correctly with missing column M values as 0
in column A.