1. ## 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?

-Torry

2. ## 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. ## 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.

4. ## Re: complex sorting (2003)

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

5. ## 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>

6. ## 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. ## Re: complex sorting (2003)

I was wondering who HansC was too.

zeddy

8. ## 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. ## 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.

10. ## 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. ## 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
•