Home » Custom Rank! Part 1

Custom Rank! Part 1

Solving Custom Grouping Part 8 challenge by Power Query, Power BI, Excel, Python and R

Rank the countries based on Olympic medals, starting with the number of gold medals. If two or more countries have the same number of gold medals, rank them by the number of silver medals. If the number of silver medals is also equal, then rank them by the number of bronze medals.

📌 Challenge Details and Links
Challenge Number: 106
Challenge Difficulty: ⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Custom Rank! Part 1 with Power Query

Power Query solution 1 for Custom Rank! Part 1, proposed by Zoran Milokanović: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  S = Table.AddIndexColumn(
    Table.Sort(Source, {{"Gold", 1}, {"Silver", 1}, {"Bronze", 1}}), 
    "Rank", 
    1
  )[[Rank], [Country]]
in
  S
Power Query solution 2 for Custom Rank! Part 1, proposed by Konrad Gryczan, PhD: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Tabela1"]}[Content], 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"Country", type text}, {"Gold", Int64.Type}, {"Silver", Int64.Type}, {"Bronze", Int64.Type}}
  ), 
  #"Sorted Rows" = Table.Sort(
    #"Changed Type", 
    {{"Gold", Order.Descending}, {"Silver", Order.Descending}, {"Bronze", Order.Descending}}
  )
in
  #"Sorted Rows"
Power Query solution 3 for Custom Rank! Part 1, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Sort   = Table.Sort(Source, {{"Gold", 1}, {"Silver", 1}, {"Bronze", 1}}), 
  Sol    = Table.AddIndexColumn(Sort, "Rank", 1)[[Rank], [Country]]
in
  Sol
Power Query solution 4 for Custom Rank! Part 1, proposed by Kris Jaganah: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Rank = Table.AddRankColumn(Source, "Rank", {{"Gold", 1}, {"Silver", 1}, {"Bronze", 1}})[
    [Rank], 
    [Country]
  ]
in
  Rank

Solving the challenge of Custom Rank! Part 1 with Excel

Excel solution 1 for Custom Rank! Part 1, proposed by Aditya Kumar Darak 🇮🇳: 
=TAKE(
    HSTACK(
        SEQUENCE(
            ROWS(
                C3:F12
            )
        ),
         SORT(
             C3:F12,
              {2,
              3,
              4},
              -1
         )
    ),
     ,
     2
)
Excel solution 2 for Custom Rank! Part 1, proposed by Owen Price: 
=HSTACK(
    SEQUENCE(
        ROWS(
            A3:A12
        )
    ),
    TAKE(
        SORT(
            A3:D12,
            {2,
            3,
            4},
            -1
        ),
        ,
        1
    )
)
Excel solution 3 for Custom Rank! Part 1, proposed by Owen Price: 
=HSTACK(
    SEQUENCE(
        ROWS(
            A3:A12
        )
    ),
    SORTBY(
        A3:A12,
        B3:B12,
        -1,
        C3:C12,
        -1,
        D3:D12,
        -1
    )
)
Excel solution 4 for Custom Rank! Part 1, proposed by Julian Poeltl: 
=LET(
    S,
    SORTBY(
        C3:C12,
        D3:D12,
        -1,
        E3:E12,
        -1,
        F3:F12,
        -1
    ),
    HSTACK(
        SEQUENCE(
            ROWS(
                S
            )
        ),
        S
    )
)
Excel solution 5 for Custom Rank! Part 1, proposed by Kris Jaganah: 
=LET(
    a,
    D3:D12+E3:E12/10+F3:F12/100,
    b,
    SORTBY(
        C3:C12,
        a,
        -1
    ),
    HSTACK(
        SEQUENCE(
            ROWS(
                b
            )
        ),
        b
    )
)
Excel solution 6 for Custom Rank! Part 1, proposed by Sunny Baggu: 
=LET(
 t, C3:G12,
 HSTACK(
 SEQUENCE(ROWS(t)),
 TAKE(SORT(t, {2, 3, 4, 5}, -1), , 1)
 )
)
Excel solution 7 for Custom Rank! Part 1, proposed by Bilal Mahmoud kh.: 
=SORTBY(
    C3:C12,
    BYROW(
        D3:G12,
        LAMBDA(
            x,
            --CONCAT(
                TEXT(
                    x,
                    "000"
                )
            )
        )
    ),
    -1
)
Excel solution 8 for Custom Rank! Part 1, proposed by Gerson Pineda: 
=HSTACK(
    ROW(
        1:10
    ),
    TAKE(
        SORT(
            C3:G12,
            {2,
            3,
            4},
            -1
        ),
        ,
        1
    )
)
Excel solution 9 for Custom Rank! Part 1, proposed by Jatin Phulwani: 
=SORTBY(
    C2:C11,
    D2:D11,
    -1,
    E2:E11,
    -1,
    F2:F11,
    -1
)
Excel solution 10 for Custom Rank! Part 1, proposed by Mey Tithveasna: 
=HSTACK(
    SEQUENCE(
        ROWS(
            C3:C12
        )
    ),
    TAKE(
        SORT(
            C3:F12,
            {2,
            3,
            4},
            -1
        ),
        ,
        1
    )
)
Excel solution 11 for Custom Rank! Part 1, proposed by Nicolas Micot: 
=TRIERPAR(
    C3:C12;
    D3:D12;
    -1;
    E3:E12;
    -1;
    F3:F12;
    -1
)
Excel solution 12 for Custom Rank! Part 1, proposed by Rick Rothstein: 
=HSTACK(
    SEQUENCE(
        ROWS(
            C3:C12
        )
    ),
    SORTBY(
        C3:C12,
        MID(
            CONCAT(
                TEXT(
                    D3:F12,
                    "00"
                )
            ),
            SEQUENCE(
                10,
                ,
                ,
                6
            ),
            6
        ),
        -1
    )
)

Solving the challenge of Custom Rank! Part 1 with Python

Python solution 1 for Custom Rank! Part 1, proposed by Konrad Gryczan, PhD: 
import pandas as pd 

path = "CH-106 Custom Rank.xlsx"
input = pd.read_excel(path, usecols="C:G", skiprows=1)
test  = pd.read_excel(path, usecols="L:M", skiprows=1)
test.columns = test.columns.str.replace(".1", "")

result = input.set_index(["Gold", "Silver", "Bronze"])
result = result.sort_index(ascending=[False, False, False]).reset_index()

print(result["Country"].equals(test["Country"])) # True

Solving the challenge of Custom Rank! Part 1 with R

R solution 1 for Custom Rank! Part 1, proposed by Konrad Gryczan, PhD: 
library(tidyverse)
library(readxl)

path = "files/CH-106 Custom Rank.xlsx"
input = read_excel(path, range = "C2:G12")
test = read_excel(path, range = "L2:M12")

# approach 1
result = input[order(-input$Gold, -input$Silver, -input$Bronze),] 
identical(result$Country, test$Country) 
# [1] TRUE

# approach 2

result = input %>% arrange(-Gold, -Silver, -Bronze)
identical(result$Country, test$Country)
# [1] TRUE

Leave a Reply