I have a List with codes, IDs, Parent IDs and would like also to get Child IDs, but not sure how to do this.
The screenshot is below:
For example:
- |ID| |PARENT ID| |CHILD ID|
- A01 |1| | | | 2 |
- A0101 |2| |1| | 3 |
- A010101 |3| |2| | |
I have a formula, which is calculating Parent ID, but I have no idea how to do it for Child ID.
Formula: =LET(x;A2:A251;y;SEQUENCE(ROWS(x));HSTACK(y;XLOOKUP(LEFT(x;LEN(x)-2);x;y;"")))
CodePudding user response：
Assuming this is related to 
Formula in B2:
=LET(x,A2:A13,y,SEQUENCE(ROWS(x)),HSTACK(y,XLOOKUP(LEFT(x,LEN(x)-2),x,y,""),XLOOKUP(x&"??",x,y,"",2)))
Here XLOOKUP(x&"??",x,y,"",2) will look up the ID for the 1st possible child and would leave it blank if no child is found. It's a wildcard search, hence the use of '??' to mimic any two characters.
If you wish to display all direct descendents, try:
Formula in B2:
=LET(x,A2:A13,y,SEQUENCE(ROWS(x)),HSTACK(y,XLOOKUP(LEFT(x,LEN(x)-2),x,y,""),MAP(x,LAMBDA(z,TEXTJOIN(",",,FILTER(y,LEFT(x,LEN(x)-2)=z,""))))))