How can I alphanumerically sort data in Excel with an exception?

Under the Sort & Filter options there should be a “Custom Sort” item. Go to that, then in the resulting window, click the down arrow under “Order”. There should be a “Custom List…” item. In that resulting window you can add you own custom sort list with whatever order you want them to be. So, in your case, I would add all the LS, CH, and other items in alphabetic order, but then put PT at the top.

Let me know if that works out for you.

I know another way if the solution above does not work for you.

Many thanks; I will try the above suggestion and report back.

I’d prefer to see the raw data and work with that. The sample data shown is from a “billing report”, which shows that it already has formatting applied (as well as record grouping “by patient” that will be lost in a sort of “all records”). That is, the data are sorted “by patient” and then each group of “patient rows” is separated from the next group by a blank line, and subtotaling is done by each group.

Working with the raw data we can preserve the patient grouping, sort by the 2-letter codes for each patient (after the patient sorting is done), and leave data ready to report again in subtotaled groups. But when you start with that grouping and subtotaling and then try to sort… I see nothing but trouble ahead.

This is actually pretty easy to sort if you’re using the raw data, but to do it with a finished report is asking for trouble. @erichw1504‘s way might work, but I wouldn’t hold my breath.

I agree with @CyanoticWasp. If you sort those rows, it doesn’t matter whether you keep the PTs where they are, All the blank lines will end up at the top of the column and the the others will be sorted in alphabetical order, but you will have a mess as far as your totals are concerned.

You are trying to use Excel as a database, and it’s not meant to be a database. Yes, they have a lot of database functions, but it’s just too picky to be easy to work with.

You would have been much better off had you started with a relational database, or even a flat file data set. But, you’re stuck with Excel, I guess. It would also help to know the significance of the codes. I don’t really understand what you’re trying to sort. It seems to me like you want to sort each block, not each line. By block, I mean each patient.

If that’s how you need to sort it…. I don’t know how to do that. I imagine if you could somehow designate a block of cells as having to be stuck together, maybe you could do it. I don’t know if Excel has that capability.

Excel pivot tables are would solve this perfectly. Learn to use pivot tables, never use subtotals.

It takes a little time to learn, but the payoff is huge. I haven’t used a subtotal in ten years and I use Excel a lot.

@wundayatta Exactly, I want to sort each block. Good way to put it.

I am a medical coder and biller. The data in these billing reports is the info I need to enter charges for patient visits at the physical therapy practice I work for. When you see your doctor and she writes notes about the visit, those notes go to someone like me so I can turn the note into numbers and send the numbers to insurance companies for remittance of the charges. We just got this nifty program that turns the words into numbers for me (which actually eliminates the interesting part of my job – now all I do is data entry), but the program is limited in how it can present the data to me. The heading row for each block refers to the patient name, diagnosis code, and treating physical therapist (we call them PTs in the biz), and the “LS, SH, LB” initials under the PT column are the therapists’ initials.

@CyanoticWasp I cannot manipulate the raw data to sort in the way I want it to. I have confirmed this with the company that makes the program which provides me with these billing reports.

Okay. Given the limitations you have to deal with, this isn’t so difficult, though it may still be time-consuming to do each block individually. Fortunately, Excel does have some intelligence built in to make it a bit less painful.

On any block of contiguous data (that is, all cells in the block have some kind of content) all you need to do is click on a single cell in the block, then select Data / Sort. Excel will attempt to outline the entire range of the block to be sorted (and usually gets it right). Then select the column/s you want to sort on, whether Ascending or Descending, and starting with the most important column first.

The key thing to keep in mind is to set Excel’s sort range to “include header row” (and your summary row is also excluded from the sort range). This means that the header row won’t sort with the rest of the data. Your data between the header and summary rows will be sorted as you request.

The pain is that you have to do this block by block by block.

Given a different data set, @jaytkay‘s advice would be excellent.