Uncle Bill’s solution gives you all claims sorted in descending order, then for claim amounts that are the same, sorted by client. That’s not what you wanted. It took me a minute to figure out what you’re getting at, which I will now paraphrase:
[ul]
[li]First sort by patients, in descending order of the highest claim for each patient[/li][li]Then sort in descending order of claim amounts for each patient[/li][/ul]
This is a non-answer but I am not sure you can do this unless you write macros to do it. I am not a VB macro guy so I can’t suggest whether that’s a viable direction.
The problem is that you need to sort by a value (highest claim for a patient) that is not contained in all the records for that patient. So you can’t do it with the Sort command.
One approach would be to reorganize your spreadsheet to add an extra column that would give the highest claim for each patient, then you could sort by highest claim, then by patient, then by individual claim. I would have to give it some more thought to determine how to do that automatically, not certain that it can be done automatically. You might have to make a manual pass through the data to set it up (could be done by using formulas but then you would have to paste in values to replace the formulas to preserve the data while sorting, and you would have to rework it every time the data changed).
You call this a “database” and say that it’s “large”. Together these tell me that Excel is the wrong tool for the job.
The info is being pulled from our main system and dropped into an Excel spreadsheet. (I call it a database out of habit)
The complete spreadsheet probably contains 9-10k claims. The person who used to create these reports (each biller gets their own based on their alpha split) is no longer here and we were unable to find out his method.
It’s been done this way for quite some time.
I nothing diddly about macros, but he was pretty good at Excel.
To clarify what I need:
The highest dollar claim followed by every other claim for that patient in descending order. These other claims should not appear anywhere else in the report.
This might not be the most elegant way to do it, but you could try this:[ol]
[li]First sort by claim amount in decending order[/li][li]Next highlight the column of patient names, go to Data>Filter>Advanced Filter. Check “Copy to another location” and “Unique records only”. This will give you a list of patients sorted in order of largest to smallest claims and will omit duplicates.[/li][li]Next, assign a rank to each patient in this new sub-list (just run numbers from 1-n in a column next to your new column of ranked patients)[/li][li]Next, use a VLOOKUP formula to grab the rank number for each patient and put it in a new column in your database.[/li][li]Now you can use the sort command to sort first by rank and then by claim amount and that should give you the order you want.[/ol][/li]
Hope it works for you.
A B C
1 Name Claim MaxClaim
2 Dog 5
3 Cat 10
4 Dog 21
5 Dog 2
6 Cat 17
I can grab both A and B then open up sort and do a Sort by Name Ascending 1st and Claim Descending as the 2nd, then in cell C2 I enter the formula
=IF(A2=A1,(MAX(B2,C1)),B2)
and fill down this to C6 giving …
A B C
1 Name Claim MaxClaim
2 Cat 17 17
3 Cat 10 17
4 Dog 21 21
5 Dog 5 21
6 Dog 2 21
Then grab all three columns and do a final sort using the keys, MaxClaim Descending, then Claim Descending.
Then delete the MaxClaim column if you don’t want it. There should be a way to do that in one step, or at least create the MaxClaim column without having to do the first sort but I can’t work it out right now.
Hopefully that’s what you wanted to do and you can apply that technique to your data.
Minor improvement over Bottle of Smoke’s solution (perhaps not even an improvement at all, depends on your needs):
a. Copy your data to Sheet2
b. Sort Sheet2 on the amount, descending
c. Enter a new fourth column in your original data with the value =VLOOKUP(A2,Sheet2!A:B,2,FALSE). (This is for the entry on row 2, and assuming your data is purely patient name, amount, which I’m sure it’s not)
d. Sort your original data, new column descending, then amount descending.
In case it isn’t obvious, in mine I’m creating a MaxClaim column, same as SpaceDog. I’d thought about his solution, but assumed it would break when the sort happens, since the values in his MaxClaim column will change (to be incorrect) during the sort.