• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find Top 3 Applications using formula

ajoshi76

ajoshi76

Member
Hello All,


I have a table having the following columns:


1. Resource Name

2. Month

3. Application Name

4. Ticket Id


Can anyone tell me how to find out the Top 3 Application Name for a particular resource for a particular month?
 
Hi Joshi ,


You can try the following construct :


=INDEX(Application_Names,SMALL(IF(Resource_Names=Particular_Resource,IF(Months=Particular_Month,ROW(Application_Names)-MIN(ROW(Application_Names))+1)),ROW(A1)))


entered as an array formula , using CTRL SHIFT ENTER.


Copying it down , will change the ROW(A1) to ROW(A2) , ROW(A3) ,... which will give the second and third top application names.


Narayan
 
Thanks Narayan. i shall try this formula


But my table for reporting is as below:


Resource Name, Month, Top 3 application names


so the top 3 application names should comes in a single row.
 
Narayan -> the formula doesnt give the top application names.


eg:


If Resource A MOnth B has 3 applications: App1 has 5 tickets, App2 has 10 tickets and App3 has 1 ticket. The cell should say: App2, App1, App3


This is the end result i am looking for.
 
I fixed my formula to give the 1st top application having maximum tickets for a resource for a month


=INDEX('SN Dump'!$F:$F,LARGE(IF('SN Dump'!$G:$G=Table1[[#This Row],[Original Name]],IF('SN Dump'!$M:$M=Table1[[#This Row],[Month]],ROW('SN Dump'!$F:$F)-MIN(ROW('SN Dump'!$F:$F))+1)),ROW(A1)))


But i need all 3 applications in the same cell for a particular resource for a particular month.
 
Thanks a lot Narayan. It indeed did the job for me


The modified formula of the above thread is as below:


=CONCATENATE(INDEX('SN Dump'!$F:$F,LARGE(IF('SN Dump'!$G:$G=Table1[[#This Row],[Original Name]],IF('SN Dump'!$M:$M=Table1[[#This Row],[Month]],ROW('SN Dump'!$F:$F)-MIN(ROW('SN Dump'!$F:$F))+1)),1))," , ",INDEX('SN Dump'!$F:$F,LARGE(IF('SN Dump'!$G:$G=Table1[[#This Row],[Original Name]],IF('SN Dump'!$M:$M=Table1[[#This Row],[Month]],ROW('SN Dump'!$F:$F)-MIN(ROW('SN Dump'!$F:$F))+1)),2))," , ",INDEX('SN Dump'!$F:$F,LARGE(IF('SN Dump'!$G:$G=Table1[[#This Row],[Original Name]],IF('SN Dump'!$M:$M=Table1[[#This Row],[Month]],ROW('SN Dump'!$F:$F)-MIN(ROW('SN Dump'!$F:$F))+1)),3)))


New Situation: If there are only 3 tickets for a particular resource and all 3 belong to different application, i get #NUM as the result.


Need help.
 
This formula gives the top application:


=INDEX('SN Dump'!$F:$F,LARGE(IF('SN Dump'!$G:$G=Table1[[#This Row],[Original Name]],IF('SN Dump'!$M:$M=Table1[[#This Row],[Month]],ROW('SN Dump'!$F:$F)-MIN(ROW('SN Dump'!$F:$F))+1)),ROW(A1)))


Can someone review and fix if required.

Also, edit this formula to get top 3 APPLICATIONS?
 
the result of the above formula is wrong even for top application. Can someone immediately help. i am unable to progress any further
 
Hi Joshi ,


Sorry ; I made a mistake in my original posted formula ; I now understand that what you want is the top 3 application names , by way of the number of tickets against each application name. It will make it easier if you can upload your file.


Narayan
 
Resource Name Month Application Ticket Id

Ashish Joshi Jun App1 1234

Ashish Joshi Jun App2 1235

Ashish Joshi Jun App1 1236

Rahul Jun App4 2134

Rahul Jun App1 1237

Rahul Jul App3 2348

Ashish Joshi Jun App1 1238

Ashish Joshi Jun App2 1239

Ashish Joshi Jun App1 1231

Rahul Jun App4 2138

Rahul Jun App1 1232

Rahul Jul App3 2349
 
The output should be in this table


Reosurce Name Month Top 3 Apps

Ashish Joshi Jun ???

Ashish Joshi Jul ???

Rahul Jun ???

Rahul Jul ???
 
Hi Joshi ,


Can you download the file from here and check ?


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


I have used one helper column.


Narayan
 
You must log in or register to reply here.
Back
Top