How do I sort subtotaled groups

P

projectile

Board Regular
Joined
Dec 14, 2007
Messages
193
Hi

I have a massive list if customers and each of their bookings for period of time, showing profit. I have sorted the list by customer name, so I can group the customer bookings together to see total job count + total profit.

see below

Code: 
<html>
<table
  <tr>
    <td width="225"><strong>Customer</strong></td>
    <td width="81"><div align="left"><strong>Job</strong></div></td>
    <td width="82"><div align="left"><strong>Booked</strong></div></td>
    <td width="87"><div align="left"><strong>Profit</strong></div></td>
    <td width="64"><div align="left"><strong>JobCount</strong></div></td>
  </tr>
  <tr>
    <td>ABC Marketing</td>
    <td align="right"><div align="left">123</div></td>
    <td align="right"><div align="left">20-Dec-08</div></td>
    <td align="right"><div align="left">448.54</div></td>
    <td align="right"><div align="left">1</div></td>
  </tr>
  <tr>
    <td>ABC Marketing</td>
    <td align="right"><div align="left">111</div></td>
    <td align="right"><div align="left">20-Apr-09</div></td>
    <td align="right"><div align="left">367.9</div></td>
    <td align="right"><div align="left">1</div></td>
  </tr>
  <tr>
    <td>ABC Marketing</td>
    <td align="right"><div align="left">127</div></td>
    <td align="right"><div align="left">24-Nov-08</div></td>
    <td align="right"><div align="left">299.51</div></td>
    <td align="right"><div align="left">1</div></td>
  </tr>
  <tr>
    <td><strong>ABC Marketing Total</strong></td>
    <td><div align="left"></div></td>
    <td><div align="left"></div></td>
    <td align="right"><div align="left"><strong>1115.95</strong></div></td>
    <td align="right"><div align="left"><strong>3</strong></div></td>
  </tr>
  <tr>
    <td>DFG Marketing</td>
    <td><div align="left">898</div></td>
    <td align="right"><div align="left">11-Mar-09</div></td>
    <td align="right"><div align="left">1451.1</div></td>
    <td align="right"><div align="left">1</div></td>
  </tr>
  <tr>
    <td>DFG Marketing</td>
    <td><div align="left">954</div></td>
    <td align="right"><div align="left">20-Jan-09</div></td>
    <td align="right"><div align="left">407.58</div></td>
    <td align="right"><div align="left">1</div></td>
  </tr>
  <tr>
    <td><strong>DFG Marketing Total</strong></td>
    <td><div align="left"></div></td>
    <td><div align="left"></div></td>
    <td align="right"><div align="left"><strong>1858.68</strong></div></td>
    <td align="right"><div align="left"><strong>2</strong></div></td>
  </tr>
</table>

</html>

Now, the problem is I need to sort the the subtotaled groups by profit, without losing the working figures. nor ungrouping the customers. for example

Code: 
<table cellspacing="0" cellpadding="0">
  <tr>
    <td width="225"><strong>Customer</strong></td>
    <td width="81"><div align="left"><strong>Job</strong></div></td>
    <td width="82"><div align="left"><strong>Booked</strong></div></td>
    <td width="87"><div align="left"><strong>Profit</strong></div></td>
    <td width="64"><div align="left"><strong>JobCount</strong></div></td>
  </tr>
  <tr>
    <td><strong>DFG Marketing Total</strong></td>
    <td><div align="left"></div></td>
    <td><div align="left"></div></td>
    <td align="right"><div align="left"><strong>1858.68</strong></div></td>
    <td align="right"><div align="left"><strong>2</strong></div></td>
  </tr>
  <tr>
    <td><strong>ABC Marketing Total</strong></td>
    <td><div align="left"></div></td>
    <td><div align="left"></div></td>
    <td align="right"><div align="left"><strong>1115.95</strong></div></td>
    <td align="right"><div align="left"><strong>3</strong></div></td>
  </tr>
</table>

And still be able to drill into the detail , for example;

Code: 
<body>
<table cellspacing="0" cellpadding="0">
  <tr>
    <td width="10"> </td>
    <td width="440"><strong>Customer</strong></td>
    <td width="81"><div align="left"><strong>Job</strong></div></td>
    <td width="82"><div align="left"><strong>Booked</strong></div></td>
    <td width="87"><div align="left"><strong>Profit</strong></div></td>
    <td width="64"><div align="left"><strong>JobCount</strong></div></td>
  </tr>
  <tr>
    <td> </td>
    <td>DFG Marketing</td>
    <td><div align="left">898</div></td>
    <td align="right"><div align="left">11-Mar-09</div></td>
    <td align="right"><div align="left">1451.1</div></td>
    <td align="right"><div align="left">1</div></td>
  </tr>
  <tr>
    <td> </td>
    <td>DFG Marketing</td>
    <td><div align="left">954</div></td>
    <td align="right"><div align="left">20-Jan-09</div></td>
    <td align="right"><div align="left">407.58</div></td>
    <td align="right"><div align="left">1</div></td>
  </tr>
  <tr>
    <td> </td>
    <td><strong>DFG Marketing Total</strong></td>
    <td><div align="left"></div></td>
    <td><div align="left"></div></td>
    <td align="right"><div align="left"><strong>1858.68</strong></div></td>
    <td align="right"><div align="left"><strong>2</strong></div></td>
  </tr>
  <tr>
    <td><div align="center"><strong>+</strong></div></td>
    <td><strong>ABC Marketing Total</strong></td>
    <td><div align="left"></div></td>
    <td><div align="left"></div></td>
    <td align="right"><div align="left"><strong>1115.95</strong></div></td>
    <td align="right"><div align="left"><strong>3</strong></div></td>
  </tr>
  <tr>
    <td> </td>
    <td> </td>
    <td></td>
    <td></td>
    <td align="right"> </td>
    <td align="right"> </td>
  </tr>
</table>
</body>

When I try to sort the subtotaled groups, I get this error:

"This removes the subtotals and sorts again. If you want to sort the subtotaled groups, choose the Cancel button; hen collapse the the outline and try again"
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Hi Everyone, has anyone got any ideas for this problem? Cannot figure it out, and somehow got it to work in past, and cannot remember how I did it :mad:
 
Upvote 0
I can't recreate your problem. It works fine for me.

To check...
Once you've created your subtotals, set it show the totals and not the individual line.
Then click on one of the totals and sort it.

What version of Excel are you using?
 
Upvote 0
Hi, thanks for looking at this.

I'm using Excel 2003.

Let me try what you suggested, and I will revert with findings. Cheers
 
Upvote 0
Ok, did what you suggested and doesn't work.

This is what i'm doing

1. sort extract in the sheet by customer.
2. click Subtotals... and use config;

At each change in = Customer

Use function = Sum

Add subtotal to = Profit , Job Count

On the sheet, click the level 2 subtotal view ( show subtotaled group totals)

Then i click on one of the totals, and click sort (this highlights the entire range)

Then sort by Profit header

Then I get that error "This removes the subtotals and sorts again. If you want to sort the subtotaled groups, choose the Cancel button; hen collapse the the outline and try again"


FYG, I haven't disclosed all the columns in this example - should think that matters?
 
Upvote 0
Manage to solve this issue , by unticking the Summary Below Data - this default setting was causing the issue.:mad:
 
Upvote 0
You must log in or register to reply here.

Similar threads

charlesstricklin
  • Question
How do I calculate a column based on two criteria?
2
Replies
11
Views
280
Joe4
Joe4
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
M
  • Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
D
  • Question
I can't remember how to do it.
Replies
2
Views
448
dpaton05
D
L
  • Question
VBA to Keep Subtotal Groupings Based on Conditional Formatting
Replies
0
Views
411
LtCmdrData
L

Forum statistics

Threads
1,214,606
Messages
6,120,490
Members
448,967
Latest member
visheshkotha

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top