CODEWITHSUNDEEP
excelexcel-formula
Frederico Eglesias
Frederico Eglesias

Reputation: 55

How to find Child ID?

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:

enter image description here

For example:

  1. A01 |1| | | | 2 |
  2. A0101 |2| |1| | 3 |
  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;"")))

Upvotes: 0

Views: 91

Answers (1)

JvdV
JvdV

Reputation: 75840

Assuming this is related to this previous question, you can simply add the 3rd column as a 3rd parameter in the HSTACK() function:

enter image description here

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:

enter image description here

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,""))))))

Upvotes: 2

Related Questions