Dear All,
The desired solution is in column C. In column D, I need a formula-based solution.
Unfortunately, my formula is not working.
Your help would be greatly appreciated.
Kind Regards,
Biz
The desired solution is in column C. In column D, I need a formula-based solution.
Unfortunately, my formula is not working.
Identify child and parent (Awesome).xlsm | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | J | |||||||
1 | WBS | Description | Hieraracy/desire result | Formula Solution | ||||||||
2 | 55-10 | Civils | Parent | Parent | TRUE | child is in row 3, so it is parent | ||||||
3 | 55-10-00 | Civils - Earthworks/Clearing & Grubbing | Parent | Parent | TRUE | it has childern from row 4 to row 7, so it is parent as well | ||||||
4 | 55-40 | RMU | Parent | Parent | TRUE | |||||||
5 | 55-10-00 | Civils - Earthworks/Clearing & Grubbing | Parent | Child | FALSE | |||||||
6 | 55-40-00 | RMU - Materials | Child | Parent | FALSE | |||||||
7 | 55-10-00-03 | Landscaping | Child | Parent | FALSE | |||||||
8 | 55-10-00-04 | Cut & Fill | Child | Child | TRUE | |||||||
9 | 55-10-05 | Civils - Roads | Child | Child | TRUE | |||||||
10 | 55-10-15 | Civils - Foundations | Parent | Parent | TRUE | child is in row 10 to 13, so it is parent | ||||||
11 | 55-40-00 | RMU - Materials | Child | Child | TRUE | |||||||
12 | 55-10-15-02 | BoP | Child | Parent | FALSE | |||||||
13 | 55-10-15-03 | Coupling Tx | Child | Child | TRUE | |||||||
14 | 55-10-15-04 | Civils - RMU | Child | Child | TRUE | |||||||
15 | 55-10-20 | Civils - Fencing | Child | Parent | FALSE | it has no parent its considered alone child | ||||||
16 | 55-10-21 | Civils - Gravel | Child | Child | TRUE | it has no parent its considered alone child | ||||||
17 | 55-40 | RMU | Parent | Child | FALSE | |||||||
18 | 55-40-00 | RMU - Materials | Child | Child | TRUE | |||||||
19 | 55-40-05 | RMU - Install | Parent | Child | FALSE | |||||||
20 | 55-40-05-LB | RMU - Labour | Child | Parent | FALSE | |||||||
21 | 55-40-05-SC | RMU - Subcontract | Child | Parent | FALSE | |||||||
UnSort Data |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D2:D21 | D2 | =IF(A2="", "", IF(MAX(IFERROR(MATCH(LEFT(A2, LEN(A2)-1) & "*", A$2:A$1000, 0), 0), 0) >= ROW(A2)-1, "Parent", "Child")) |
E2:E21 | E2 | =C2=D2 |
Your help would be greatly appreciated.
Kind Regards,
Biz