Ranking using multiple criterion

Hi All,

I have a sales data which has a team manager , team managers will have no.of sales rep. the reps will sell different products ma,hv,hg, others (Revenue)

conditions:

product ma is given more importance, hence those team managers who have reps selling more Ma should be ranked1st but the same team manager can also have reps selling hv,hg &others in that case those reps should also come as they report to the same manager but ranked below the reps who sell more me.

hence all the team managers who have reps selling ME is ranked & your left with team manager who have the remaining 3 products alone in that case ranks is 2nd hv 3rd hg & last others

there will be team mangers who do not have reps containing hv nor hv nor me but only other then others should be so ranked highest


ranking: 1st because he has 4 me contributing to the revenue

team mangerA , rep1 ,rank1 me,
team mangerA , rep2 ,rank1 me,
team mangerA , rep2 ,rank1 me,
team mangerA , rep2 ,rank1 me,
team mangerA , rep3 ,rank2 hv,
team mangerA , rep1 ,rank4 others

Followed by 2 nd because he has 2 me contributing to the revenue

team mangerb , rep1 ,rank1 me,
team mangerb , rep2 ,rank1 me,
team mangerb , rep3 ,rank2 hv,
team mangerb , rep1 ,rank4 others

Followed by 3 nd because he has 2 hv contributing to the revenue


team mangerc , rep1 ,rank1 hv,
team mangerc , rep2 ,rank1 hv,
team mangerc , rep3 ,rank2 others

Followed by 4th because he has 1 hv contributing to the revenue


team manger D , rep1 ,rank1 hv,
team manger D, rep2 ,rank2 others

Followed by 5th because he has 3other contributing to the revenue


team manger d , rep1 ,rank1 others,
team manger d , rep2 ,rank2 others,
team manger d , rep3 ,rank3 others


Attached is sample data kindly help me with this multiple ranking

Regards
Gamefreak
Followed by 6th because he has 2other contributing to the revenue


team manger e , rep1 ,rank1 others,
team manger e , rep2 ,rank2 others,

Hi Newdoverman,

Thank you much for that wonderful formula, I would certainly get back to you incase there any further help as I have to check the same with another data in my office, fingers crossed.

Regards
Gamefreak

Hi Newdoverman,

your output is based on the total revenue of the team but my requisite is different its not based on total revenue but on team leader who's reps sell more MA product ,but the others reps who are reporting to the same team manager should also be with in the same team leader as they are one team.

2) when the ME products are exhausted them team manages who has other product like HG should be ranked,but the others reps who are reporting to the same team manager should also be with in the same team leader as they are one team along with their products which they have sold it can be hv, hg & others.

3) when the HV products are exhausted them team manages who has other product like HG should be ranked, but the others reps who are reporting to the same team manager should also be with in the same team leader as they are one team along with their products which they have sold it can be others.

3) when the HG products are exhausted them team manages who has other product like others should be ranked.

hence the logical sequence is :
1 ME 2)HG 3HV 4) others. Products is this sales data is based on revenue ( column G in this spresdsheet)


Kindly help me,

Regards
Gamefreak

Hi All,

I have a sales data which have no. of team mangers ,no of reps reporting to them & the a no of products these reps would be selling. Among the products they sell MA product is important hence the team manager who has a rep who generated more revenue by selling MA product should be ranked first even though the total sales is less. The team mangers whole team along with the other reps should be pasted as such even though the other members would have sold other product but the product sequence is as follows 1 ME 2)HG 3HV 4) others.
There may be conditions in which the team managers would not have reps who do not sell ME then the they should appear only after the all the team manger who have ME products.
Hence ,
Teamleader with reps with most me first ,(along with rest of the reps who sell other products ranked in this manner 1 ME 2)HG 3HV 4) others.)
Then Tem leaders with 2)HG first ,(along with rest of the reps who sell other products ranked in this manner 3HV 4) others.)
Then Tem leaders with 3)HV first ,(along with rest of the reps who sell other products ranked in this manner 4) others.)
Finally
Then Tem leaders with others ranked by higher revenue ,(along with rest of the reps who sell other products ranked in this manner 3HV 4) others.)
1 ME 2)HG 3HV 4) others. Products is this sales data is based on revenue ( column G in this spreadsheet)

Regards
Gamefreak

Hi ,

Any one with the solution kindly upload your Spread sheet pls.

Regards
Gamefreak

Hi Newdoverman,

Currently the calculation is based on count. of ME/hv/hg/ others but the ME is based on the largest value of sales which is in the coloum D

Thank you so Much ..... its almost correct but I need ME is directly proportional to the sales column T in your spead sheet so is there away to rank them based on the revenue contribution made by Me,Hv,HG & others rather than by the counts of no of ME???

Thank & Regards

Gamefreak

Hi Newdoverman,

Thank you so much this is the required output as its driven by sales value in the column. I need to learn much , was amazed by your excel skills.

Regards
Gamefreak

Hi newdoverman,

I am really sorry to bother you again but I replicated the same Methodology to another file the raking is Haphazard. the ranking should be based on the coloum W in the spreadsheet attached. Is it possible for you to help me with this ?

Regards
Gamefreak

Hi newdoverman,

Thank you for you reply , I have put a pivot so that it shows me a subtotal of the ME which helps me identify the Largest ME value , whe i compare them again the ranking with pivot the Rank one becomes rank 2, could you have a look ,is it bcos your calulation take in to account No. of Clients * no of counts , If you calculation can take in to account Column W where you can get the % actual sale value of ME it would work fine. Can you pls help me with this Query ?

Regards
GameFreak

Hi Newdoverman,

Thank you so much for your help! It really did work this time!

Regards
Gamefreak