Top 3 of Top 3 with sum of remaining categories

Amas73

New Member
Joined
Nov 21, 2011
Messages
1
Hello,

I tried to search for this but had no luck. Could be just an issue with knowing what to search for. If so I apologise, can you point me to the correct place.

I am trying to produce a (dynamic) list of the Top 5 Categories with their Top 5 Sub-categories and the remainder Categories/Sub-categories are summated. Data is presentated in 3 colums; Category, Sub-Category & Total. So the amount that determines the order is at the Sub-Category level & will need to be summed by Category to determing the order. This is where I came undone. I could work a way to just get the Top of a single dimension list & sum the remainder. But the 2nd dimension throwes me. :(

As an example for a Top 3 of Top 3 set. If the data was this:

[TABLE="width: 463"]
<tbody>[TR]
[TD]Category[/TD]
[TD]Sub-Category[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]Field Activity[/TD]
[TD]Service Order/Work Order[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Field Activity[/TD]
[TD]Access/Gates[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]Field Activity[/TD]
[TD]Staff Conduct[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]Field Activity[/TD]
[TD]Damage to Property[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD]Field Activity[/TD]
[TD]Incomplete Work[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Infrastructure[/TD]
[TD]Other[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Infrastructure[/TD]
[TD]Poles[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Infrastructure[/TD]
[TD]Substation[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Infrastructure[/TD]
[TD]Advertising/Painting-EE Asset[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Infrastructure[/TD]
[TD]Line Clearance[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Infrastructure[/TD]
[TD]Turret/Pillar Box[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Quality of Supply[/TD]
[TD]Outage Notification[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD]Quality of Supply[/TD]
[TD]Not Notified of Results[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD]Quality of Supply[/TD]
[TD]Outage Duration[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]NMI Reclassification Distribution[/TD]
[TD]Information Notice[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Meter Reading[/TD]
[TD]Estimates[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]Meter Reading[/TD]
[TD]Access[/TD]
[TD="align: right"]47[/TD]
[/TR]
[TR]
[TD]Meter Reading[/TD]
[TD]Staff Conduct[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Meter Reading[/TD]
[TD]Gates[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]Meter Reading[/TD]
[TD]Qld Locksmiths[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Meter Reading[/TD]
[TD]Date/Time[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Advertising/Marketing[/TD]
[TD]General[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]


Then the result would be like this:

[TABLE="width: 492"]
<tbody>[TR]
[TD]Category[/TD]
[TD]Sub-Category[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]Meter Reading[/TD]
[TD][/TD]
[TD="align: right"]112[/TD]
[/TR]
[TR]
[TD]Meter Reading[/TD]
[TD]Estimates[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]Meter Reading[/TD]
[TD]Access[/TD]
[TD="align: right"]47[/TD]
[/TR]
[TR]
[TD]Meter Reading[/TD]
[TD]Gates[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]Meter Reading[/TD]
[TD]All Other Sub-categories[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]Quality of Supply[/TD]
[TD][/TD]
[TD="align: right"]71[/TD]
[/TR]
[TR]
[TD]Quality of Supply[/TD]
[TD]Outage Notification[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD]Quality of Supply[/TD]
[TD]Not Notified of Results[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD]Quality of Supply[/TD]
[TD]Outage Duration[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Field Activity[/TD]
[TD][/TD]
[TD="align: right"]69[/TD]
[/TR]
[TR]
[TD]Field Activity[/TD]
[TD]Damage to Property[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD]Field Activity[/TD]
[TD]Staff Conduct[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]Field Activity[/TD]
[TD]Incomplete Work[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Field Activity[/TD]
[TD]All Other Sub-categories[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]All Other Categories[/TD]
[TD][/TD]
[TD="align: right"]41[/TD]
[/TR]
[TR]
[TD]All Other Categories[/TD]
[TD]Other[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]All Other Categories[/TD]
[TD]Poles[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]All Other Categories[/TD]
[TD]Substation[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]All Other Categories[/TD]
[TD]All Other Sub-categories[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]TOTAL[/TD]
[TD][/TD]
[TD="align: right"]586[/TD]
[/TR]
</tbody><colgroup><col span="2"><col></colgroup>[/TABLE]

Cheers & thanks for any assistance.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Forum statistics

Threads
1,223,902
Messages
6,175,278
Members
452,629
Latest member
SahilPolekar

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