Number hierarchy

[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

[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

...

[sna]

thanks