Rank Multiple Criteria Duplicates

[jas888]

Hello to everyone,
I have been an active reader and have only just joined today in a bid to get some assistance as quite normally the friendly advice that is readily available has been sufficient up until now (where I cannot find a solution to my following problem- apologies if it's listed somewhere already..?)


For simplicity I have put it below:



[TABLE="width: 150"]
<tbody>[TR]
[TD]Ranking
{Col A}[/TD]
[TD]Store {Col B}[/TD]
[TD]NOV% (Mth 1) -{Col C}[/TD]
[TD]DEC% (Mth 2) {Col D}[/TD]
[TD]JAN% (Mth 3) {Col E}[/TD]
[TD]Prev Mth vs Cuu Mth % {Col F}[/TD]
[TD]Mth 1 vs Mth 3 % {Col G}[/TD]
[TD][/TD]
[TD]Adjusted Ranking {Col I}[/TD]
[TD]Store {Col J}[/TD]
[TD]JAN% {Col K}[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]96[/TD]
[TD="align: center"]96[/TD]
[TD="align: center"]93.5[/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]93.5[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]88.7[/TD]
[TD="align: center"]91[/TD]
[TD="align: center"]91[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]91[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]88.3[/TD]
[TD="align: center"]90.2[/TD]
[TD="align: center"]85[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]-3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]91[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]75[/TD]
[TD="align: center"]91[/TD]
[TD="align: center"]91[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]85[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]92.2[/TD]
[TD="align: center"]90.4[/TD]
[TD="align: center"]85[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]-7[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]85[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]75.7[/TD]
[TD="align: center"]77[/TD]
[TD="align: center"]84[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]84[/TD]
[/TR]
</tbody>[/TABLE]


1. I need Col A Ranking formula based on Col E (Jan % figures)
2. Problem is there are two lots of DUPLICATE values in Col E (Store B & Store D who have reached 91% and Store C & Store E who have both reached 85%)
3. As a result I need to rely on another criteria to further rank these stores and that is based on figures in Col F (i.e. Previous Month vs Current Month difference in %)

4. As a result of the above, I then require Col J to Rank the stores "Names" in Descending order (i.e. from Largest to Smallest % as per above criteria)
5. Finally, (not sure if it's possible) I then require their respective % figures from JAN to be put alongside the Store names in Col K

6. BTW- Col I, J,K is what I'll be presenting to the stores as form of presentation slide
7. And if that lovely person would care to explain the reasoning behind the coding such that I can understand how to apply this in future.. (please)


SOMEONE, please help- as this is a big project I'm working on and to simplify this process would mean so much to me right now!

Thank you to all of you in advance who reach out to me!!!!




[TABLE="width: 585"]
[/TABLE]

 

[ValuationMan]

Unless I'm missing something, it seems sorting the data (Data -> Sort) with multiple levels is the easiest solution.

 

[jas888]

Unless I'm missing something, it seems sorting the data (Data -> Sort) with multiple levels is the easiest solution.

Hi ValuationMan,
Thank you for your reply.
I’m not sure if I made myself clear, what I meant is the things I’ve listed - I.e. what each columns are to do etc is where I need the help in getting the formulas to calculate such.
Eg. Ranking when duplicates and then needing to further rank based on another criterion etc

So basically I need the formulas for all the respective components that I don’t know at the moment..?

 

[ValuationMan]

My understanding is that your data is Columns B-G and you want to calculate Columns I-K. Is that correct?

 

[jas888]

Yes sir that his correct
Including the ranking into col a ...

 

[jas888]

@ValuationMan, any luck man?
Do you think you can assist me with the above?