Split the numbers into two parts where sum of first part is > sum of second part where it happens for the first time. Hence, not all combinations need to be listed. What we need to list is when it happens for the first time when we start from left.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 378
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Split on First Greater Sum with Power Query
Power Query solution 1 for Split on First Greater Sum, proposed by John V.:
let
S = Excel.CurrentWorkbook(){0}[Content],
L = List.Transform, K = List.Skip,
R = Table.AddColumn(S, "R", each
let
a = Text.Split([Numbers], ", "),
b = K(List.Accumulate(a, {0}, (s, c) => s & {List.Last(s) + Number.From(c)})),
c = 1 + List.PositionOf(L(b, each 2 * _ > List.Last(b)), true),
d = L({List.FirstN(a, c), K(a, c)}, each {Text.Combine(_, ", ")})
in
Table.FromColumns(d)
)[R]
in
Table.Combine(R)
Blessings!
Power Query solution 2 for Split on First Greater Sum, proposed by John V.:
let
S = Excel.CurrentWorkbook(){0}[Content],
L = List.Transform,
R = Table.AddColumn(
S,
"R",
each
let
a = Text.Split([Numbers], ", "),
b = L(a, each Number.From(_)),
c = List.Generate(
() => [i = 0, s = 0],
each 2 * [s] <= List.Sum(b) or [i] = 0,
each [i = [i] + 1, s = [s] + b{[i]}],
each 1 + [i]
),
d = List.Last(c),
e = L({List.FirstN(a, d), List.Skip(a, d)}, each {Text.Combine(_, ", ")})
in
Table.FromColumns(e)
)[R]
in
Table.Combine(R)Power Query solution 3 for Split on First Greater Sum, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.Combine(
Table.AddColumn(
Source,
"A",
each
let
a = Text.Split([Numbers], ", "),
b = List.Transform(
{1 .. List.Count(a)},
each {
Text.Combine(List.FirstN(a, _), ", "),
Text.Combine(List.LastN(a, List.Count(a) - _), ", ")
}
),
c = List.Transform(
{1 .. List.Count(a)},
each List.Sum(List.Transform(List.FirstN(a, _), Number.From))
- List.Sum(List.Transform(List.LastN(a, List.Count(a) - _), Number.From))
),
d = List.PositionOf(c, List.Select(c, each _ > 0){0}),
e = Table.FromRows({b{d}})
in
e
)[A]
)
in
SolPower Query solution 4 for Split on First Greater Sum, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.AddColumn(
Fonte,
"Personalizar",
each [
o = Table.Combine(
[
a = Text.Split([Numbers], ", "),
b = List.Transform(
{1 .. List.Count(a)},
each Table.FromRows(
{
{Number.Abs(List.Sum(List.FirstN(List.Transform(a, Number.From), _)))}
& {
Text.Combine(
List.Transform(List.FirstN(List.Transform(a, Number.From), _), Text.From),
","
)
}
& {Number.Abs(List.Sum(List.LastN(List.Transform(a, Number.From), _)))}
& {
Text.Combine(
List.Transform(List.LastN(List.Transform(a, Number.From), _), Text.From),
","
)
}
}
)
)
][b]
),
max = List.Last(o[Column1] & o[Column3]),
lst = Table.Combine(
List.Transform(
o[Column1],
each Table.AddColumn(
Table.FromColumns({List.Repeat({_}, List.Count(o[Column3]))} & {o[Column3]}),
"soma",
each [Column1] + [Column2]
)
)
),
sum = Table.SelectRows(lst, each [soma] = max and [Column1] > [Column2]){0}? ?? null,
vl1 = try
Table.First(Table.SelectRows(o[[Column1], [Column2]], each [Column1] = sum[Column1]))[
Column2
]
otherwise
null,
vl2 = try
Table.Last(Table.SelectRows(o[[Column3], [Column4]], each [Column3] = sum[Column2]) ?? null)[
Column4
]
otherwise
null
][[vl1], [vl2]]
),
exp = Table.ExpandRecordColumn(res, "Personalizar", {"vl1", "vl2"})
in
expPower Query solution 5 for Split on First Greater Sum, proposed by Ramiro Ayala Chávez:
let
Origen = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Fx = (x) =>
let
a = Text.Split(x, ", "),
b = List.Generate(
() => [i = 0, j = 1],
each [j] <= List.Count(a),
each [i = [i], j = [j] + 1],
each List.Range(a, 0, [j] - [i])
),
c = List.Repeat({a}, List.Count(a)),
d = List.Transform({0 .. List.Count(a) - 1}, each List.Difference(c{_}, b{_})),
e = List.Transform(b, each List.Sum(List.Transform(_, Number.From))),
f = List.Transform(d, each List.Sum(List.Transform(_, Number.From))),
g = List.Zip({List.Positions(e), e, f}),
h = List.First(List.Select(g, each _{1} > _{2})){0},
i = List.Skip(List.Zip({List.Positions(b), b, d}){h}),
j = List.Transform(i, each Text.Combine(_, ", "))
in
j,
k = Table.AddColumn(Origen, "P1", each Fx([Numbers]){0}),
Sol = Table.AddColumn(k, "P2", each Fx([Numbers]){1})
in
SolPower Query solution 6 for Split on First Greater Sum, proposed by Rafael González B.:
let
Source = Excel.CurrentWorkbook(){0}[Content],
TAC = Table.AddColumn(
Source,
"Values",
each
let
T = [Numbers],
TS = Text.Split(T, ", "),
LT = List.Transform(TS, each Number.From(_)),
LC = List.Count(LT),
LG = List.Generate(
() => [i = 0, P = 0],
each [i] <= LC,
each [
i = [i] + 1,
P = [P] + 1,
LH = List.Range(LT, 0, i),
RH = List.Range(LT, P, LC - i),
Test = List.Sum(LH) > List.Sum(RH)
]
),
TFR = Table.FromRecords(List.Skip(LG))[[LH], [RH], [Test]],
FN = Table.First(Table.SelectRows(TFR, each ([Test] = true))),
RT = Record.TransformFields(
FN,
{
{"LH", (x) => Text.Combine(List.Transform(x, each Text.From(_)), ", ")},
{"RH", (y) => Text.Combine(List.Transform(y, each Text.From(_)), ", ")}
}
),
RR = {Record.RemoveFields(RT, "Test")},
RTT = Table.FromRecords(RR)
in
RTT
)[Values]
in
Table.Combine(TAC)Power Query solution 7 for Split on First Greater Sum, proposed by Mihai Radu O:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
sol = Table.AddColumn(
Source,
"R",
each
let
a = List.Transform(Text.Split([Numbers], ", "), Number.From),
b = List.Transform(
{1 .. List.Count(a)},
each List.Sum(List.FirstN(a, _)) > List.Sum(a) - List.Sum(List.FirstN(a, _))
),
c = List.PositionOf(b, true, Occurrence.First),
d = {Text.BeforeDelimiter([Numbers], ",", c), Text.AfterDelimiter([Numbers], ",", c)}
in
d
)[R]
in
Table.FromRows(sol)Power Query solution 8 for Split on First Greater Sum, proposed by Glyn Willis:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Numbers", type text}, {"Answer Expected", type any}, {"Column1", type text}}
),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"Custom",
each [
l = List.Transform(Text.Split([Numbers], ", "), Int64.From),
s = [
i = 1,
c = List.Count(l),
lft = List.FirstN(l, i),
rgt = List.LastN(l, c - i),
x = List.Sum(lft),
y = List.Sum(rgt)
],
lg = Table.SelectRows(
Table.FromRecords(
List.Generate(
() => s,
each [i] <= [c],
each [
i = [i] + 1,
c = [c],
lft = List.FirstN(l, i),
rgt = List.LastN(l, c - i),
x = List.Sum(lft),
y = List.Sum(rgt)
],
each [sum = [x], lft = [lft], rgt = [rgt], f = [x] > [y]]
),
type table [sum = number, lft = list, rgt = list, f = logical]
),
each [f]
){0}[[lft], [rgt]]
][lg]
),
#"Expanded Custom" = Table.ExpandRecordColumn(
#"Added Custom",
"Custom",
{"lft", "rgt"},
{"lft", "rgt"}
),
#"Extracted Values" = Table.TransformColumns(
#"Expanded Custom",
{
{"lft", each Text.Combine(List.Transform(_, Text.From), ", "), type text},
{"rgt", each Text.Combine(List.Transform(_, Text.From), ", "), type text}
}
)
in
#"Extracted Values"Solving the challenge of Split on First Greater Sum with Excel
Excel solution 1 for Split on First Greater Sum, proposed by Rick Rothstein:
=TEXTSPLIT(
TEXTJOIN(
"*",
,
MAP(
A2:A10,
LAMBDA(
a,
LET(
n,
TEXTSPLIT(
a,
","
),
t,
SUM(
0+n
),
SUBSTITUTE(
a,
",",
"|",
MIN(
SCAN(
0,
SEQUENCE(
COUNT(
-n
)
),
LAMBDA(
a,
x,
LET(
s,
SUM(
0+INDEX(
n,
,
SEQUENCE(
,
x
)
)
),
IF(
s>t-s,
x
)
)
)
)
)
)
)
)
)
),
"|",
"*"
)Excel solution 2 for Split on First Greater Sum, proposed by John V.:
=TEXTSPLIT(
CONCAT(
MAP(
A2:A10,
LAMBDA(
x,
LET(
n,
-TEXTSPLIT(
x,
","
),
SUBSTITUTE(
x,
", ",
";",
XMATCH(
TRUE,
2*SCAN(
,
n,
SUM
)Excel solution 3 for Split on First Greater Sum, proposed by محمد حلمي:
=LET(a,A2:A10,j,MAP(a,LAMBDA(c,LET(d,--TEXTSPLIT(c,,","),x,XMATCH(TRUE,SCAN(,d,
LAMBDA(a,d,a+d))>MAP(SEQUENCE(20),LAMBDA(a,
SUM(DROP(d,a))))),SUM(x*2,LEN(TAKE(d,x)))))),
HSTACK(LEFT(a,j-2),MID(a,j+1,99)))Excel solution 4 for Split on First Greater Sum, proposed by محمد حلمي:
=TEXTSPLIT(
TEXTJOIN(
"/",
,
MAP(
A2:A10,
LAMBDA(
i,
LET(
n,
--TEXTSPLIT(
i,
", "
),
SUBSTITUTE(
i,
", ",
"|",
XMATCH(
TRUE,
SCAN(
,
n,
LAMBDA(
a,
d,
a+d
)
)>
SUM(
n
)/2
)
)
)
)
)
),
"|",
"/"
)Excel solution 5 for Split on First Greater Sum, proposed by Kris Jaganah:
=DROP(
REDUCE(
"",
A2:A10,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
a,
--TEXTSPLIT(
y,
,
", "
),
b,
SCAN(
,
a,
SUM
),
c,
SEQUENCE(
ROWS(
a
)
),
d,
BYROW(
XLOOKUP(
c+TOROW(
c
)-1,
c,
a,
0
),
SUM
),
e,
FILTER(
a,
bSUM(
DROP(
a,
f
)
),
e,
TAKE(
a,
f+1
)
),
""
),
h,
TAKE(
a,
ROWS(
g
)-ROWS(
a
)
),
i,
ARRAYTOTEXT(
g
),
HSTACK(
i,
IF(
i="",
"",
ARRAYTOTEXT(
h
)
)
)
)
)
)
),
1
) =DROP(
REDUCE(
"",
A2:A10,
LAMBDA(
A,
B,
VSTACK(
A,
LET(
SP,
--TEXTSPLIT(
B,
,
","
),
L,
DROP(
MAP(
SEQUENCE(
ROWS(
SP
)
),
LAMBDA(
A,
SUM(
TAKE(
SP,
A
)
)
)
),
-1
)>DROP(
MAP(
SEQUENCE(
ROWS(
SP
)
),
LAMBDA(
A,
SUM(
DROP(
SP,
A
)
)
)
),
-1
),
X,
XMATCH(
TRUE,
L
),
HSTACK(
TEXTJOIN(
", ",
,
TAKE(
SP,
X
)
),
TEXTJOIN(
", ",
,
DROP(
SP,
X
)
)
)
)
)
)
),
1
)Excel solution 7 for Split on First Greater Sum, proposed by Timothée BLIOT:
=DROP(
REDUCE(
"",
A2:A10,
LAMBDA(
w,
v,
LET(
A,
--TEXTSPLIT(
v,
,
", "
),
B,
--MAP(
SEQUENCE(
ROWS(
A
)
),
LAMBDA(
x,
SUM(
TAKE(
A,
x
)
)>SUM(
A
)-SUM(
TAKE(
A,
x
)
)
)
),
VSTACK(
w,
HSTACK(
ARRAYTOTEXT(
TAKE(
A,
XMATCH(
1,
B
)
)
),
ARRAYTOTEXT(
TAKE(
A,
-ROWS(
A
)+XMATCH(
1,
B
)
)
)
)
)
)
)
),
1
)Excel solution 8 for Split on First Greater Sum, proposed by Hussein SATOUR:
=TEXTSPLIT(
CONCAT(
MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
--TEXTSPLIT(
x,
", "
),
b,
SCAN(
0,
a,
SUM
),
SUBSTITUTE(
x,
", ",
"/",
XMATCH(
TRUE,
b > SUM(
a
)-b
)
)&"|"
)
)
)
),
"/",
"|",
1
)Excel solution 9 for Split on First Greater Sum, proposed by 🇵🇪 Ned Navarrete C.:
=LET(
h,
MAP(
A2:A10,
LAMBDA(
l,
LET(
n,
--TEXTSPLIT(
l,
,
", "
),
m,
ROWS(
n
),
s,
SEQUENCE(
m-1
),
t,
MAP(
s,
LAMBDA(
f,
LET(
e,
LAMBDA(
x,
TAKE(
n,
x
)
),
a,
e(
f
),
b,
e(
-m+f
),
IF(
SUM(
a
)>SUM(
b
),
ARRAYTOTEXT(
a
)&"*"&ARRAYTOTEXT(
b
),
1/0
)
)
)
),
@TOCOL(
t,
3
)
)
)
),
HSTACK(
TEXTBEFORE(
h,
"*"
),
TEXTAFTER(
h,
"*"
)
)
)Excel solution 10 for Split on First Greater Sum, proposed by Pieter de Bruijn:
=DROP(
REDUCE(
"",
A2:A10,
LAMBDA(
x,
y,
LET(
t,
--TEXTSPLIT(
y,
,
","
),
r,
ROWS(
t
),
VSTACK(
x,
IFNA(
REDUCE(
NA(),
SEQUENCE(
r-1
),
LAMBDA(
n,
z,
LET(
s,
INDEX(
t,
SEQUENCE(
z
)
),
e,
INDEX(
t,
SEQUENCE(
r-z,
,
z+1
)
),
IF(
ISNA(
n
),
IFS(
SUM(
s
)>SUM(
e
),
HSTACK(
ARRAYTOTEXT(
s
),
ARRAYTOTEXT(
e
)
)
),
n
)
)
)
),
{"",
""}
)
)
)
)
),
1
)Excel solution 11 for Split on First Greater Sum, proposed by Giorgi Goderdzishvili:
=TEXTSPLIT(TEXTJOIN("|",
,
MAP(A2:A10,
LAMBDA(x,
LET(
_nm,
x,
_sp,
1*TEXTSPLIT(
_nm,
", "
),
_mp,
MAP(SEQUENCE(
,
COLUMNS(
_sp
)
),
LAMBDA(x,
IF(SUM(
TAKE(
_sp,
,
x
)
)>SUM(TAKE(_sp,
,
-(COLUMNS(
_sp
)-x))),
1,
0))),
_in,
XMATCH(
1,
_mp,
0
),
_fn,
ARRAYTOTEXT(
TAKE(
_sp,
,
_in
)
)&"@"&ARRAYTOTEXT(TAKE(_sp,
,
-(COLUMNS(
_sp
)-_in))),
_fn)))),
"@",
"|")Excel solution 12 for Split on First Greater Sum, proposed by Edwin Tisnado:
=DROP(REDUCE(0,
A2:A10,
LAMBDA(y,
x,
LET(n,
--TEXTSPLIT(
x,
", "
),
j,
MATCH(1,
--(2*SCAN(
,
n,
LAMBDA(
x,
y,
x+y
)
)>SUM(
n
)),
0),
VSTACK(
y,
HSTACK(
TEXTBEFORE(
x,
",",
j
),
TEXTAFTER(
x,
" ",
j
)
)
)))),
1)Excel solution 13 for Split on First Greater Sum, proposed by Anup Kumar:
=LET(
ns,
--TEXTSPLIT(
A2,
,
", "
),
cn,
ROWS(
ns
),
rv,
SORTBY(
ns,
SEQUENCE(
cn
),
-1
),
di,
SCAN(
0,
SEQUENCE(
cn-1
),
LAMBDA(
x,
y,
SUM(
TAKE(
ns,
y
)
)>SUM(
TAKE(
rv,
cn-y
)
)
)
),
in,
IF(
AND(
di
),
0,
XMATCH(
TRUE,
di
)
),
IF(
in<>0,
HSTACK(
ARRAYTOTEXT(
TAKE(
ns,
in
)
),
ARRAYTOTEXT(
DROP(
ns,
in
)
)
),
""
)
)Excel solution 14 for Split on First Greater Sum, proposed by Diarmuid Early:
=LET(
mp,
MAP(
A2:A10,
LAMBDA(
input,
LET(
nums,
--TEXTSPLIT(
input,
", "
),
pos,
XMATCH(
TRUE,
SCAN(
0,
nums,
SUM
)>SUM(
nums
)/2
),
SUBSTITUTE(
input,
", ",
"|",
pos
)
)
)
),
TEXTSPLIT(
TEXTJOIN(
"/",
,
mp
),
"|",
"/"
)
)
The inner LAMBDA finds which number input first meets the condition,
then replaces the delimiter ", " in that position with a "|".
MAP applies that to all the inputs.
Then TEXTJOIN combines that into one string with another delimiter ("/"),
and then TEXTSPLIT splits on the | and / delimiters into the desired output.
This is all a big messy workaround for the fact that MAP and TEXTSPLIT both don't like arrays of arrays (so MAP can only output one cell per input where we want two here,
and TEXTSPLIT applied to an array will only give the first part of each one instead of all of them)Excel solution 15 for Split on First Greater Sum, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=IFNA(REDUCE(B1,
A2:A10,
LAMBDA(a,
z,
VSTACK(a,
LET(n,
--TEXTSPLIT(
z,
,
", "
),
l,
ROWS(
n
),
s,
SEQUENCE(
l-1
),
f,
LAMBDA(
x,
ABS(
SUM(
TAKE(
n,
x
)
)
)
),
d,
@(FILTER(
s,
MAP(
s,
f
)>MAP(
s-l,
f
),
0
)),
g,
LAMBDA(
y,
ARRAYTOTEXT(
TAKE(
n,
y
)
)
),
IF(
d,
HSTACK(
g(
d
),
g(
d-l
)
),
""
))))),
"")Excel solution 16 for Split on First Greater Sum, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=LET(
nu,
A2:A10,
d,
MAP(
nu,
LAMBDA(
b,
LET(
n,
--TEXTSPLIT(
b,
,
", "
),
l,
ROWS(
n
),
s,
SEQUENCE(
l
),
f,
SCAN(
,
n,
SUM
),
@FILTER(
s,
f>ABS(
SUM(
n
)-f
),
0
)
)
)
),
IF(
{1,
0},
IF(
d,
TEXTBEFORE(
nu,
",",
d
),
""
),
IF(
d,
TEXTAFTER(
nu,
",",
d
),
""
)
)
)Excel solution 17 for Split on First Greater Sum, proposed by Arden Nguyen, CPA:
=LET(
a,
A2:A10,
b,
MAP(
a,
LAMBDA(
x,
LET(
L,
COUNTA(
TEXTSPLIT(
x,
", "
)
),
f,
LAMBDA(
_a,
_b,
_s,
IF(
_b < L,
LET(
_x,
& TEXTSPLIT(
TEXTBEFORE(
_a,
", ",
_b
),
", "
),
_y,
TEXTSPLIT(
TEXTAFTER(
_a,
", ",
_b
),
", "
),
cond,
SUM(
_x + 0
) > SUM(
_y + 0
),
IF(
cond,
_b,
_s(
_a,
_b + 1,
_s
)
)
),
0
)
),
f(
x,
1,
f
)
)
)
),
HSTACK(
IFERROR(
TEXTBEFORE(
a,
", ",
b
),
""
),
IFERROR(
TEXTAFTER(
a,
", ",
b
),
""
)
)
)Solving the challenge of Split on First Greater Sum with Python
Python solution 1 for Split on First Greater Sum, proposed by Giorgi Goderdzishvili:
for i in lst:
for k in range(len(i)):
if sum(i[:k+1])>sum(i[k+1:]):
print(i[:k+1],i[k+1:])
break
Solving the challenge of Split on First Greater Sum with R
R solution 1 for Split on First Greater Sum, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
janitor::clean_names() %>%
select(cut_1test = 1, cut_2test = 2)
find_cut_point = function(n_vec) {
vec = str_split(n_vec, ", ")[[1]] %>% as.numeric()
n = length(vec)
for (i in 1:n) {
if (sum(vec[1:i]) > sum(vec[(i+1):n])) {
return(i)
}
}
}
cut_vector = function(n_vec) {
vec = str_split(n_vec, ", ")[[1]] %>% as.numeric()
cut_point = find_cut_point(n_vec)
p1 = vec[1:cut_point] %>% str_c(collapse = ", ")
p2 = vec[(cut_point+1):length(vec)] %>% str_c(collapse = ", ")
return(list(p1, p2))
}
result = input %>%
mutate(cut = map(Numbers, cut_vector)) %>%
unnest_wider(cut, names_sep = "_") %>%
bind_cols(test) %>%
mutate(check_cut1 = cut_1 == cut_1test,
check_cut2 = cut_2 == cut_2test)
&