Excel BI Challenge Workout 185

— CAN YOU SOLVE THIS - EXCEL CHALLENGE 185 —
(Solutions in any language are also welcome for Excel Challenges)

For the given range in From & To, find the count, smallest and largest numbers where first digit of a number = sum of remaining digits
For ex. 541 where first digit 5 is equal to sum of remaining digits 4 & 1.

(Your formula need not be a single formula. You can write multiple formulas to arrive at a solution. Also your formula need not be different from others as long as you have worked out your formula independently)

#excel, #excelchallenge, #powerquerychallenge, #daxchallenge, #m, #dax, #powerbi, #powerquery, #powerpivot, #sheets, #googlesheets, #data, #analytics, #businessintelligence, #finance, #office365, #python, #r

Faster one

``````=LET(s,SEQUENCE(10,,0),r,REDUCE(s,SEQUENCE(5),LAMBDA(a,z,LET(b,TAKE(a,,1)&TOROW(s),
t,MAP(b,LAMBDA(b,SUM(--MID(b,SEQUENCE(LEN(b)),1)))),HSTACK(TOCOL(IFS(t<10,--b),3),TOCOL(IFS(t<10,t),3))))),
g,SEQUENCE(,6),d,DROP(r,,1)*10^g,e,TAKE(r,,1),n,SORT(TOCOL(IFS(g>=LEN(e),d+e),3)),x,XMATCH(A2:A6,n,1),y,MATCH(B2:B6,n),
VSTACK({"Count","Smallest","Largest"},HSTACK(y-x+1,INDEX(n,HSTACK(x,y)))))
``````

La ultima fila demora bastante, a pesar de utilizar buffer, alguna sugerencia??

``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
[a = Record.ToList(_),
b = List.Transform({a{0}..a{1}}, each List.Buffer(List.Transform(Text.ToList(Text.From(_)), Number.From))),
c = List.Transform(b, List.Count),
d = List.Select(b, each List.First(_) = List.Sum(List.Skip(_))),
e = List.Count(d),
f = Number.From(Text.Combine(List.Transform(List.First(d), each Text.From(_)), "")),
g = Number.From(Text.Combine(List.Transform(List.Last(d), each Text.From(_)), ""))
][[e],[f],[g]])[[Custom]],
Sol = Table.ExpandRecordColumn(Lista, "Custom", {"e", "f", "g"}, {"Count", "Smallest", "Largest"})
in
Sol
``````

Slow but sure

``````=LET(s,SEQUENCE(10^6),m,MAP(s,LAMBDA(a,SUM(--MID(a&"00000",SEQUENCE(6),1)))),c,m<10,d,FILTER(m,c)*10^SEQUENCE(,6),e,FILTER(s,c),n,SORT(TOCOL(IFS(LEN(d)>LEN(e),d+e),3)),
REDUCE({"Count","Smallest","Largest"},SEQUENCE(ROWS(A2:A6)),LAMBDA(a,v,LET(x,XMATCH(INDEX(A2:A6,v),n,1),y,XMATCH(INDEX(B2:B6,v),n,-1),VSTACK(a,HSTACK(y-x+1,INDEX(n,x),INDEX(n,y)))))))
``````

LinkedIn Post by:Oscar Mendez Roca Farell

hi everyone, my proposal solution only for the firsts 4 ranges:

`=REDUCE({"Count"\"Smallest"\"Largest"}, MAP(A2:A5, B2:B5, LAMBDA(a, b, LET(_s, SEQUENCE(b-a+1, ,a), _f, FILTER(_s, MAP(_s, LAMBDA(c, LET(_t, TOCOL(--MID(c, SEQUENCE(10),1),3), IFERROR(TAKE(_t, 1)/SUM(DROP(_t, 1)), ))))=1), TEXTJOIN(", ", , COUNT(_f), TAKE(_f, 1), TAKE(_f,-1))))), LAMBDA(i, x, VSTACK(i, TEXTSPLIT(x, ", "))))`

I can’t get this one. It’s too hard for my puny brain even to convert into a single array solution (#CALC! error).

This equation only works up until row 5 due to SEQUENCE limitations:

``````=LET(A,A2, B,B2,
C, SEQUENCE(B-A+1,,A),
D, IFERROR(--MID(C,SEQUENCE(,LEN(B)),1),0),
E, DROP(D,,1),
F, FILTER(C,TAKE(D,,1)=BYROW(E,LAMBDA(E,SUM(E)))),
G, HSTACK(COUNT(F),MIN(F),MAX(F)),
G)
``````

Hi to all!

Slow, but works for all the entries:

`=DROP(REDUCE(0,SEQUENCE(ROWS(A2:B6)),LAMBDA(a,v,LET(f,INDEX(A2:A6,v),s,SEQUENCE((9+INDEX(B2:B6,v)-f)/9,9,f),n,TOCOL(s/MAP(s,LAMBDA(x,-LEFT(x)=SUM(-MID(x*10^6,ROW(2:9),1)))),2),VSTACK(a,HSTACK(ROWS(n),MIN(n),MAX(n)))))),1)`

Blessings!

First Digit is Equal to Sum of Remaining Digits w/ #powerquery. #bitanbit #powerbi

``````let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
Solution = Table.FromRecords(Table.TransformRows(Source, each
let
r = List.Buffer([List.Select](http://list.select/)({[From]..[To]}, each
let
l = List.Transform(Text.ToList(Text.From(_)), (n) => Number.From(n))
in
l{0} = List.Sum(List.Skip(l))
))
in [Count = List.Count(r), Smallest = List.Min(r), Largest = List.Max(r)]
))
in
Solution
``````

I had the same problem as Sunny Baggu (could not process the last range); however, this formula works for the first 4…

`=TEXTSPLIT(TEXTJOIN("/",,IFERROR(MAP(A2:A6,B2:B6,LAMBDA(m,n,LET(s,SEQUENCE(n-m+1,,m),f,FILTER(s,MAP(s,LAMBDA(x,0+LEFT(x)=SUM(0+MID(x,SEQUENCE(,LEN(x)),1))-LEFT(x)))),COUNT(f)&" "&TAKE(f,1)&" "&TAKE(f,-1)))),""))," ","/")`

``````=LET(
v,SEQUENCE((B6-A6)/100+1,100,A6),
r,TOCOL(MAP(IF(v>B6,123,v),LAMBDA(e,LET(
r,(LEFT(e)+0)=(SUM(--(MID(e,SEQUENCE(LEN(e)-1,,2),1)))),
IF(r,e,NA())))),2),HSTACK(ROWS(r),AGGREGATE({15,14},,r,1)))
``````

``````=REDUCE({"Count","Smallest","Largest"},A2:A5,
LAMBDA(a,b,LET(
r,SEQUENCE(OFFSET(b,,1)-b+1,,b),
v,FILTER(r,MMULT((0&MID(r,SEQUENCE(,9,2),1))+0,
SEQUENCE(9)^0)=LEFT(r)+0),
VSTACK(a,HSTACK(ROWS(v),AGGREGATE({15,14},,v,1))))))
``````

Doesn’t work for the last row (seems like Sequence doesn’t like big numbers) but I tried:

`=LET(_nombres;SEQUENCE(B2-A2+1;1;A2);_firstDigit;BYROW(_nombres;LAMBDA(a;GAUCHE(a;1)+0));_sommeRemaining;BYROW(CTXT(_nombres;0);LAMBDA(a;SOMME(SIERREUR(STXT(a;SEQUENCE(1;10;2);1)+0;0))));_nombresQuiMarchent;FILTRE(_nombres;_firstDigit=_sommeRemaining);_resultatsConcat;NBVAL(_nombresQuiMarchent)&";"&MIN(_nombresQuiMarchent)&";"&MAX(_nombresQuiMarchent);FRACTIONNER.TEXTE(_resultatsConcat;";")) `

Slow brute force solution:

``````let
Source = Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="Table1"]}[Content],{{"From", Int64.Type}, {"To", Int64.Type}}),
Range = List.Buffer({1..List.Max(Source[To])}),
Filtered = List.Buffer([List.Select](http://list.select/)(Range, each
let
digits = Number.RoundDown(Number.Log10(_)),
first = Number.IntegerDivide(_, Number.Power(10, digits)),
rest = Number.Mod(_, Number.Power(10, digits)),
summed = List.Sum(
List.Transform(
{0..digits},
(i) => (
Number.Mod(rest, Number.Power(10, i+1)) -
Number.Mod(rest, Number.Power(10, i))
) / Number.Power(10, i)
)
),
result = (first = summed)
in
result
)),
let in_range = List.Buffer([List.Select](http://list.select/)(Filtered, (i) => i >= [From] and i <= [To]))
in [Count = List.Count(in_range), Smallest = List.Min(in_range), Largest = List.Max(in_range)]
),
Expand = Table.ExpandRecordColumn(Result, "Record", {"Count", "Smallest", "Largest"}, {"Count", "Smallest", "Largest"})
in
Expand
``````

M-Code

``````let
Source = Table.SelectRows(Table.AddColumn(Table.FromValue({1..999999}),"Sum" ,each List.Sum(List.Transform(Text.ToList(Text.From([Value])),Number.From))),each [Sum]<10),
ANum = List.Buffer(List.Sort(List.RemoveNulls(List.Combine(Table.AddColumn(Source,"N",each List.Transform({1..6},(p)=> if p > Number.RoundDown(Number.Log10([Value])) then [Sum]*Number.Power(10,p)+[Value] else null))[N])))),
Comb = Table.FromRecords(Table.TransformRows(Excel.CurrentWorkbook(){[Name="Table1"]}[Content], each let L = [List.Select](http://list.select/)(ANum, (n)=> n>=[From] and n<=[To]) in [Count = List.Count(L),Smallest = L{0}, Largest = List.Last(L)]))
in
Comb
``````

Hii Vijay Sir,
could get this till row 5 due to sequence limitation, (looking forward to learn from others responses)
For now please see my submission:

``````=DROP(REDUCE("",SEQUENCE(ROWS(A2:B5)),LAMBDA(a,v,VSTACK(a,
LET(_num,SEQUENCE(INDEX(A2:B5,v,2)-INDEX(A2:B5,v,1)+1,,INDEX(A2:B5,v,1)),_left,LEFT(_num)+0,_right,RIGHT(_num,LEN(_num)-1),_rightsum,MAP(_right,LAMBDA(a,SUM(MID(a,SEQUENCE(LEN(a)),1)+0))),_cond,(_left=_rightsum)+0,
_cnt,SUM(_cond),_smallest,XLOOKUP(1,_cond,_num),_largest,XLOOKUP(1,_cond,_num,,,-1),HSTACK(_cnt,_smallest,_largest))))),1)
``````

` =REDUCE({"Count","Smallest","Largest"},MAP(A2:A6,B2:B6,LAMBDA(x,y,LET(m,SEQUENCE(1000000,10),n,--(IFERROR(MAP(m,LAMBDA(x,SUM(--MID(x,SEQUENCE(LEN(x)-1,,2),1)))),0)=--LEFT(m)),o,TOCOL(IF(n=1,m,m/0),3),p,(o>=x)*(o<=y)*o,q,IF(p=0,"",p),TEXTJOIN("-",1,COUNT(q),MIN(q),MAX(q))))),LAMBDA(a,b,VSTACK(a,--TEXTSPLIT(b,"-"))))`
`=LET(_d,A2:B5,fx,LAMBDA(r,LET(_n1,INDEX(r,1,1),_n2,INDEX(r,1,2),_s,SEQUENCE(_n2-_n1,,_n1),_m,MAP(--LEFT(_s),--RIGHT(_s,LEN(_s)-1),LAMBDA(a,b,a=SUM(--MID(b,SEQUENCE(LEN(b)),1)))),HSTACK(SUM(--_m),XLOOKUP(TRUE,_m,_s),XLOOKUP(TRUE,_m,_s,,,-1)))),REDUCE(fx(TAKE(_d,1)),DROP(SEQUENCE(ROWS(_d)),1),LAMBDA(a,b,VSTACK(a,fx(CHOOSEROWS(_d,b))))))`