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

[CarlBH]

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]

 

[sheetspread]

You should unpivot the data with BI Tools or this method in standalone Excel:

https://www.youtube.com/watch?v=xmqTN0X-AgY

Then set a pivot table where you can easily filter by Top N

 

[CarlBH]

Thank you. Just trying that now...

 

[sheetspread]

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.

 

[CarlBH]

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.

 

[CarlBH]

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.

 

[sheetspread]

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?