Rank With Multiple Criteria

[Lewzerrrr]

Hi,

This has been asked a lot but I can't adapt solutions to my needs.

I need to rank by brand the top & bottom variance but only if the sales & plan <> 0.

Sample data below with expected results

[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Brand[/TD]
[TD="class: xl63, width: 64"]Sales[/TD]
[TD="class: xl63, width: 64"]Plan[/TD]
[TD="class: xl63, width: 64"]Var[/TD]
[TD="width: 64"]Top Rank[/TD]
[TD="width: 64"]Bottom Rank[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]7,374[/TD]
[TD="class: xl64, align: right"]6,287[/TD]
[TD="class: xl64, align: right"]1,087[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]12[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]15,849[/TD]
[TD="class: xl64, align: right"]16,798[/TD]
[TD="class: xl64, align: right"]-949[/TD]
[TD="class: xl63"]8[/TD]
[TD="class: xl63"]6[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="class: xl64, align: right"]3,085[/TD]
[TD="class: xl64, align: right"]3,549[/TD]
[TD="class: xl64, align: right"]-464[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]6[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="class: xl64, align: right"]9,498[/TD]
[TD="class: xl64, align: right"]11,716[/TD]
[TD="class: xl64, align: right"]-2,218[/TD]
[TD="class: xl63"]7[/TD]
[TD="class: xl63"]2[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]3,666[/TD]
[TD="class: xl64, align: right"]4,165[/TD]
[TD="class: xl64, align: right"]-499[/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]10[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]5,026[/TD]
[TD="class: xl64, align: right"]6,091[/TD]
[TD="class: xl64, align: right"]-1,065[/TD]
[TD="class: xl63"]9[/TD]
[TD="class: xl63"]5[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]12,042[/TD]
[TD="class: xl64, align: right"]12,987[/TD]
[TD="class: xl64, align: right"]-945[/TD]
[TD="class: xl63"]7[/TD]
[TD="class: xl63"]7[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]4,059[/TD]
[TD="class: xl64, align: right"]5,677[/TD]
[TD="class: xl64, align: right"]-1,618[/TD]
[TD="class: xl63"]11[/TD]
[TD="class: xl63"]3[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="class: xl64, align: right"]2,294[/TD]
[TD="class: xl64, align: right"]4,121[/TD]
[TD="class: xl64, align: right"]-1,827[/TD]
[TD="class: xl63"]6[/TD]
[TD="class: xl63"]3[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]14,603[/TD]
[TD="class: xl64, align: right"]15,349[/TD]
[TD="class: xl64, align: right"]-746[/TD]
[TD="class: xl63"]6[/TD]
[TD="class: xl63"]8[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl63"]-[/TD]
[TD="class: xl63"]-[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="class: xl64, align: right"]842[/TD]
[TD="class: xl64, align: right"]1,219[/TD]
[TD="class: xl64, align: right"]-377[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]8[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl63"]-[/TD]
[TD="class: xl63"]-[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]11,814[/TD]
[TD="class: xl64, align: right"]13,996[/TD]
[TD="class: xl64, align: right"]-2,182[/TD]
[TD="class: xl63"]12[/TD]
[TD="class: xl63"]2[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]6,930[/TD]
[TD="class: xl64, align: right"]7,996[/TD]
[TD="class: xl64, align: right"]-1,066[/TD]
[TD="class: xl63"]10[/TD]
[TD="class: xl63"]4[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl63"]-[/TD]
[TD="class: xl63"]-[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="class: xl64, align: right"]3,374[/TD]
[TD="class: xl64, align: right"]5,832[/TD]
[TD="class: xl64, align: right"]-2,458[/TD]
[TD="class: xl63"]8[/TD]
[TD="class: xl63"]1[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]13,091[/TD]
[TD="class: xl64, align: right"]12,184[/TD]
[TD="class: xl64, align: right"]907[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]11[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]41,375[/TD]
[TD="class: xl64, align: right"]40,040[/TD]
[TD="class: xl64, align: right"]1,335[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]13[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="class: xl64, align: right"]5,802[/TD]
[TD="class: xl64, align: right"]6,408[/TD]
[TD="class: xl64, align: right"]-606[/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]5[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="class: xl64, align: right"]4,325[/TD]
[TD="class: xl64, align: right"]4,702[/TD]
[TD="class: xl64, align: right"]-377[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]7[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="class: xl64, align: right"]2,082[/TD]
[TD="class: xl64, align: right"]2,830[/TD]
[TD="class: xl64, align: right"]-748[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl63"]4[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]21,470[/TD]
[TD="class: xl64, align: right"]22,101[/TD]
[TD="class: xl64, align: right"]-631[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl63"]9[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]7,115[/TD]
[TD="class: xl64, align: right"]12,438[/TD]
[TD="class: xl64, align: right"]-5,323[/TD]
[TD="class: xl63"]13[/TD]
[TD="class: xl63"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[lrobbo314]

Turn your table into a pivot table. Then, in the 'Rows' section, add Brand and Var. Then add var to the 'Values' section. Click on 'Field Value Settings' and go to 'Show Values As' and select 'Rank Largest to Smallest'. This is your 'Top Rank'. Add Var to 'Values' again, only this time select 'Rank Smallest to Largest', which is your bottom rank. Then, on your pivot table under 'Row Filters', in the 'Select Field' dropdown, select 'Var' and filter out your zero values.

 

[Lewzerrrr]

Turn your table into a pivot table. Then, in the 'Rows' section, add Brand and Var. Then add var to the 'Values' section. Click on 'Field Value Settings' and go to 'Show Values As' and select 'Rank Largest to Smallest'. This is your 'Top Rank'. Add Var to 'Values' again, only this time select 'Rank Smallest to Largest', which is your bottom rank. Then, on your pivot table under 'Row Filters', in the 'Select Field' dropdown, select 'Var' and filter out your zero values.

Good solution however I need a formula as I can't change the table format unfortunately

 

[lrobbo314]

Try this.

Top Rank formula: =IF(D2=0,"",SUMPRODUCT((($A$2:$A$25=A2)*($D$2:$D$25<>0))*(D2<$D$2:$D$25))+1)
Bottom Rank formula: =IF(D2=0,"",SUMPRODUCT((($A$2:$A$25=A2)*($D$2:$D$25<>0))*(D2>$D$2:$D$25))+1)

 

[Lewzerrrr]

Try this.

Top Rank formula: =IF(D2=0,"",SUMPRODUCT((($A$2:$A$25=A2)*($D$2:$D$25<>0))*(D2<$D$2:$D$25))+1)
Bottom Rank formula: =IF(D2=0,"",SUMPRODUCT((($A$2:$A$25=A2)*($D$2:$D$25<>0))*(D2>$D$2:$D$25))+1)

This would blank out if sales & plan was populated but matched together.

I've created this code to get the top ranks however stumbling on bottom as I'd need to find the bottom number to work back to 1.

Sub RankTop()    
    MyLR = Cells(Rows.Count, 2).End(xlUp).Row
    
    Range("B2:G" & MyLR).Sort key1:=[G2], Order1:=xlDescending, Header:=xlNo '' sort variance vs plan to get highest
    Range("B2:G" & MyLR).Sort key1:=[C2], Order1:=xlDescending, Header:=xlNo '' sort brands to get TS to TM
       
    I = 1
    
        For J = 2 To MyLR
        
        If Cells(J, 3) <> Cells(J - 1, 3) Then I = 1 '' Re-rank from 1 when it finds TM
        
            If Cells(J, 5) = 0 And Cells(J, 6) = 0 Then GoTo NextIteration '' Skips over where sales and plan are both zero
            
                Cells(J, 8) = I
            
            I = I + 1
            
NextIteration:


        Next J
        
End Sub

 

[lrobbo314]

This would blank out if sales & plan was populated but matched together.

I'm not sure what you mean by this. But, looking at your code, it seems like the data isn't in the same place. My formulas made the assumption that your data started in A1, so maybe changing the references would work. I pasted the data you had in the original post in A1 and the formulas produced the same results that you posted as your desired results.

 

[Lewzerrrr]

I'm not sure what you mean by this. But, looking at your code, it seems like the data isn't in the same place. My formulas made the assumption that your data started in A1, so maybe changing the references would work. I pasted the data you had in the original post in A1 and the formulas produced the same results that you posted as your desired results.

So let's say there was a sales of 1,000 and also a plan of 1,000 giving a variance of 0. I wouldn't want this to be blanked out which it would by your formula. I only want to blank if sales and plan are both zero.

 

[lrobbo314]

I see. Try these formulas.

Top Rank: =IF(AND(B2=0,C2=0),"",SUMPRODUCT((($A$2:$A$25=A2)*(($B$2:$B$25<>0)*($C$2:$C$25<>0)))*((B2-C2)<($B$2:$B$25-$C$2:$C$25)))+1)
Bottom Rank: =IF(AND(B2=0,C2=0),"",SUMPRODUCT((($A$2:$A$25=A2)*(($B$2:$B$25<>0)*($C$2:$C$25<>0)))*((B2-C2)>($B$2:$B$25-$C$2:$C$25)))+1)

 

[Lewzerrrr]

I see. Try these formulas.

Top Rank: =IF(AND(B2=0,C2=0),"",SUMPRODUCT((($A$2:$A$25=A2)*(($B$2:$B$25<>0)*($C$2:$C$25<>0)))*((B2-C2)<($B$2:$B$25-$C$2:$C$25)))+1)
Bottom Rank: =IF(AND(B2=0,C2=0),"",SUMPRODUCT((($A$2:$A$25=A2)*(($B$2:$B$25<>0)*($C$2:$C$25<>0)))*((B2-C2)>($B$2:$B$25-$C$2:$C$25)))+1)

Amazing, thank you! Last thing if you can, I'd need it to rank unique instead of duplicating

For example if there's 2 zero variance then go 1, 2 as this formula goes 1, 1

 

[lrobbo314]

Let me know if these work.

Top: =IF(AND(B2=0,C2=0),"",(SUMPRODUCT((($A$2:$A$25=A2)*(($B$2:$B$25<>0)*($C$2:$C$25<>0)))*((B2-C2)<($B$2:$B$25-$C$2:$C$25)))+1)+COUNTIFS($A$2:A2,A2,$D$2:D2,D2)-1)

Bottom: =IF(AND(B2=0,C2=0),"",(SUMPRODUCT((($A$2:$A$25=A2)*(($B$2:$B$25<>0)*($C$2:$C$25<>0)))*((B2-C2)>($B$2:$B$25-$C$2:$C$25)))+1)+COUNTIFS($A$2:A2,A2,$D$2:D2,D2)-1)