Parent Child Relation

[Biz]

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
	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

 

[keda duck]

In row 10, your description is child is in row 10 to 13, so it is parent. Are you sure? It is its parent?

 

[Biz]

Row 10 is the parent (55-10-15) and its children are in rows 12-14 (55-10-15-??). Hope this clarifies your query.

 

[keda duck]

I have a formula solution.

text.xlsx
	A	B	C	D	E	F
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	parent	TRUE
6	55-40-00	RMU - Materials	Child	child	TRUE
7	55-10-00-03	Landscaping	Child	child	TRUE
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	child	TRUE
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	child	TRUE	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	parent	TRUE
18	55-40-00	RMU - Materials	Child	child	TRUE
19	55-40-05	RMU - Install	Parent	parent	TRUE
20	55-40-05-LB	RMU - Labour	Child	child	TRUE
21	55-40-05-SC	RMU - Subcontract	Child	child	TRUE
Sheet3

Cell Formulas
Range	Formula
D2:D21	D2	=IF(COUNT(FIND(A2&"-",A2:A21)),"parent","child")
E2:E21	E2	=C2=D2

 

[Biz]

Thank you very much for your help.

 

[Peter_SSs]

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
	A			D	E
1	WBS			Formula Solution
2	55-10			parent	parent
3	55-10-00			parent	parent
4	55-40			parent	parent
5	55-10-00			parent	parent
6	55-40-00			child	child
7	55-10-00-03			child	child
8	55-10-00-04			child	child
9	10-15			parent	child
10	55-10-15			parent	parent
11	55-40-00			child	child
12	55-10-15-02			child	child
13	55-10-15-03			child	child
14	55-10-15-04			child	child
15	55-10-20			child	child
16	55-10-21			child	child
17	55-40			parent	parent
18	55-40-00			child	child
19	55-40-05			parent	parent
20	55-40-05-LB			child	child
21	55-40-05-SC			child	child
Parent Child

Cell Formulas
Range	Formula
D2:D21	D2	=IF(COUNT(FIND(A2&"-",A2:A21)),"parent","child")
E2:E21	E2	=IF(COUNT(MATCH(A2&"-*",A2:A21,0)),"parent","child")

 

[Biz]

Hi Peter,

Thank you for highlighting this flaw.

Kind Regards,

Biz

 

[Peter_SSs]

You're welcome.