MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sorting


Posted by SJC on March 21, 2001 2:35 PM

I have a table containing project description, open date, and close date. Some close dates are left blank. How can I sort the table in descending order of close date, but with the projects having blank close dates come out first then the most recently closed projects?


Posted by Aladin Akyurek on March 21, 2001 3:25 PM

A simple-minded solution:

Sort first on close date in ascending order. Then create a series in the column next to close date starting at 1. Then sort the whole thing on the series column in descending order.

Aladin

Posted by Celia on March 21, 2001 4:19 PM


Sort the whole data in close date ascending order then just sort the data that has close dates in descending order.
Celia

Posted by David Hawley on March 21, 2001 6:37 PM


Hi SJC

Asumming your "close date" are in Column A and your last Column in the table is Column D put this formula in E2:

=IF(A4="","Z","A")

Then your sort your entire table by Column D.

Dave

OzGrid Business Applications

Posted by Dave Hawley on March 21, 2001 6:38 PM

Make that: =IF(A2="","Z","A")

OzGrid Business Applications

Posted by Mark W. on March 22, 2001 4:46 AM

Using "close date" as the header for these dates
enter the following formula in a unused column
on the 1st data row, and copy down:

=IF('close date','close date',FALSE)

Sort these new values in descending order.