Subtotal Detail Sorting

ErCoGor

New Member
Joined
Apr 2, 2018
Messages
2
Hello!
I have been researching this question for a while and don't know if I'm misunderstanding what I've read, or if my question truly isn't being answered... so, I'm hoping to try and explain it here and would love some help!!
I am using Subtotal to understand what Categories have highest $ value. However, I'd also like to sort the "subcategories" within the Subtotal of each Category by $ value, as well.

What I'm currently seeing is:

[TABLE="class: grid, width: 608"]
<tbody>[TR]
[TD]Product Class[/TD]
[TD]Family[/TD]
[TD="align: right"]Sales Amt[/TD]
[/TR]
[TR]
[TD]XXX[/TD]
[TD]subcat 6[/TD]
[TD="align: right"]$78.86[/TD]
[/TR]
[TR]
[TD]XXX[/TD]
[TD]subcat 1[/TD]
[TD="align: right"]$5,282.14[/TD]
[/TR]
[TR]
[TD]XXX[/TD]
[TD]subcat 5[/TD]
[TD="align: right"]$97.07[/TD]
[/TR]
[TR]
[TD]XXX[/TD]
[TD]subcat 3[/TD]
[TD="align: right"]$507.12[/TD]
[/TR]
[TR]
[TD]XXX[/TD]
[TD]subcat 4[/TD]
[TD="align: right"]$380.73[/TD]
[/TR]
[TR]
[TD]XXX[/TD]
[TD]subcat 2[/TD]
[TD="align: right"]$1,080.91[/TD]
[/TR]
[TR]
[TD]XXX[/TD]
[TD]subcat 7[/TD]
[TD="align: right"]$74.39[/TD]
[/TR]
[TR]
[TD]XXX Total[/TD]
[TD][/TD]
[TD="align: right"]$7,501.22[/TD]
[/TR]
[TR]
[TD]XXXXX[/TD]
[TD]subcat 9[/TD]
[TD="align: right"]$129.46[/TD]
[/TR]
[TR]
[TD]XXXXX[/TD]
[TD]subcat 10[/TD]
[TD="align: right"]$47.26[/TD]
[/TR]
[TR]
[TD]XXXXX[/TD]
[TD]subcat 6[/TD]
[TD="align: right"]$391.04[/TD]
[/TR]
[TR]
[TD]XXXXX[/TD]
[TD]subcat 5[/TD]
[TD="align: right"]$392.03[/TD]
[/TR]
[TR]
[TD]XXXXX[/TD]
[TD]subcat 3[/TD]
[TD="align: right"]$1,643.95[/TD]
[/TR]
[TR]
[TD]XXXXX[/TD]
[TD]subcat 8[/TD]
[TD="align: right"]$133.70[/TD]
[/TR]
[TR]
[TD]XXXXX[/TD]
[TD]subcat 1[/TD]
[TD="align: right"]$3,995.09[/TD]
[/TR]
[TR]
[TD]XXXXX[/TD]
[TD]subcat 11[/TD]
[TD="align: right"]($2,744.34)[/TD]
[/TR]
[TR]
[TD]XXXXX[/TD]
[TD]subcat 2[/TD]
[TD="align: right"]$1,895.09[/TD]
[/TR]
[TR]
[TD]XXXXX[/TD]
[TD]subcat 7[/TD]
[TD="align: right"]$332.52[/TD]
[/TR]
[TR]
[TD]XXXXX[/TD]
[TD]subcat 4[/TD]
[TD="align: right"]$956.61[/TD]
[/TR]
[TR]
[TD]XXXXX Total[/TD]
[TD][/TD]
[TD="align: right"]$7,172.41[/TD]
[/TR]
</tbody>[/TABLE]

What I'd like to see automatically is:

[TABLE="class: grid, width: 608"]
<tbody>[TR]
[TD]Product Class[/TD]
[TD]Family[/TD]
[TD="align: right"]Sales Amt[/TD]
[/TR]
[TR]
[TD]XXX[/TD]
[TD]subcat 1[/TD]
[TD="align: right"]$5,282.14[/TD]
[/TR]
[TR]
[TD]XXX[/TD]
[TD]subcat 2[/TD]
[TD="align: right"]$1,080.91[/TD]
[/TR]
[TR]
[TD]XXX[/TD]
[TD]subcat 3[/TD]
[TD="align: right"]$507.12[/TD]
[/TR]
[TR]
[TD]XXX[/TD]
[TD]subcat 4[/TD]
[TD="align: right"]$380.73[/TD]
[/TR]
[TR]
[TD]XXX[/TD]
[TD]subcat 5[/TD]
[TD="align: right"]$97.07[/TD]
[/TR]
[TR]
[TD]XXX[/TD]
[TD]subcat 6[/TD]
[TD="align: right"]$78.86[/TD]
[/TR]
[TR]
[TD]XXX[/TD]
[TD]subcat 7[/TD]
[TD="align: right"]$74.39[/TD]
[/TR]
[TR]
[TD]XXX Total[/TD]
[TD][/TD]
[TD="align: right"]$7,501.22[/TD]
[/TR]
[TR]
[TD]XXXXX[/TD]
[TD]subcat 1[/TD]
[TD="align: right"]$3,995.09[/TD]
[/TR]
[TR]
[TD]XXXXX[/TD]
[TD]subcat 2[/TD]
[TD="align: right"]$1,895.09[/TD]
[/TR]
[TR]
[TD]XXXXX[/TD]
[TD]subcat 3[/TD]
[TD="align: right"]$1,643.95[/TD]
[/TR]
[TR]
[TD]XXXXX[/TD]
[TD]subcat 4[/TD]
[TD="align: right"]$956.61[/TD]
[/TR]
[TR]
[TD]XXXXX[/TD]
[TD]subcat 5[/TD]
[TD="align: right"]$392.03[/TD]
[/TR]
[TR]
[TD]XXXXX[/TD]
[TD]subcat 6[/TD]
[TD="align: right"]$391.04[/TD]
[/TR]
[TR]
[TD]XXXXX[/TD]
[TD]subcat 7[/TD]
[TD="align: right"]$332.52[/TD]
[/TR]
[TR]
[TD]XXXXX[/TD]
[TD]subcat 8[/TD]
[TD="align: right"]$133.70[/TD]
[/TR]
[TR]
[TD]XXXXX[/TD]
[TD]subcat 9[/TD]
[TD="align: right"]$129.46[/TD]
[/TR]
[TR]
[TD]XXXXX[/TD]
[TD]subcat 10[/TD]
[TD="align: right"]$47.26[/TD]
[/TR]
[TR]
[TD]XXXXX[/TD]
[TD]subcat 11[/TD]
[TD="align: right"]($2,744.34)[/TD]
[/TR]
[TR]
[TD]XXXXX Total[/TD]
[TD][/TD]
[TD="align: right"]$7,172.41[/TD]
[/TR]
</tbody>[/TABLE]

Is there any quicker/more efficient way to do this as opposed to having to go to each individual Subtotal Group and sorting those individual groups of cells by Column C Largest to Smallest? Assistance would be greatly appreciated!!

Thank you for reading!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Welcome to MrExcel,

Have you tried using a PivotTable? The sorting and subtotaling you describe is handled easily by a PivotTable.

This could probably be done using a helper column too. That would be kind of clunky though- so I'd encourage you to try a PivotTable first.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,248
Members
452,623
Latest member
cliftonhandyman

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