Home » List Harshad Numbers

List Harshad Numbers

List the numbers which are Harshad number (also known as Niven number or Multidigital number). A Harshad number is that number which is divisible by the sum of its digits Example: 1729 is a Harshad number as 1+7+2+9 = 19 and 1729 is divisible by 19 as remainder is 0 when 1729 is divided by 19.

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

Solving the challenge of List Harshad Numbers with Power Query

Power Query solution 1 for List Harshad Numbers, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Calc = Table.FromColumns(
    {
      Source[Number], 
      List.Transform(
        Source[Number], 
        each Number.Mod(_, List.Sum(List.Transform(Text.ToList(Text.From(_)), Number.From)))
      )
    }
  ), 
  Sol = Table.SelectRows(Calc, each [Column2] = 0)[Column1]
in
  Sol
Power Query solution 2 for List Harshad Numbers, proposed by Luan Rodrigues: 
let
  Fonte = Tabela1, 
  tab = Table.AddColumn(
    Fonte, 
    "Personalizar", 
    each [
      a = List.Sum(List.Transform(Text.ToList(Text.From([Number])), each Number.From(_))), 
      b = Number.From([Number] / a) = Number.From(Int64.From([Number] / a))
    ][b]
  ), 
  res = Table.SelectRows(tab, each ([Personalizar] = true))[[Number]]
in
  res
Power Query solution 3 for List Harshad Numbers, proposed by Jaroslaw Kujawa: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table16"]}[Content], 
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"Number", Int64.Type}}), 
  #"Added Custom" = Table.AddColumn(
    #"Changed Type", 
    "Custom", 
    each Number.Mod(
      [Number], 
      List.Sum(List.Transform(Text.ToList(Text.From([Number])), Number.FromText))
    )
  )
in
  Table.SelectRows(#"Added Custom", each ([Custom] = 0))[Number]
Power Query solution 4 for List Harshad Numbers, proposed by Rafael González B.: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Numbers"]}[Content], 
  NumberToText = Table.TransformColumnTypes(Source, {{"Number", type text}}), 
  Operations = Table.AddColumn(
    NumberToText, 
    "CustomList", 
    each Text.Contains(
      Text.From(
        Number.From([Number]) / List.Sum(List.Transform(Text.ToList([Number]), each Number.From(_)))
      ), 
      ","
    )
  ), 
  Result = Table.TransformColumnTypes(
    Table.RemoveColumns(Table.SelectRows(Operations, each ([CustomList] = false)), {"CustomList"}), 
    {"Number", Int64.Type}
  )
in
  Result
Power Query solution 5 for List Harshad Numbers, proposed by Victor Momoh (MVP, MOS, R.Eng): 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Result = Table.SelectRows(
    Source, 
    each Number.Mod(
      _[Number], 
      List.Sum(List.Transform(Text.ToList(Text.From(_[Number])), Number.From))
    )
      = 0
  )
in
  Result
Power Query solution 6 for List Harshad Numbers, proposed by Udit Chatterjee: 
let
  Source = #"Challenge-143", 
  ccHarshadNumCheck = Table.AddColumn(
    Source, 
    "Harshad Number Check", 
    each Number.Mod(
      [Number], 
      List.Sum(List.Transform(Text.ToList(Number.ToText([Number])), each Number.FromText(_)))
    )
      = 0, 
    type logical
  ), 
  filteredRows = Table.SelectRows(ccHarshadNumCheck, each ([Harshad Number Check] = true)), 
  keepRequiredCol = Table.RemoveColumns(filteredRows, {"Harshad Number Check"})
in
  keepRequiredCol
Power Query solution 7 for List Harshad Numbers, proposed by Sue Bayes: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Type = Table.TransformColumnTypes(Source, {{"Number", type text}}), 
  TextToList = Table.AddColumn(Type, "Custom", each Text.ToList([Number])), 
  Expand = Table.TransformColumnTypes(
    Table.ExpandListColumn(TextToList, "Custom"), 
    {{"Custom", Int64.Type}, {"Number", Int64.Type}}
  ), 
  Grp = Table.Group(Expand, {"Number"}, {{"Sum", each List.Sum([Custom]), type nullable number}}), 
  Divide = Table.TransformColumnTypes(
    Table.AddColumn(Grp, "Division", each [Number] / [Sum], type number), 
    {{"Division", Int64.Type}}
  ), 
  Answer = Table.AddColumn(
    Divide, 
    "Answer", 
    each if [Division] * [Sum] = [Number] then [Number] else null, 
    type number
  )[[Answer]], 
  Filter = Table.SelectRows(Answer, each ([Answer] <> null))
in
  Filter

Solving the challenge of List Harshad Numbers with Excel

Excel solution 1 for List Harshad Numbers, proposed by Rick Rothstein: 
=FILTER(A2:A9,MAP(A2:A9,LAMBDA(x,MOD(x,SUM(0+MID(x,SEQUENCE(LEN(x)),1)))))=0)
Excel solution 2 for List Harshad Numbers, proposed by John V.: 
=LET(n,A2:A9,s,ROW(1:10),FILTER(n,MOD(n,MMULT(-(0&MID(n,TOROW(s),1)),s^0))=0))
Excel solution 3 for List Harshad Numbers, proposed by محمد حلمي: 
=FILTER(A2:A9,MAP(A2:A9,LAMBDA(a,
MOD(a,SUM(-MID(a,SEQUENCE(LEN(a)),1)))=0)))
Excel solution 4 for List Harshad Numbers, proposed by Kris Jaganah: 
=FILTER(A2:A9,MAP(A2:A9,LAMBDA(x,MOD(x,SUM(--MID(x,SEQUENCE(LEN(x)),1)))))=0)
Excel solution 5 for List Harshad Numbers, proposed by Julian Poeltl: 
=FILTER(A2:A9,MAP(A2:A9,LAMBDA(N,MOD(N,SUM(--MID(N,SEQUENCE(LEN(N)),1)))=0)))
Excel solution 6 for List Harshad Numbers, proposed by Alejandro Campos: 
=TOCOL(
 MAP(
 A2:A9,
 LAMBDA(x,
 LET(
 e, SUM(--MID(x, SEQUENCE(LEN(x)), 1)),
 FILTER(x, MOD(x, e) = 0)))),
 3)
Excel solution 7 for List Harshad Numbers, proposed by Timothée BLIOT: 
=FILTER(A2:A9,MAP(A2:A9,LAMBDA(x,MOD(x,SUM(MID(x,SEQUENCE(LEN(x)),1)*1))=0)))
Excel solution 8 for List Harshad Numbers, proposed by Hussein SATOUR: 
=FILTER(A2:A9, MAP(A2:A9, LAMBDA(x, MOD(x, SUM(--MID(x, SEQUENCE(LEN(x)), 1)))=0)))
Excel solution 9 for List Harshad Numbers, proposed by Sunny Baggu: 
=FILTER(A2:A9,MAP(A2:A9,LAMBDA(a,
LET(_m,MID(a,SEQUENCE(LEN(a)),1),
_sum,SUM(--_m),
MOD(a,_sum)=0))))
Excel solution 10 for List Harshad Numbers, proposed by Sunny Baggu: 
=FILTER(A2:A9,MAP(A2:A9,LAMBDA(a,MOD(a,SUMPRODUCT(--MID(a,SEQUENCE(LEN(a)),1)))=0)))
Excel solution 11 for List Harshad Numbers, proposed by Md. Zohurul Islam: 
=LET(z,A2:A9,u,MAP(z,LAMBDA(x,IF(MOD(x,SUM(ABS(MID(x,SEQUENCE(LEN(x)),1))))=0,1,0))),FILTER(z,u>0))
Excel solution 12 for List Harshad Numbers, proposed by Stefan Olsson: 
=FILTER(A2:A9, BYROW(A2:A9, LAMBDA(x, 1>MOD(x, SUM(--MID(x, SEQUENCE(LEN(x)),1))))))
Excel solution 13 for List Harshad Numbers, proposed by Victor Momoh (MVP, MOS, R.Eng): 
=FILTER(A2:A9,MAP(A2:A9,LAMBDA(x,MOD(x,SUM(--MID(x,SEQUENCE(LEN(x)),1)))))=0)
Excel solution 14 for List Harshad Numbers, proposed by Abhishek Kumar Jain: 
=FILTER(A2:A9,MAP(A2:A9,LAMBDA(x,MOD(x,SUM(--MID(x,SEQUENCE(LEN(x)),1)))=0)))
Excel solution 15 for List Harshad Numbers, proposed by Guillermo Arroyo: 
=LET(m,A2:A9,FILTER(m,MAP(m,LAMBDA(n,MOD(n,(SUM(--MID(n,SEQUENCE(LEN(n)),1))))=0))))
Excel solution 16 for List Harshad Numbers, proposed by Rayan S.: 
=LET(
 Sol, MAP(
 A2:A9,
 LAMBDA(a,
 LET(
 split, MID(a, SEQUENCE(LEN(a)), 1),
 Divide, a / SUM(split + 0),
 IF(Divide = ROUND(Divide, 0), a, 0)
 )
 )
 ),
 FILTER(Sol, Sol > 0)
)
Excel solution 17 for List Harshad Numbers, proposed by Ibrahim Sadiq: 
=LET(a,A2:A9,FILTER(a,MOD(a,MMULT(0+(0&MID(a,SEQUENCE(,9),1)),ROW(1:9)^0))=0))
Excel solution 18 for List Harshad Numbers, proposed by Amr Tawfik CMA®,FMVA,Lean Coach: 
=FILTER(A2:A9,IF(MOD(A2:A9,MAP(A2:A9,LAMBDA(x,SUM(--MID(x,SEQUENCE(LEN(x),,),1)))))=0,TRUE,FALSE))

Solving the challenge of List Harshad Numbers with Python in Excel

Python in Excel solution 1 for List Harshad Numbers, proposed by Alejandro Campos: 
[n for n in xl("A2:A9")[0] if n % sum(map(int, str(n))) == 0]

Solving the challenge of List Harshad Numbers with SQL

SQL solution 1 for List Harshad Numbers, proposed by Zoran Milokanović: 
WITH /* Microsoft SQL Server 2019 */
CALC
AS
(
 SELECT
 D.NUMBER, CAST(D.NUMBER AS BIGINT) AS TEMP, CAST(0 AS BIGINT) AS DIGIT
 FROM DATA D
 UNION ALL
 SELECT
 C.NUMBER, CAST(C.TEMP / 10 AS BIGINT) AS TEMP, CAST(C.TEMP % 10 AS BIGINT) AS DIGIT
 FROM CALC C
 WHERE
 C.TEMP > 0
)
SELECT
 C.NUMBER AS ANSWER_EXPECTED
FROM CALC C
GROUP BY
 C.NUMBER
HAVING
 C.NUMBER % SUM(C.DIGIT) = 0
ORDER BY
 1
;

&&&

Leave a Reply