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]

For extra credit, here is a Power Query version to get the same results.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Brand", type text}, {"Sales", Int64.Type}, {"Plan", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Brand", "Sales", "Plan"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Index", "Order"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Var", each [Sales]-[Plan]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each [Sales]=0 and [Plan] =0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom] = false)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Brand", Order.Ascending}, {"Var", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Brand"}, {{"Count", each _, type table}, {"Rows", each Table.RowCount(_), type number}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Bottom",1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom2", "Custom", {"Order", "Sales", "Plan", "Var", "Bottom"}, {"Order", "Sales", "Plan", "Var", "Bottom"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Expanded Custom",{"Order", "Brand", "Count", "Rows", "Sales", "Plan", "Var", "Bottom"}),
    #"Added Custom3" = Table.AddColumn(#"Reordered Columns1", "Top", each [Rows]-[Bottom]+1),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom3",{"Count", "Rows"}),
    #"Sorted Rows1" = Table.Sort(#"Removed Columns1",{{"Order", Order.Ascending}}),
    #"Reordered Columns2" = Table.ReorderColumns(#"Sorted Rows1",{"Order", "Brand", "Sales", "Plan", "Var", "Top", "Bottom"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns2",{"Order"})
in
    #"Removed Columns2"

[TABLE="width: 323"]
<tbody>[TR]
[TD]Brand[/TD]
[TD]Sales[/TD]
[TD]Plan[/TD]
[TD]Var[/TD]
[TD]Top[/TD]
[TD]Bottom[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="align: right"]7374[/TD]
[TD="align: right"]6287[/TD]
[TD="align: right"]1087[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="align: right"]15849[/TD]
[TD="align: right"]16798[/TD]
[TD="align: right"]-949[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="align: right"]3085[/TD]
[TD="align: right"]3549[/TD]
[TD="align: right"]-464[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="align: right"]9498[/TD]
[TD="align: right"]11716[/TD]
[TD="align: right"]-2218[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="align: right"]3666[/TD]
[TD="align: right"]4165[/TD]
[TD="align: right"]-499[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="align: right"]5026[/TD]
[TD="align: right"]6091[/TD]
[TD="align: right"]-1065[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="align: right"]12042[/TD]
[TD="align: right"]12987[/TD]
[TD="align: right"]-945[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="align: right"]4059[/TD]
[TD="align: right"]5677[/TD]
[TD="align: right"]-1618[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="align: right"]2294[/TD]
[TD="align: right"]4121[/TD]
[TD="align: right"]-1827[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="align: right"]14603[/TD]
[TD="align: right"]15349[/TD]
[TD="align: right"]-746[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="align: right"]842[/TD]
[TD="align: right"]1219[/TD]
[TD="align: right"]-377[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="align: right"]11814[/TD]
[TD="align: right"]13996[/TD]
[TD="align: right"]-2182[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="align: right"]6930[/TD]
[TD="align: right"]7996[/TD]
[TD="align: right"]-1066[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="align: right"]3374[/TD]
[TD="align: right"]5832[/TD]
[TD="align: right"]-2458[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="align: right"]13091[/TD]
[TD="align: right"]12184[/TD]
[TD="align: right"]907[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="align: right"]41375[/TD]
[TD="align: right"]40040[/TD]
[TD="align: right"]1335[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="align: right"]5802[/TD]
[TD="align: right"]6408[/TD]
[TD="align: right"]-606[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="align: right"]4325[/TD]
[TD="align: right"]4702[/TD]
[TD="align: right"]-377[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="align: right"]2082[/TD]
[TD="align: right"]2830[/TD]
[TD="align: right"]-748[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="align: right"]21470[/TD]
[TD="align: right"]22101[/TD]
[TD="align: right"]-631[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="align: right"]7115[/TD]
[TD="align: right"]12438[/TD]
[TD="align: right"]-5323[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]


The first step of adding the table to Power Query, I only added Brand, Sales, and Plan. Var is a column that is created in the code.