Three level ranking

[Raju Kumar Singh]

Hey guys..

Please help me to write a VBa code for the below scenario.

I have five columns. First one have names. 3rd, 4th, 4th have criteria. First, give ranking as per 3rd columns, if any output rank have same ranking then it will go to 4th column criteria. If after applying ranking again same ranks are there then will check with 5th column criteria. Final ranking will be kept in 2nd column.

[TABLE="width: 323"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD] Rank[/TD]
[TD]criteria 1[/TD]
[TD]Criteria 2[/TD]
[TD]Criteria 3[/TD]
[/TR]
[TR]
[TD]ARR[/TD]
[TD] [/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]NET[/TD]
[TD] [/TD]
[TD]8[/TD]
[TD]1[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]JUNb[/TD]
[TD] [/TD]
[TD]45[/TD]
[TD]25[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]PRO[/TD]
[TD] [/TD]
[TD]23[/TD]
[TD]12[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD]NEC[/TD]
[TD] [/TD]
[TD]12[/TD]
[TD]2[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]JUN[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]RKSI[/TD]
[TD] [/TD]
[TD]15[/TD]
[TD]32[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]JUNk[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]25[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]JUNe[/TD]
[TD] [/TD]
[TD]3[/TD]
[TD]12[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]TS [/TD]
[TD] [/TD]
[TD]33[/TD]
[TD]11[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]ING[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]DIM[/TD]
[TD] [/TD]
[TD]4[/TD]
[TD]15[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]JUNt[/TD]
[TD] [/TD]
[TD]16[/TD]
[TD]16[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]AVN[/TD]
[TD] [/TD]
[TD]6[/TD]
[TD]36[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]MAR[/TD]
[TD] [/TD]
[TD]29[/TD]
[TD]15[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]NIS[/TD]
[TD] [/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]JUNx[/TD]
[TD] [/TD]
[TD]22[/TD]
[TD]11[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]PT.[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]15[/TD]
[/TR]
</tbody>[/TABLE]

Thanks...

 

[Raju Kumar Singh]

Hi Eric,

Thank you very much for the nice explanation of logic behind the formula...I understood now!!

Hi Peter,

Glad to see that there are multiple ways to do the same task of ranking(my case here)...It was great learning!! Thanks a lot.


Regards,
Raju Kumar Singh

 

[Peter_SSs]

Hi Peter,

Glad to see that there are multiple ways to do the same task of ranking(my case here)...It was great learning!! Thanks a lot.

Glad to contribute - but Eric did all the 'hard lifting' here.

 

[Eric W]

Peter: Oh, great, now I have to read up on SortedLists! Based on what I've seen, it would have been a better choice for my second macro. You also raise a good point. While crafting the formulas / macros, I made a conscious (but unmentioned) decision not to worry about ties, because I thought it very unlikely. But in the off chance it does, your macro covers that.

Raju, glad we could help. Since I learned something new here too, it works out for everyone.

 

[Peter_SSs]

Peter: Oh, great, now I have to read up on SortedLists!