Delete Projects rows where Root is closed

[raheem]

I have a long list of projects where each project root has multiple subprojects. How can I delete all those projects where complete status is Closed, skipping those projects where Root is still open irrespective of subprojects?

In the image, Project B and D need to be deleted only.

 

[Cubist]

One way is to use a helper column D to determine whether the row should be deleted. Apply filter on TRUE, select those rows and delete.

Book1
	A	B	C	D
1	Item	Project	Status	To Delete
2	Root	A	Open	FALSE
3		A1	Open	FALSE
4		A2	Open	FALSE
5		A3	Closed	FALSE
6		A4	Closed	FALSE
7	Root	B	Closed	TRUE
8		B1	Closed	TRUE
9		B1	Closed	TRUE
10		B1	Closed	TRUE
Sheet5

Cell Formulas
Range	Formula
D2:D10	D2	=LOOKUP(2,1/($A$2:A2<>""),$C$2:C2)="Closed"

 

[raheem]

One way is to use a helper column D to determine whether the row should be deleted. Apply filter on TRUE, select those rows and delete.

Book1
	A	B	C	D
1	Item	Project	Status	To Delete
2	Root	A	Open	FALSE
3		A1	Open	FALSE
4		A2	Open	FALSE
5		A3	Closed	FALSE
6		A4	Closed	FALSE
7	Root	B	Closed	TRUE
8		B1	Closed	TRUE
9		B1	Closed	TRUE
10		B1	Closed	TRUE
Sheet5

Cell Formulas
Range	Formula
D2:D10	D2	=LOOKUP(2,1/($A$2:A2<>""),$C$2:C2)="Closed"

Thank you very much. This solved the issue.

 

[raheem]

Thank you very much. This solved the issue.

Can you please also little bit explain the logic in this formula? Sorry for that. I am not very good in Lookup.

 

[Cubist]

It checks the last non-blank row in A as you drag down and returns the corresponding status in C. Essentially, it fills down the status of the Root and if it's "Closed" then returns TRUE.

 

[raheem]

It checks the last non-blank row in A as you drag down and returns the corresponding status in C. Essentially, it fills down the status of the Root and if it's "Closed" then returns TRUE.

Thank you very much