Home » Sort by Unit Digit

Sort by Unit Digit

Sort the numbers on the basis of unit digit (last digit). In case of tie of unit digits, FIFO sequence should be maintained.

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 513
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Sort by Unit Digit with Power Query

Power Query solution 1 for Sort by Unit Digit, proposed by Aditya Kumar Darak 🇮🇳: 
let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Return = Table.Sort(Source, each Number.Mod([Numbers], 10))
in
  Return
Power Query solution 2 for Sort by Unit Digit, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Sol = Table.Sort(Source, {each Text.End(Text.From([Numbers]),1), each List.PositionOf(Source[Numbers], [Numbers] )})
in
 Sol

Pareciera que esta formula funciona 

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Sol = Table.Sort(Source, each Text.End(Text.From([Numbers]),1))
in
 Sol
Power Query solution 3 for Sort by Unit Digit, proposed by Ramiro Ayala Chávez: 
let
  S   = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  a   = Table.TransformColumnTypes(S, {"Numbers", type text}), 
  b   = Table.Sort(a, {each Text.End([Numbers], 1)}), 
  Sol = Table.RenameColumns(b, {"Numbers", "Answer Expected"})
in
  Sol
Power Query solution 4 for Sort by Unit Digit, proposed by Yaroslav Drohomyretskyi: 
let
  S = Table.Sort(
    Excel.CurrentWorkbook(){[Name = "t"]}[Content], 
    each Text.End(Text.From([Numbers]), 1)
  )
in
  S
Power Query solution 5 for Sort by Unit Digit, proposed by Szabolcs Phraner: 
Table.Sort(
  Source, 
  {each Number.From(Text.End([Numbers], 1)), each List.PositionOf(Source[Numbers], [Numbers])}
)
Power Query solution 6 for Sort by Unit Digit, proposed by Szabolcs Phraner: 
let
Source =..,
IndexCol = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
 Last_Digit = Table.AddColumn(IndexCol, "Last Digit", each Text.End([Numbers], 1), type text),
 ChangeType = Table.TransformColumnTypes(Last_Digit,{{"Last Digit", Int64.Type}}),
 Sort = Table.Sort(ChangeType,{{"Last Digit", Order.Ascending}, {"Index", Order.Ascending}}),
 SelectCol = Table.SelectColumns(Sort,{"Numbers"})
in
 SelectCol
Power Query solution 7 for Sort by Unit Digit, proposed by Ernesto Vega Castillo: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Split = Table.SplitColumn(
    Table.TransformColumnTypes(
      Table.DuplicateColumn(Source, "Numbers", "Numbers - Copy"), 
      {{"Numbers - Copy", type text}}, 
      "es-ES"
    ), 
    "Numbers - Copy", 
    Splitter.SplitTextByPositions({0, 1}, true), 
    {"Numbers - Copy.1", "Numbers - Copy.2"}
  ), 
  Sol = Table.SelectColumns(
    Table.Sort(
      Split, 
      {{"Numbers - Copy.2", Order.Ascending}, {"Numbers - Copy.1", Order.Descending}}
    ), 
    {"Numbers"}
  )
in
  Sol

Solving the challenge of Sort by Unit Digit with Excel

Excel solution 1 for Sort by Unit Digit, proposed by Rick Rothstein: 
=SORTBY(A2:A10,MOD(A2:A10,10))

=0+MID(SORT(RIGHT(A2:A10)&A2:A10),2,9)
Excel solution 2 for Sort by Unit Digit, proposed by John V.: 
=SORTBY(
    A2:A10,
    RIGHT(
        A2:A10
    )
)
✅=SORTBY(
    A2:A10,
    MOD(
        A2:A10,
        10
    )
)
✅=LET(
    n,
    A2:A10,
    SORTBY(
        n,
        n-TRUNC(
            n,
            -1
        )
    )
)
✅=LET(
    n,
    A2:A10,
    SORTBY(
        n,
        n-10*INT(
            n/10
        )
    )
)
Excel solution 3 for Sort by Unit Digit, proposed by محمد حلمي: 
=SORTBY(
    A2:A10,
    RIGHT(
        A2:A10
    )
)
Excel solution 4 for Sort by Unit Digit, proposed by Kris Jaganah: 
=SORTBY(
    A2:A10,
    RIGHT(
        A2:A10
    )
)
Excel solution 5 for Sort by Unit Digit, proposed by Julian Poeltl: 
=SORTBY(
    A2:A10,
    RIGHT(
        A2:A10
    )
)
Excel solution 6 for Sort by Unit Digit, proposed by Aditya Kumar Darak 🇮🇳: 
=SORTBY(
    A2:A10,
    RIGHT(
        A2:A10
    )
)
Excel solution 7 for Sort by Unit Digit, proposed by Timothée BLIOT: 
=SORTBY(
    A2:A10,
    --RIGHT(
        A2:A10
    )
)
Excel solution 8 for Sort by Unit Digit, proposed by Hussein SATOUR: 
=SORTBY(
    A2:A10,
    RIGHT(
        A2:A10
    ),
    ,
    SEQUENCE(
        9
    ),
    1
)
Excel solution 9 for Sort by Unit Digit, proposed by Oscar Mendez Roca Farell: 
=SORTBY(A2:A10, MOD(A2:A10, 10))
Excel solution 10 for Sort by Unit Digit, proposed by Sunny Baggu: 
=SORTBY(
    A2:A10,
    RIGHT(
        A2:A10
    )
)
Excel solution 11 for Sort by Unit Digit, proposed by Abdallah Ally: 
=SORTBY(
    A2:A10,
    RIGHT(
        A2:A10
    )
)
Excel solution 12 for Sort by Unit Digit, proposed by Pieter de B.: 
=LET(
    n,
    A2:A10,
    l,
    LEN(
        n
    ),
    m,
    MAX(
        l
    ),
    s,
    SEQUENCE(
        ,
        m
    ),
    TAKE(
        SORT(
            HSTACK(
                n,
                IFERROR(
                    MID(
                        n,
                        1+l-m+m-s,
                        1
                    ),
                    ""
                )
            ),
            s+1
        ),
        ,
        1
    )
)
Excel solution 13 for Sort by Unit Digit, proposed by Hamidi Hamid: 
=SORTBY(A2:A10,RIGHT(A2:A10,1))
Excel solution 14 for Sort by Unit Digit, proposed by Andy Heybruch: 
=SORTBY(
    A2:A10,
    RIGHT(
        A2:A10,
        1
    )
)
Excel solution 15 for Sort by Unit Digit, proposed by Bilal Mahmoud kh.: 
=LET(
    a,
    --RIGHT(
        A2:A10,
        1
    ),
    b,
    REDUCE(
        ,
        SEQUENCE(
            10,
            ,
            0
        ),
        LAMBDA(
            x,
            y,
            VSTACK(
                x,
                FILTER(
                    A2:A10,
                    a=y,
                    0
                )
            )
        )
    ),
    FILTER(
        b,
        b<>0
    )
)
Excel solution 16 for Sort by Unit Digit, proposed by Imam Hambali: 
=TOCOL(IF(SEQUENCE(,COUNTA(A2:A10))=(RIGHT(A2:A10)*1),1,1/0)*A2:A10,3,1)
Excel solution 17 for Sort by Unit Digit, proposed by Imam Hambali: 
=SORTBY(A2:A10,RIGHT(A2:A10),1)
Excel solution 18 for Sort by Unit Digit, proposed by Milan Shrimali: 
=filter(sort(map(A1:A9,lambda(x,hstack(x,right(x,1)))),2,1),{1,0})
Excel solution 19 for Sort by Unit Digit, proposed by Nicolas Micot: 
=TRIERPAR(A2:A10;DROITE(A2:A10;1);1;LIGNE(A2:A10);1)
Excel solution 20 for Sort by Unit Digit, proposed by Songglod P.: 
=LET(n,A2:A10,SORTBY(n,RIGHT(n)))
Excel solution 21 for Sort by Unit Digit, proposed by Ricardo Alexis Domínguez Hernández: 
=SORTBY(A2:A10,RIGHT(A2:A10))
Excel solution 22 for Sort by Unit Digit, proposed by Ogunronbi Taiwo Fisayo: 
Pretty easy:
SORTBY(
    A2:A10,
    RIGHT(
        A2:A10,
        1
    )
)

Solving the challenge of Sort by Unit Digit with Python

Python solution 1 for Sort by Unit Digit, proposed by Konrad Gryczan, PhD: 
import pandas as pd
path = "513 Sort by Unit Digit.xlsx"
input = pd.read_excel(path, usecols = "A")
test = pd.read_excel(path, usecols = "B")
result = input.copy()
result['sort'] = result['Numbers'].apply(lambda x: x % 10)
result = result.sort_values(by = 'sort').drop(columns = 'sort').reset_index(drop = True)
print(result['Numbers'].equals(test['Answer Expected'])) # True

Solving the challenge of Sort by Unit Digit with Python in Excel

Python in Excel solution 1 for Sort by Unit Digit, proposed by Alejandro Campos: 
numbers = xl("A2:A10")[0]
df = pd.DataFrame({'Numbers': numbers})
def last_digit(x):
 return x % 10
df_sorted = df.sort_values(by='Numbers', key=lambda x: x.map(last_digit), kind='stable').reset_index(drop=True)
df_sorted
Python in Excel solution 2 for Sort by Unit Digit, proposed by Abdallah Ally: 
df = xl("A1:B10", headers=True)
# Perform data munging
df['My Answer'] = sorted(df['Numbers'], key=lambda x: str(x)[-1])
df['Check'] = df['Answer Expected'] == df['My Answer']
df

Solving the challenge of Sort by Unit Digit with R

R solution 1 for Sort by Unit Digit, proposed by Konrad Gryczan, PhD: 
library(tidyverse)
library(readxl)
library(stringi)
path = "513 Sort by Unit Digit.xlsx"
input = read_excel(path, range = "A1:A10")
test = read_excel(path, range = "B1:B10")
result = input %>%
 arrange(str_sub(Numbers, -1)) 
identical(result$Numbers, test$`Answer Expected`)
# TRUE

&&&

Leave a Reply