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

ESS	757
LOG ON	547
LAPTOP	439
Citrix	338
SAP PRODUCTION	337
MICROSOFT OUTLOOK	269
OFFICE 365	251
PRINTING	242
NETWORK DRIVES	213
Case System	148

<tbody>
</tbody><colgroup><col><col></colgroup>

The table to search from in a worksheet called "Resolve type data" looks like the below, but with a lot more service names.

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

<tbody>
</tbody><colgroup><col><col span="13"></colgroup>

 

[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?