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
<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.
<tbody>
</tbody><colgroup><col><col span="13"></colgroup>
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>