Rank based on two columns one is text & one is numeric

[Shayeebur]

Hi All,

I am facing difficulty in finding out Rank based on their Skill i.e. text against Points.

I have name of the employees in Column C & their skill in Column D where they take calls and their achieved points column E. I need to get two Rank one is all Rank which is column F which I already put the formula for the same and the second is Skillset Rank in Column G which I am unable to get the answer.

I am trying to find out who is top 3 ranks if skill is Live or Training & who is top 3 in Internet skill & who is top 3 rank in Super Calls skill & last one is top 3 rank Escalation Calls. Rest all will get “Rank 0” as their Skillset Rank. I have mentioned the output in sheet 2 however I want to get this from Formula.

Please find attached excel for the same.

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[Only4Excel]

In G3, then copy down.

=COUNTIFS(D$3:D$54,D3,E$3:E$54,">"&E3)+1

Format cell >> Custom

[<=3]0;"Rank 0"

 

[NARAYANK991]

Hi ,

Another possibility :

=TEXT(COUNTIFS(D$3:D$54,D3,E$3:E$54,">"&E3)+1,"[<=3]0;" & CHAR(34) & "Rank 0" & CHAR(34))

Narayan

 

[Shayeebur]

Thanks a lot Narayank. You are Genius.
Thanks once again.

 

[Shayeebur]

Sorry Narayan i am trying to find out who is top 6 ranks if skill is Live and Training i mean including live & training 1 to
6 ranks and who is top 3 in Internet skill & who is top 3 rank in Super Calls skill & last one is top 3 rank Escalation Calls. Rest all will get “Rank 0” as their Skillset Rank.

 

[bosco_yip]

... find out who is top 6 in Live and Training and who is top 3 in Internet skill , Super Calls skill & Escalation Calls. Rest all will get “Rank 0”.....

Try,

In G3, formula copy down :

=TEXT(COUNTIFS(D$3:D$54,D3,E$3:E$54,">"&E3)+1,"[<"&IF(OR(LEFT(D3)={"L","T"}),7,4)&"]0;Ra\nk \0")

In J4, formula copy across and down :

=IF(ROWS($1:1)<=J$2,INDEX($C$3:$C$54,MATCH(1,INDEX(($D$3:$D$54=J$3)*($G$3:$G$54=$I4&""),0),0)),"")

Regards
Bosco

 

[NARAYANK991]

Hi ,

This is the revised file.

Narayan

 

[Shayeebur]

I think I am unable explain the question properly. Please find the attached exact file which I am finding out the skillset rank.
Including Live & SDA only 6 employee can get upto 6 rank not like in live 6 people & SDA 6 people, it should be including Live & SDA only 1 to 6 rank will get. & rest will get like in IHD top 3 rank & SS top 3 rank & UKED top 3 rank. Please find attached actual sheet & my answers in sheet 2.

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[Shayeebur]

Hi Narayan

Its working fine thanks a lot. You are truely genious.