Page 1 of 1
excel query 5
Posted: Fri Nov 21, 2025 8:03 am
by sna
Dear all,
I need your input to build formula to arrange name by each department, dynamic array that produces output like in attach template
Thk
Re: excel query 5
Posted: Fri Nov 21, 2025 11:24 am
by snasui
Try this,
Code: Select all
=LET(
d,A3:A20,
sep,"===============",
curDept,SCAN("",d,LAMBDA(prev,x,IF(x=sep,"",IF(prev="",x,prev)))),
uniqDept,SORT(UNIQUE(FILTER(curDept,(curDept<>"")*(curDept<>sep)))),
listDept,LAMBDA(de,SORT(FILTER(d,(curDept=de)*(d<>sep)*(d<>de)))),
maxRows,1+MAX(BYROW(uniqDept,LAMBDA(x,ROWS(listDept(x))))),
MAKEARRAY(
maxRows,ROWS(uniqDept),
LAMBDA(r,c,
IF(r=1,
INDEX(uniqDept,c),
IFERROR(INDEX(listDept(INDEX(uniqDept,c)),r-1),"")
)
)
)
)
Re: excel query 5
Posted: Sat Nov 22, 2025 7:41 am
by sna
Thanks