Sorting within Boxes (revisited)

**jomili** · 06-24-2011, 01:38 PM

I need to revisit a closed thread (http://www.excelforum.com/excel-prog...hin-boxes.html).

RomperStomper gave me a great macro that I thought did what I needed, but it doesn't quite do it. See the attached file. On lines 148 and 149 of each tab you can see the issue. In the Unsorted, the correct folks report to the correct PACs. In the Sorted, different folks are reporting to the PACs.

Here's the macro Romper gave, which would be applied to the "Unsorted" tab to sort it correctly. I don't understand the macro well enough to try my hand at altering it, so hope someone older and wiser (or at least wiser) would take a look. Thanks in advance.

Please Login or Register  to view this content.

**realniceguy5000** · 06-24-2011, 03:11 PM

Maybe?

I just added a check in the script, it appears a couple lines up from the problem you really had nothing to sort ,because it was only one line?

Please Login or Register  to view this content.

...

**realniceguy5000** · 06-24-2011, 03:29 PM

That may not be the issue after all. I see your talking about PAC in your post. The pac are in col B the sort in not sorting col B.This is the first instance of 2 pacs in a col which appears to be the problem?

So maybe you just need to include col B in the sort?

Please Login or Register  to view this content.

**jomili** · 06-24-2011, 03:49 PM

Real Nice Guy,

You're right that we need to include PAC in the sort. The output you see in Unsorted comes directly from a pivot table, so everything Under a DeptID or a PAC needs to stay associated with that particular DeptID and PAC. What we're wanting to sort is all of the other columns, to put the Supv Names in order, and then the Names.

The hierarchy is like this:

DeptID 1st, PAC 2nd, Supv Name withing DeptID and PAC, then Name.

I tried your latest code, and it didn't work the way expected. I've attached a new workbook; see the "Experiment" sheet (which happens when I run the macro on the "Unsorted" sheet). Notice in line 129 how the PAC has moved down. In that area, the PAC should have stayed at the top of the DeptID field.

**realniceguy5000** · 06-24-2011, 04:05 PM

Sorry not sure I follow, lol maybe just slow today...

Are you saying that everyone in rows 148-157 are pac 25000 except Michelle B who is 11006 so really you are only sorting the 25000 group?

at least for this instance?

Thank You, Mike

**jomili** · 06-24-2011, 04:09 PM

Yep, that's exactly the situation.

**realniceguy5000** · 06-24-2011, 05:14 PM

Try This?

It looks like the problem may be with more than 2 numbers in range B, We may need to add a case or more if statments based of the mycount number. However see if this is any closer...

Please Login or Register  to view this content.

**jomili** · 06-28-2011, 10:03 AM

RealNiceGuy, I posted a reply with an attachment yesterday, and just came back to check becuase I wondered why you hadn't replied to it!. Apparently my post has vanished into thin air! Sorry about that; here's the essence of what it said:

Your last macro ALMOST does the trick. The only thing I could see wrong (or not as right as I would like it) is shown on the Experiment tab (running your new macro against the Unsorted tab). See lines 180 and 181. Within PAC 11006 the SupvNames should be in order. Within SupvNames, the Names should be in order. This example looks like something different is happening.