Parent Child Relation

Biz

Well-known Member
Joined
May 18, 2009
Messages
1,773
Office Version
  1. 2021
Platform
  1. Windows
Dear All,

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
ABCDEJ
1WBSDescriptionHieraracy/desire resultFormula Solution
255-10CivilsParentParentTRUEchild is in row 3, so it is parent
355-10-00Civils - Earthworks/Clearing & GrubbingParentParentTRUEit has childern from row 4 to row 7, so it is parent as well
455-40RMUParentParentTRUE
555-10-00Civils - Earthworks/Clearing & GrubbingParentChildFALSE
655-40-00RMU - MaterialsChildParentFALSE
755-10-00-03LandscapingChildParentFALSE
855-10-00-04Cut & FillChildChildTRUE
955-10-05Civils - RoadsChildChildTRUE
1055-10-15Civils - FoundationsParentParentTRUEchild is in row 10 to 13, so it is parent
1155-40-00RMU - MaterialsChildChildTRUE
1255-10-15-02BoPChildParentFALSE
1355-10-15-03Coupling TxChildChildTRUE
1455-10-15-04Civils - RMUChildChildTRUE
1555-10-20Civils - FencingChildParentFALSEit has no parent its considered alone child
1655-10-21Civils - GravelChildChildTRUEit has no parent its considered alone child
1755-40RMUParentChildFALSE
1855-40-00RMU - MaterialsChildChildTRUE
1955-40-05RMU - InstallParentChildFALSE
2055-40-05-LBRMU - LabourChildParentFALSE
2155-40-05-SCRMU - SubcontractChildParentFALSE
UnSort Data
Cell Formulas
RangeFormula
D2:D21D2=IF(A2="", "", IF(MAX(IFERROR(MATCH(LEFT(A2, LEN(A2)-1) & "*", A$2:A$1000, 0), 0), 0) >= ROW(A2)-1, "Parent", "Child"))
E2:E21E2=C2=D2


Your help would be greatly appreciated.

Kind Regards,

Biz
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
In row 10, your description is child is in row 10 to 13, so it is parent. Are you sure? It is its parent?
 
Upvote 0
Row 10 is the parent (55-10-15) and its children are in rows 12-14 (55-10-15-??). Hope this clarifies your query.
 
Upvote 0
I have a formula solution.
text.xlsx
ABCDEF
1WBSDescriptionHieraracy/desire resultFormula Solution
255-10CivilsParentparentTRUEchild is in row 3, so it is parent
355-10-00Civils - Earthworks/Clearing & GrubbingParentparentTRUEit has childern from row 4 to row 7, so it is parent as well
455-40RMUParentparentTRUE
555-10-00Civils - Earthworks/Clearing & GrubbingParentparentTRUE
655-40-00RMU - MaterialsChildchildTRUE
755-10-00-03LandscapingChildchildTRUE
855-10-00-04Cut & FillChildchildTRUE
955-10-05Civils - RoadsChildchildTRUE
1055-10-15Civils - FoundationsParentparentTRUEchild is in row 10 to 13, so it is parent
1155-40-00RMU - MaterialsChildchildTRUE
1255-10-15-02BoPChildchildTRUE
1355-10-15-03Coupling TxChildchildTRUE
1455-10-15-04Civils - RMUChildchildTRUE
1555-10-20Civils - FencingChildchildTRUEit has no parent its considered alone child
1655-10-21Civils - GravelChildchildTRUEit has no parent its considered alone child
1755-40RMUParentparentTRUE
1855-40-00RMU - MaterialsChildchildTRUE
1955-40-05RMU - InstallParentparentTRUE
2055-40-05-LBRMU - LabourChildchildTRUE
2155-40-05-SCRMU - SubcontractChildchildTRUE
Sheet3
Cell Formulas
RangeFormula
D2:D21D2=IF(COUNT(FIND(A2&"-",A2:A21)),"parent","child")
E2:E21E2=C2=D2
 
  • Like
Reactions: Biz
Upvote 0
Solution
Thank you very much for your help.
 
Upvote 0
Thank you very much for your help.
I don't know what is possible with your data, but for the slightly changed sample data below, doesn't that suggested formula in column D produce an incorrect result in row 9?

If my understanding is correct & the parent WBS needs to appear as the left part of the child WBS then perhaps the column E suggestion may ne more appropriate?

23 07 21.xlsm
ADE
1WBSFormula Solution
255-10parentparent
355-10-00parentparent
455-40parentparent
555-10-00parentparent
655-40-00childchild
755-10-00-03childchild
855-10-00-04childchild
910-15parentchild
1055-10-15parentparent
1155-40-00childchild
1255-10-15-02childchild
1355-10-15-03childchild
1455-10-15-04childchild
1555-10-20childchild
1655-10-21childchild
1755-40parentparent
1855-40-00childchild
1955-40-05parentparent
2055-40-05-LBchildchild
2155-40-05-SCchildchild
Parent Child
Cell Formulas
RangeFormula
D2:D21D2=IF(COUNT(FIND(A2&"-",A2:A21)),"parent","child")
E2:E21E2=IF(COUNT(MATCH(A2&"-*",A2:A21,0)),"parent","child")
 
Upvote 0
Hi Peter,

Thank you for highlighting this flaw.

Kind Regards,

Biz
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top