Ranking multiple criteria without duplicates

[annabeladams]

I understand that this question has been answered before, but I am running into ongoing problems with the formulas I have tried so far.

In the following data set, I want to rank highest to lowest scores in column A. Where there are ranked duplicates, I want to apply the secondary criteria in coloumn B, but here, the higher the number the worse the performance. If there are still duplicates, I then want to apply the third criteria in column C. Here, again the lower the number the better performance. I would not like any duplicates in the data set. Any advice would be greatly appreciated!

 

[jkpieterse]

Very simple using PowerQuery here is the m-code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CORRECT", Int64.Type}, {"CORRECTIONS 2*", Int64.Type}, {"ORDER 3*", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"CORRECT", Order.Ascending}, {"CORRECTIONS 2*", Order.Descending}, {"ORDER 3*", Order.Descending}}),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows")
in
    #"Removed Duplicates"

 

[annabeladams]

Very simple using PowerQuery here is the m-code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CORRECT", Int64.Type}, {"CORRECTIONS 2*", Int64.Type}, {"ORDER 3*", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"CORRECT", Order.Ascending}, {"CORRECTIONS 2*", Order.Descending}, {"ORDER 3*", Order.Descending}}),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows")
in
    #"Removed Duplicates"

Unfortunately I have a MacOS, so I don't think I am able to use PowerQuery?

 

[jkpieterse]

Alas no. Excel on mac is years behind. You could do this manually by first doing a custom sort and then removing duplicates.

 

[annabeladams]

I have tried the RANK.EQ function below that ranks the numbers but includes duplicates.

=RANK.EQ(A3,$A$5:$A$23)

To remove duplicates I applied that COUNTIF function (below) however although it is individually ranking the numbers, my understanding is that if the same number occurs twice, it ranks according to the order of that number in the list.

=COUNTIF($A$3:$A$23,">"&$A3)+COUNTIF($A$3:A3,A3)

I don't want to rank according to order in the list, hence why I have included the secondary criteria. To apply the second criteria, I used the below function, but the ranked numbers now aren't incremental, and there are duplicates.

=RANK.EQ($A3,$A$3:$A$23)+COUNTIFS($A$3:$A$23,$A3,$B$3:$B$23,"<"&$B3).

Then when I try to apply the tertiary criteria using the below forumla, this doesn't seem to work either.

=RANK.EQ($A3,$A$3:$A$23)+COUNTIFS($A$3:$A$23,$A3,$B$3:$B$23,">"&$B3)+COUNTIFS($B$3:$B$23,$B3,$C$3:$C$23,"<"&$C3)

Sorry for all the questions, I am just not sure of the best way forward! I also don't know if I can attach a worksheet if that would be easier?

 

[annabeladams]

WAIT! I figured out how to do it using PowerQuery and the code you sent throgh (as I have a Windows VM) and it works perfectly. However, as I am new to m-code, is there a way I can automatically rank performance in a sperate column as shown in column D in this screenshot (I did it manually here)? Please let me know!

 

[jkpieterse]

Sure. It involves adding a column which gives the # of rows in the table, then adding an index column and subtraction these two:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CORRECT", Int64.Type}, {"CORRECTIONS 2*", Int64.Type}, {"ORDER 3*", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"CORRECT", Order.Ascending}, {"CORRECTIONS 2*", Order.Descending}, {"ORDER 3*", Order.Descending}}),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows"),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Custom", each Table.RowCount(#"Removed Duplicates")),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 0, 1),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Custom.1", each [Custom]-[Index]),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"Custom.1", "Rank"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Custom", "Index"})
in
    #"Removed Columns"

 

[annabeladams]

AMAZING! Thank you. I have one final question! I have realised that when the numbers are sorted I can no longer identify who the score belongs to (NAME) or the date, (DATE) unless I do so manually, see picture. How can I ensure that the correct name and date appears next to the numbers when ranked?

Also, are there any good resources you could reccomend for M-code so that I can learn to do this myself? Thank you again!

 

[jkpieterse]

If you make sure the first two columns are INSIDE the table that is treated by the M code, all should be well.
There is no need to learn M code, you just follow the steps:
- Select table
- Data, From Table (make sure everything needed in the table is selected!)
- Sort the columns, in the priority order as you need it
- Select the columns which you need to have the unique items from and click Remove rows, duplicates
- Insert a calculated column with this formula (this is the only step requiring knowledge of M):
Table.RowCount(#"Removed Duplicates")
- Insert an index column starting at zero
- Insert a calculated column which subtracts the Index from the previous calculated column
- Done

 

[annabeladams]

Thank you, that all worked perfectly. And thanks for the advice re M-code, I'll give it a go!