List the numbers where one digit is equal to sum of other digits. Ex. 4051 > here 5 = 4+0+1
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 365
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of One Digit Equals Other Sum with Power Query
Power Query solution 1 for One Digit Equals Other Sum, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.TransformColumns(
Source,
{
"Number",
each
let
a = List.Transform(List.Sort(Text.ToList(Text.From(_))), each Number.FromText(_))
in
if List.Sum(List.FirstN(a, List.Count(a) - 1)) = List.Last(a) then _ else null
}
)
in
Table.SelectRows(Ans, each ([Number] <> null))Power Query solution 2 for One Digit Equals Other Sum, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.SelectRows(
Source,
each [
T = Text.From([Number]),
L = Text.ToList(T),
N = List.Transform(L, Number.From),
M = List.Max(N),
R = List.Sum(N) = 2 * M
][R]
)
in
ReturnPower Query solution 3 for One Digit Equals Other Sum, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.SelectRows(
Fonte,
each [
a = List.Transform(Text.ToList(Text.From([Number])), Number.From),
b = List.RemoveNulls(
List.Transform(
{0 .. List.Count(a)},
(x) =>
try
List.Sum(
List.Transform(Text.ToList(Text.RemoveRange(Text.From([Number]), x)), Number.From)
)
otherwise
null
)
),
c = List.AnyTrue(List.Transform(List.Zip({a, b}), (x) => x{0} = x{1}))
][c]
)
in
resPower Query solution 4 for One Digit Equals Other Sum, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddTest = Table.AddColumn(
Source,
"Test",
each [
a = List.Transform(Text.ToList(Text.From([Number])), each Number.From(_)),
b = List.Sum(a),
c = List.Transform(a, each if _ = b - _ then 1 else 0),
d = List.Sum(c)
][d]
),
Answer = Table.RemoveColumns(Table.SelectRows(AddTest, each [Test] > 0), "Test")
in
AnswerPower Query solution 5 for One Digit Equals Other Sum, proposed by Glyn Willis:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Number", Int64.Type}, {"Answer Expected", Int64.Type}}
),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"C",
each [
N = [Number],
L = Text.ToList(Text.From([Number])),
P = List.Positions(L),
R = List.AnyTrue(
List.Transform(
P,
(x) =>
List.Sum(List.Transform(List.RemoveRange(L, x, 1), (y) => Int64.From(y)))
= Int64.From(L{x})
)
)
][R]
),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([C] = true))[[Number]]
in
#"Filtered Rows"Solving the challenge of One Digit Equals Other Sum with Excel
Excel solution 1 for One Digit Equals Other Sum, proposed by Rick Rothstein:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
n,
OR(
LET(
m,
0+MID(
n,
SEQUENCE(
LEN(
n
)
),
1
),
MAP(
m,
LAMBDA(
x,
SUM(
m
)-x=x
)
)
)
)
)
)
)Excel solution 2 for One Digit Equals Other Sum, proposed by John V.:
=TOCOL(
MAP(
A2:A10,
LAMBDA(
x,
LET(
b,
0&MID(
x,
ROW(
1:15
),
1
),
x/OR(
SUM(
-b
)/2=-b
)
)
)
),
2
)Excel solution 3 for One Digit Equals Other Sum, proposed by محمد حلمي:
=TOCOL(
MAP(
A2:A10,
LAMBDA(
a,
a/LET(
d,
SORT(
-MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
),
@d=SUM(
d
)-@d
)
)
),
2
)
//
=TOCOL(
MAP(
A2:A10,
LAMBDA(
a,
a/LET(
d,
-MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
m,
MIN(
d
),
m=SUM(
d
)-m
)
)
),
2
)Excel solution 4 for One Digit Equals Other Sum, proposed by Kris Jaganah:
=TOCOL(MAP(A2:A10,
LAMBDA(x,
x/LET(a,
-MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
b,
MIN(
a
),
(SUM(
a
)-b)=b))),
3)Excel solution 5 for One Digit Equals Other Sum, proposed by Julian Poeltl:
=LET(
AR,
A2:A10,
FILTER(
AR,
MAP(
AR,
LAMBDA(
A,
SUM(
N(
CHOOSECOLS(
MID(
A,
SEQUENCE(
1,
LEN(
A
)
),
1
)*1,
SEQUENCE(
1,
LEN(
A
)
)
)*2=SUM(
MID(
A,
SEQUENCE(
1,
LEN(
A
)
),
1
)*1
)
)
)
)
)=1
)
)Excel solution 6 for One Digit Equals Other Sum, proposed by Timothée BLIOT:
=TOCOL(MAP(A2:A10,
LAMBDA(z,
z/LET(A,
--REGEXEXTRACT(
z,
"d",
1
),
SUM(--(SUM(
A
)=A*2))=1))),
3)Excel solution 7 for One Digit Equals Other Sum, proposed by Hussein SATOUR:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
x,
LET(
b,
--SORT(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
),
MAX(
b
) = REDUCE(
,
b,
SUM
)/2
)
)
)
)Excel solution 8 for One Digit Equals Other Sum, proposed by Sunny Baggu:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
x,
LET(
_m,
SORT(
--MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
),
SUM(
DROP(
_m,
-1,
)
) = TAKE(
_m,
-1,
)
)
)
)
)Excel solution 9 for One Digit Equals Other Sum, proposed by Abdallah Ally:
=TOCOL(MAP(A2:A10,
LAMBDA(x,
x/LET(a,
x,
b,
--MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
REDUCE(0,
b,
LAMBDA(x,
y,
x+(y=SUM(
b
)-y)))))),
2)Excel solution 10 for One Digit Equals Other Sum, proposed by Abdallah Ally:
=TOCOL(
MAP(
A2:A10,
LAMBDA(
x,
x/LET(
a,
x,
b,
--MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
2*LARGE(
b,
1
)=SUM(
b
)
)
)
),
2
)Excel solution 11 for One Digit Equals Other Sum, proposed by 🇵🇪 Ned Navarrete C.:
=TOCOL(MAP(A2:A10,
LAMBDA(r,
LET(y,
--MID(
r,
SEQUENCE(
LEN(
r
)
),
1
),
r/(2*MAX(
y
)=SUM(
y
))))),
3)Excel solution 12 for One Digit Equals Other Sum, proposed by Pieter de Bruijn:
=TOCOL(
MAP(
A2:A10,
LAMBDA(
a,
LET(
b,
--MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
TOCOL(
MAP(
UNIQUE(
b
),
LAMBDA(
c,
IFS(
SUM(
FILTER(
b,
b<>c
)
)=c,
a
)
)
),
2
)
)
)
),
2
)
or inspired by Edwin Tisnado / Ned Navarrete C.
=TOCOL(MAP(A2:A10,
LAMBDA(a,
LET(b,
-MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
a/(2*MIN(
b
)=SUM(
b
))))),
2)Excel solution 13 for One Digit Equals Other Sum, proposed by Giorgi Goderdzishvili:
=TOCOL(MAP(A2:A10,
LAMBDA(t,
LET(
_nm,
t,
_cr,
--MID(
_nm,
SEQUENCE(
,
LEN(
_nm
)
),
1
),
_sq,
SEQUENCE(
,
LEN(
_nm
)
),
_mp,
MAP(
_cr,
_sq,
LAMBDA(
c,
s,
LET(
nms,
REPLACE(
_nm,
s,
1,
0
),
sm,
SUM(
--MID(
nms,
SEQUENCE(
,
LEN(
nms
)
),
1
)
)=c,
sm
)
)
),
_nm/(SUM(
--_mp
)=1)))),
3)Excel solution 14 for One Digit Equals Other Sum, proposed by Andres Rojas Moncada:
=EXCLUIR(REDUCE("",
A2:A10,
LAMBDA(a,
v,
LET(d,
EXTRAE(
v,
SECUENCIA(
LARGO(
v
)
),
1
)*1,
m,
MAX(
d
),
SI((SUMA(
d
)-m)=m,
APILARV(
a,
v
),
a)))),
1)Excel solution 15 for One Digit Equals Other Sum, proposed by Hazem Hassan:
=TOCOL(
MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
SORT(
--MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
,
-1
),
IF(
@a=SUM(
DROP(
a,
1
& )
),
x,
1/0
)
)
)
),
3
)
//
=TOCOL(
MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
LEN(
x
),
b,
--MID(
x,
SEQUENCE(
a
),
1
),
IF(
LARGE(
b,
1
)=SUM(
LARGE(
b,
SEQUENCE(
a-1
)+1
)
),
x,
1/0
)
)
)
),
3
)
//
=TOCOL(MAP(A2:A10,
LAMBDA(x,
LET(a,
--MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
b,
MAX(
a
),
IF(SUM((a<>b)*a)=b,
x,
1/0)))),
3)Excel solution 16 for One Digit Equals Other Sum, proposed by Luis Couto:
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(x,LET(d,SORT(MID(x,SEQUENCE(LEN(x)),1),,-1),--@d=SUM(--DROP(d,1))))))Excel solution 17 for One Digit Equals Other Sum, proposed by Hammed Toheeb:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
rows,
LET(
x,
--MID(
rows,
SEQUENCE(
LEN(
rows
)
),
1
),
SUM(
DROP(
SORT(
x,
1,
-1
),
1
)
)=MAX(
x
)
)
)
)
)Excel solution 18 for One Digit Equals Other Sum, proposed by Caroline Blake:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
arr,
LET(
a,
MID(
arr,
SEQUENCE(
,
LEN(
arr
)
),
1
),
ind,
MAP(
a,
LAMBDA(
b,
SUM(
VALUE(
a
)
)-VALUE(
b
)=VALUE(
b
)
)
),
ftr,
IFERROR(
FILTER(
ind,
ind=TRUE
),
0
),
IF(
ftr=TRUE,
arr,
0
)
)
)
)>0
)Solving the challenge of One Digit Equals Other Sum with Python
Python solution 1 for One Digit Equals Other Sum, proposed by Jan Willem Van Holst:
import pandas as pd
df = pd.read_csv(r"C:JWLENOVOPYTHONExcel-Challenge_365.csv", sep=";")
mylist=df['Number'].to_list()
def fx(inputNumber):
unpack = [int(x) for x in [*str(inputNumber)]]
totalUnpack = sum(unpack)
listOfsums = [totalUnpack-x for x in unpack]
result = [a == b for a, b in zip(unpack, listOfsums)]
return any(result)
print([elem for elem in mylist if fx(elem)])
Solving the challenge of One Digit Equals Other Sum with R
R solution 1 for One Digit Equals Other Sum, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Excel/365 One digit is Equal to Sum of other Digits.xlsx", range = "A1:A10")
test = read_excel("Excel/365 One digit is Equal to Sum of other Digits.xlsx", range = "B1:B5")
evaluate = function(number) {
digits = as.numeric(unlist(strsplit(as.character(number), "")))
check = purrr::map_lgl(digits, ~ .x == sum(digits[-which(digits == .x)]))
return(any(check))
}
result = input %>%
mutate(eval = map_lgl(Number, evaluate)) %>%
filter(eval) %>%
select(`Answer Expected` = Number)
&&