match in grandchildren

Maxime Schmidt

Hello all,

 

I have a problem to which I can't find a solution. I hope you will be able to solve it.We have a store page where we collect data from parts from different departments. We then assign those parts to a project using a parent/child relationship. On the parent line, we use formulas like this one:  =IF(AND(OR($[Engine type]378 = "CF34-3B"; $[Engine type]378 = "CF34-8C"); $Description378 = "Engine"); IF(ISERROR(MATCH("Brown Harness"; CHILDREN($Description378); 0)); "- Brown Harness"; ""); "")

The important part here is this one: IF(ISERROR(MATCH("Brown Harness"; CHILDREN($Description378); 0)); "- Brown Harness"; ""). This part is looking in the children's description and report a missing part (here the brown harness). 

I have 1 column for each part and I am able to send the missing parts to the main column and have a list of all missing. This is working great so far.

The problem here is that when I have multiple tier. Here I could assign the brown harness to a kit and the kit to the main project. I now have parent/child/grandchild. The match can only search in the children and the grandchild is lost.

Do you know a way to be able to search in the grandchild tier? I have tried things like match(children(children())) but I received an error since the 2nd children send an array instead of a cell to the first one.

Thanks for your time and have a great day.

Maxime

Paul Newcome

Have you tried incorporating a COLLECT function coupled with a COUNT(PARENT()) = 2? The COUNT(PARENT()) would show 0 for parent rows, 1 for children rows, 2 for grandchildren rows, 3 for great grandchildren rows, etc...