Finding top 10 values in a specific months data within a range of months

CarlBH

New Member
Joined
Mar 16, 2018
Messages
4
I run reports for ticket analysis in Excel using exports from a case management system (very limited report functionality in an old system).
I build up a set of data, each week/month adding the latest resolved/new cases within the period.
From that I create a worksheet using CountIFS to produce a table that shows me the number of cases by case type in each month over the last 2 years for trending purposes.

What I want to find a way to do is to extract the top 10 values and their associated case types (bearing in mind there will be duplicate values) in a given months data. The complexity is I want the month/week in question to be based on the month/week for "today" that is set elsewhere in the report. So that when I set the "report month" date elsewhere in the report, the output table below autopopulates bith the service and the value from the source data.
Something similar to a combination of HLOOKUP against the month or week and a LARGE function. But instead of looking for a specific Row every time within the HLOOKUP, it is looking up the LARGE Top 10 values and the Services associated with that value.

OUTPUT Required
[TABLE="width: 210"]
<tbody>[TR]
[TD="align: left"]ESS[/TD]
[TD="align: right"]757[/TD]
[/TR]
[TR]
[TD="align: left"]LOG ON[/TD]
[TD="align: right"]547[/TD]
[/TR]
[TR]
[TD="align: left"]LAPTOP[/TD]
[TD="align: right"]439[/TD]
[/TR]
[TR]
[TD="align: left"]Citrix[/TD]
[TD="align: right"]338[/TD]
[/TR]
[TR]
[TD="align: left"]SAP PRODUCTION[/TD]
[TD="align: right"]337[/TD]
[/TR]
[TR]
[TD="align: left"]MICROSOFT OUTLOOK[/TD]
[TD="align: right"]269[/TD]
[/TR]
[TR]
[TD="align: left"]OFFICE 365[/TD]
[TD="align: right"]251[/TD]
[/TR]
[TR]
[TD="align: left"]PRINTING[/TD]
[TD="align: right"]242[/TD]
[/TR]
[TR]
[TD="align: left"]NETWORK DRIVES[/TD]
[TD="align: right"]213[/TD]
[/TR]
[TR]
[TD="align: left"]Case System[/TD]
[TD="align: right"]148[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]


The table to search from in a worksheet called "Resolve type data" looks like the below, but with a lot more service names.
[TABLE="width: 1195"]
<tbody>[TR]
[TD]Service name[/TD]
[TD="align: right"]Jan-17[/TD]
[TD="align: right"]Feb-17[/TD]
[TD="align: right"]Mar-17[/TD]
[TD="align: right"]Apr-17[/TD]
[TD="align: right"]May-17[/TD]
[TD="align: right"]Jun-17[/TD]
[TD="align: right"]Jul-17[/TD]
[TD="align: right"]Aug-17[/TD]
[TD="align: right"]Sep-17[/TD]
[TD="align: right"]Oct-17[/TD]
[TD="align: right"]Nov-17[/TD]
[TD="align: right"]Dec-17[/TD]
[TD="align: right"]Jan-18[/TD]
[/TR]
[TR]
[TD]ESS[/TD]
[TD="align: right"]986[/TD]
[TD="align: right"]910[/TD]
[TD="align: right"]730[/TD]
[TD="align: right"]685[/TD]
[TD="align: right"]749[/TD]
[TD="align: right"]746[/TD]
[TD="align: right"]695[/TD]
[TD="align: right"]723[/TD]
[TD="align: right"]797[/TD]
[TD="align: right"]727[/TD]
[TD="align: right"]652[/TD]
[TD="align: right"]479[/TD]
[TD="align: right"]757[/TD]
[/TR]
[TR]
[TD]LOG ON[/TD]
[TD="align: right"]760[/TD]
[TD="align: right"]802[/TD]
[TD="align: right"]746[/TD]
[TD="align: right"]511[/TD]
[TD="align: right"]509[/TD]
[TD="align: right"]434[/TD]
[TD="align: right"]478[/TD]
[TD="align: right"]402[/TD]
[TD="align: right"]351[/TD]
[TD="align: right"]420[/TD]
[TD="align: right"]354[/TD]
[TD="align: right"]418[/TD]
[TD="align: right"]547[/TD]
[/TR]
[TR]
[TD]LAPTOP[/TD]
[TD="align: right"]259[/TD]
[TD="align: right"]310[/TD]
[TD="align: right"]336[/TD]
[TD="align: right"]301[/TD]
[TD="align: right"]378[/TD]
[TD="align: right"]427[/TD]
[TD="align: right"]434[/TD]
[TD="align: right"]369[/TD]
[TD="align: right"]425[/TD]
[TD="align: right"]442[/TD]
[TD="align: right"]468[/TD]
[TD="align: right"]305[/TD]
[TD="align: right"]439[/TD]
[/TR]
[TR]
[TD]Citrix[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]263[/TD]
[TD="align: right"]303[/TD]
[TD="align: right"]390[/TD]
[TD="align: right"]305[/TD]
[TD="align: right"]371[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]233[/TD]
[TD="align: right"]338[/TD]
[/TR]
[TR]
[TD]SAP PRODUCTION[/TD]
[TD="align: right"]350[/TD]
[TD="align: right"]292[/TD]
[TD="align: right"]302[/TD]
[TD="align: right"]237[/TD]
[TD="align: right"]318[/TD]
[TD="align: right"]280[/TD]
[TD="align: right"]279[/TD]
[TD="align: right"]274[/TD]
[TD="align: right"]288[/TD]
[TD="align: right"]273[/TD]
[TD="align: right"]365[/TD]
[TD="align: right"]214[/TD]
[TD="align: right"]337[/TD]
[/TR]
[TR]
[TD]MICROSOFT OUTLOOK[/TD]
[TD="align: right"]505[/TD]
[TD="align: right"]452[/TD]
[TD="align: right"]453[/TD]
[TD="align: right"]466[/TD]
[TD="align: right"]924[/TD]
[TD="align: right"]485[/TD]
[TD="align: right"]535[/TD]
[TD="align: right"]568[/TD]
[TD="align: right"]475[/TD]
[TD="align: right"]453[/TD]
[TD="align: right"]461[/TD]
[TD="align: right"]215[/TD]
[TD="align: right"]269[/TD]
[/TR]
[TR]
[TD]OFFICE 365[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]108[/TD]
[TD="align: right"]148[/TD]
[TD="align: right"]124[/TD]
[TD="align: right"]131[/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]225[/TD]
[TD="align: right"]347[/TD]
[TD="align: right"]290[/TD]
[TD="align: right"]165[/TD]
[TD="align: right"]251[/TD]
[/TR]
[TR]
[TD]PRINTING[/TD]
[TD="align: right"]260[/TD]
[TD="align: right"]242[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]186[/TD]
[TD="align: right"]233[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]209[/TD]
[TD="align: right"]242[/TD]
[TD="align: right"]286[/TD]
[TD="align: right"]220[/TD]
[TD="align: right"]221[/TD]
[TD="align: right"]221[/TD]
[TD="align: right"]242[/TD]
[/TR]
[TR]
[TD]NETWORK DRIVES[/TD]
[TD="align: right"]151[/TD]
[TD="align: right"]144[/TD]
[TD="align: right"]174[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]179[/TD]
[TD="align: right"]129[/TD]
[TD="align: right"]152[/TD]
[TD="align: right"]193[/TD]
[TD="align: right"]161[/TD]
[TD="align: right"]206[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]213[/TD]
[/TR]
[TR]
[TD]Case System[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]189[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]145[/TD]
[TD="align: right"]151[/TD]
[TD="align: right"]144[/TD]
[TD="align: right"]148[/TD]
[/TR]
[TR]
[TD]OFFICE 365 PRO PLUS[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]574[/TD]
[TD="align: right"]282[/TD]
[TD="align: right"]247[/TD]
[TD="align: right"]510[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]71[/TD]
[/TR]
[TR]
[TD]INTERNET[/TD]
[TD="align: right"]196[/TD]
[TD="align: right"]235[/TD]
[TD="align: right"]216[/TD]
[TD="align: right"]170[/TD]
[TD="align: right"]196[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]114[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD]VIRUS[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]136[/TD]
[TD="align: right"]131[/TD]
[TD="align: right"]163[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD]PC[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]63[/TD]
[/TR]
[TR]
[TD]WIRELESS[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]109[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]63[/TD]
[/TR]
[TR]
[TD]JUNOS PULSE[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]61[/TD]
[/TR]
[TR]
[TD]TELEPHONY (LANDLINE/DESKPHONE)[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]47[/TD]
[/TR]
</tbody><colgroup><col><col span="13"></colgroup>[/TABLE]
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Of course if you already have the underlying data with a service name column and date column then you can just pivot/filter/group by month/subtotal without unpivoting anything.
 
Upvote 0
Unfortunately although it initially gives me what I need, when I add more data to the source for the next month for example it doesn't refresh through.
The first consolidated table updates when I click on refresh all to show the new total value. However this doesn't carry through to the 2nd pivot created when I double clicked on the data originally.

e.g. If I add the ticket data for Feb and March to date, the consolidated table count goes up to reflect the new total ticket count, but this is not reflected in the next two tables created when I followed the youtube guide.
I am looking to create something that I can load the new data each month, select a pivottable refresh all once and the whole workbook updates automatically for the new months data.
 
Upvote 0
The issue is I use the same underlying data for multiple information representations, month by month trend, distribution overall by service, and the current month top 10.
 
Upvote 0
Can you start with just the service name and date? Or does your system only give you the 2D table as you've shown above?
 
Upvote 0

Forum statistics

Threads
1,224,753
Messages
6,180,748
Members
452,996
Latest member
nelsonsix66

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