Page 1 of 1
Excel query 3
Posted: Sun Nov 16, 2025 4:05 pm
by sna
Dear all,
I need your input to find total by group whose total nearest
The data in column A,B n answer in the next
Pls find the attached
Thanks
Re: Excel query 3
Posted: Mon Nov 17, 2025 12:09 am
by snasui
Try this,
Code: Select all
=LET(
d,A2:B16,
cats,UNIQUE(INDEX(d,,1)),
sums,SUMIFS(INDEX(d,,2),INDEX(d,,1),cats),
MAKEARRAY(ROWS(cats),4,
LAMBDA(r,c,
LET(
cat,INDEX(cats,r),
sum,INDEX(sums,r),
others,FILTER(cats,cats<>cat),
osums,SUMIFS(INDEX(d,,2),INDEX(d,,1),others),
diffs,ABS(osums-sum),
match,FILTER(others,diffs=MIN(diffs)),
pairCat,TEXTJOIN(", ",,match),
pairSum,INDEX(FILTER(osums,diffs=MIN(diffs)),1),
CHOOSE(c,cat,sum,pairCat,pairSum)
)
)
)
)
Re: Excel query 3
Posted: Tue Nov 18, 2025 8:00 am
by sna
Thanks