Find Top 3 Applications using formula

[ajoshi76]

Hello All,

I have a table having the following columns:
1. Assignee
2. Application Name
3. Month showing number of tickets

RAW Data is as below:

Assignee Application Name Jun Jul
Resource 1 Application 1 3 4
Resource 3 Application 3 160 132
Resource 3 Application 1 8 45
Resource 2 Application 1 116 98
Resource 2 Application 3 3 0
Resource 3 Application 5 3 0
Resource 5 Application 1 8 12
Resource 5 Application 5 220 3
Resource 5 Application 3 3 0
Resource 4 Application 1 3 9
Resource 6 Application 1 26 36
Resource 6 Application 5 3 19
Resource 6 Application 3 8 7
Resource 7 Application 5 49 111
Resource 7 Application 1 22 78
Resource 5 Application 3 3 0
Resource 8 Application 1 3 0
Resource 4 Application 3 71 147
Resource 8 Application 7 3 62

The output should be in this table
Assignee Month Top 3 Apps
Resource 1 Jun ???
Resource 2 Jun ???
Resource 3 Jun ???
Resource 4 Jun ???
Resource 8 Jun ???
Resource 7 Jun ???
Narayan (one of the excel Ninjas) worked and provided me with this formula (modified with the correct headers as above:

SUBSTITUTE(TRIM(SUBSTITUTE(IFERROR(INDEX(Table2[Application],MATCH(LARGE(IF(Table2[Assignee]=A2,IF(Table2[Month]=C2,Table2[Counts])),ROW($A$1)),IF(Table2[Assignee]=A2,IF(Table2[Month]=C2,Table2[Counts])),0)),"")&","&IFERROR(INDEX(Table2[Application],MATCH(LARGE(IF(Table2[Assignee]=A2,IF(Table2[Month]=C2,Table2[Counts])),ROW($A$2)),IF(Table2[Assignee]=A2,IF(Table2[Month]=C2,Table2[Counts])),0)),"")&","&IFERROR(INDEX(Table2[Application],MATCH(LARGE(IF(Table2[Assignee]=A2,IF(Table2[Month]=C2,Table2[Counts])),ROW($A$3)),IF(Table2[Assignee]=A2,IF(Table2[Month]=C2,Table2[Counts])),0)),""),","," "))," ",",")

Narayan can you look at this post and provide a solution

Thank you very much for all your efforts

Regards
Ashish

 

[Sajan]

Hello Ashish,
This looks very similar to another post I saw on the old forum... where someone had posted some solutions also. Not sure if those replies did not carry over into the new forum.

If this is a new / different problem, can you describe the new situation?

-Sajan.

 

[NARAYANK991]

Hi Sajan ,

Based on the data posted initially , I had uploaded the following worksheet :

https://www.dropbox.com/s/ykgpgaefi1qxr2p/Example_Joshi.xlsx

The data posted now has space characters in the application names , which are being trimmed by my formula ; hence what I posted earlier needs to be revised.

Narayan