1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Sort records by date with blanks last - Excel

Discussion in 'Business Applications' started by cpclemans, Oct 15, 2015.

Thread Status:
Not open for further replies.
Advertisement
  1. cpclemans

    cpclemans Thread Starter

    Joined:
    Oct 15, 2015
    Messages:
    5
    We have a list of ladies and the dates their babies are due. There's another column in the list with a date that they left our care - maybe moved away or transferred to another office. I'd like to sort the list by due date, unless they have left our care - and then have those ladies at the end of the list. So if the transfer date is blank, sort by due date, with all those transferred at the end.

    Advice please? Thanks!
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,653
    First Name:
    Wayne
    if you sort by the column they left your care and then by DueDate column
    that should work all blanks should then be together


    can you post an example
     
  3. DaveBurnett

    DaveBurnett Account Closed

    Joined:
    Nov 11, 2002
    Messages:
    12,970
    When I tried it it complained that blank was not a valid date!! I had formatted the column as date so that is not really surprising.
     
  4. cpclemans

    cpclemans Thread Starter

    Joined:
    Oct 15, 2015
    Messages:
    5
    Here's a sample of the worksheet - I am further stumped by the fact that we will be adding records to the list - so don't know how many there will be. Thanks
     

    Attached Files:

  5. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,653
    First Name:
    Wayne
    You already have a custom Sort setup
    which sorts by
    Date closed
    and then by
    Due Date

    Does that do what you are after ?

    Or do you use a different column from date closed ?

    see sorted file attached
     

    Attached Files:

  6. cpclemans

    cpclemans Thread Starter

    Joined:
    Oct 15, 2015
    Messages:
    5
    In the ideal world, the closed ones would be at the end of the list rather than the top...
     
  7. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,653
    First Name:
    Wayne
    thats just a different sort order

    But you need to use dates in the columns
    is that something you can do
    Also you have a formula in column A - which is just a number and reason for those ?
    and they will change as you sort ??

    can they just be a number ?
     

    Attached Files:

    Last edited: Oct 15, 2015
  8. cpclemans

    cpclemans Thread Starter

    Joined:
    Oct 15, 2015
    Messages:
    5
    Sorry if I am being dense...
    Column A is so that we can have a count - so we know how many are due excluding the ones that are closed.

    I reformatted the closed column to be dates - but the blanks seem to screw up the sort. I tried typing blank spaces into the blank cells, which helps, but it seems there should be a simpler solution!

    Thanks for your patience ! CArolyn
     
  9. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,653
    First Name:
    Wayne
    try this
    its a table - so the formulas will be automatic when you add a new row
    also on the Sort column O
    you can click on the arrow and sort from there

    I have also changed the column A to show the count of None closed rows
     

    Attached Files:

  10. cpclemans

    cpclemans Thread Starter

    Joined:
    Oct 15, 2015
    Messages:
    5
    That is exactly what I was looking for! Thanks so much. I haven't really used tables before - will have to start studying up. I appreciate you taking the time - I certainly wasn't going to figure this out on my own!
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Sort records date
  1. Totores
    Replies:
    1
    Views:
    172
  2. DKTaber
    Replies:
    6
    Views:
    209
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1158014

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice