Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Oct 2006
    Location
    Colorado Springs, Colorado, USA
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    complex sorting (2003)

    I have a client who wants to do a complex sort. She has a list of patients and discharge dates, and wants them grouped by patient name, ordered by discharge date within the patient name groups, and then wants the patient name blocks ordered by oldest discharge date for each name. For example:

    Moore, Mark 09/23/06
    Moore, Mark 01/01/07
    Moore, Mark 02/10/07
    Kurt, Katie 10/03/06
    Kurt, Katie 12/23/06
    Kurt, Katie 01/12/07
    Smith, Sara 11/07/06
    Smith, Sara 11/09/06
    Smith, Sara 12/11/06
    Smith, Sara 02/02/07

    I can get the first two easy enough, but how can I get Excel to sort groups of rows?

    Thanks in advance,
    -Torry

  2. #2
    Star Lounger
    Join Date
    Oct 2006
    Location
    Colorado Springs, Colorado, USA
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: complex sorting (2003)

    Very cool!

    For anyone reading this thread, the formula as noted in the previous post is slightly incorrect: it should replace C2 with A2. The spreadsheet example is right, and works perfectly!

    Thanks for the super-quick response!
    -Torry

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: complex sorting (2003)

    Edited to correct formula by HansC

    Say that your data are in columns A and B, starting in row 2. For illustration purposes, I'll assume rows 2 through 100.
    Add formulas in column C (insert an empty column if necessary)
    In C2: =MIN(IF($A$2:$A$100=A2,$B$2:$B$100))
    This is an array formula, i.e. it must be confirmed with Ctrl+Shift+Enter instead of just Enter.
    Fill down as far as needed (to C100 in this example).
    Use Data | Sort to sort on column C, then column A, then column B.
    You can hide column C if desired.

    See attached simplistic example.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: complex sorting (2003)

    Thanks for pointing out the typo, I have corrected it.

  5. #5
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: complex sorting (2003)

    Hi Hans:

    <hr>Edited to correct formula by HansC<hr>
    Would you like to correct your correction - or have you changed your name?? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: complex sorting (2003)

    <img src=/S/drop.gif border=0 alt=drop width=23 height=23>

    I'll ask HansD to correct the correction by HansC. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: complex sorting (2003)

    I was wondering who HansC was too.

    zeddy

  8. #8
    Star Lounger
    Join Date
    Oct 2006
    Location
    Colorado Springs, Colorado, USA
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: complex sorting (2003)

    The formula worked great for her. Now she is asking if there is a way to have the formula automatically update when she adds new entries to the list. This was easy in Excel2003, I just made the 3 columns into a list, and then entering new data on the insert line updated everything. Unfortunately, the end user is on Excel 2000, and doesn't have this option. Any suggestions on how to do this a different way?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: complex sorting (2003)

    You can define dynamic named ranges Names for column A and Dates for column B (see Insert | Name | Define in the attached workbook), and you can prefill column C with formulas that return a blank as long as the corresponding call in column A is blank.
    The array formula in C2 (confirmed with Ctrl+Shift+Enter) is
    <code>
    =IF(A2="","",MIN(IF(Names=A2,Dates)))
    </code>
    and this can be filled down as far as you like.
    Attached Files Attached Files

  10. #10
    Star Lounger
    Join Date
    Oct 2006
    Location
    Colorado Springs, Colorado, USA
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: complex sorting (2003)

    I figured the solution would involve named ranges, I just couldn't figure out the OFFSET and COUNTA stuff. Works great! Thanks a million.
    -Torry

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: complex sorting (2003)

    See Named Ranges on Chip Pearson's website for some background on named ranges, including dynamic ranges.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •