Number hierarchy
Posted: Sun Jul 28, 2024 11:00 am
by sna
Hi there,
I need formula for excel 365 to assign hierarchy numbers base on data in table and provide results like columns highlighted.
Thanks
Re: Number hierarchy
Posted: Sun Jul 28, 2024 5:00 pm
by norkaz
...
Hi
For a Parent/Child hierarchy with duplicate employee names, such as ‘Thomas’ in your Excel sample file, it would create a logical loop in the hierarchy.
To avoid logical loops in the hierarchy, it would be better to eliminate duplicate items in the collection based on the parent-child relationship sequence.
E2
=HSTACK(SCAN(E1,XMATCH(A2:A18,TOCOL(A2:A18,1)),LAMBDA(x,y,IFERROR(y,x)))+IFERROR(INDEX(SEQUENCE(COUNTA(FILTER(SCAN(A1,A2:A18,LAMBDA(x,y,IF(y="",x,y))),B2:B18<>"")),,2)-XMATCH(FILTER(SCAN(A1,A2:A18,LAMBDA(x,y,IF(y="",x,y))),B2:B18<>""),FILTER(SCAN(A1,A2:A18,LAMBDA(x,y,IF(y="",x,y))),B2:B18<>"")),XMATCH(SCAN(A1,A2:A18,LAMBDA(x,y,IF(y="",x,y)))&SCAN(B1,B2:B18,LAMBDA(x,y,IF(y="",x,y))),FILTER(SCAN(A1,A2:A18,LAMBDA(x,y,IF(y="",x,y))),B2:B18<>"")&FILTER(SCAN(B1,B2:B18,LAMBDA(x,y,IF(y="",x,y))),B2:B18<>"")))/10,0)&IF(C2:C18<>"","."&SEQUENCE(COUNT(ROW(A2:A18)),,2)-XMATCH(SCAN(B1,B2:B18,LAMBDA(x,y,IF(y="",x,y))),SCAN(B1,B2:B18,LAMBDA(x,y,IF(y="",x,y))))-1,""),TOCOL(A2:C18,3))
Bigcat9Excel
Bangkok Thailand
...
Re: Number hierarchy
Posted: Mon Jul 29, 2024 2:56 pm
by sna
thanks