How to find all entries of a specific value based on a filter-CodePudding

So i'm running into an issue with using filters and formula's at the same time.

I have the following scenario:

ID  Value1  Value2  Value3
1   Illble  No  Illble
1   Illble  No  Illble
1   Illble  No  Illble
1   Illble  Yes Illble
2   Illble  No  Illble
2   Illble  Yes Illble
2   Illble  No  Illble
3   Illble  No  Illble
3   Illble  No  Illble
4   Illble  Yes Illble
4   Illble  No  Illble
4   Illble  No  Illble
4   Illble  No  Illble
4   Illble  No  Illble
4   Illble  No  Illble
5   Illble  No  Illble
5   Illble  No  Illble

(Added the table as code sample if the image doesn't load)

I'm currently using a formula to highlight the row where the value in the column marked "Value2" equals "Yes".

=$C2="Yes"

I want to now remove all rows with the same ID if one of the ID's has been highlighted. In other words, if an ID has any row where the value in the column "Value2" equals "Yes" I want to remove all rows with that ID.

I've tried filtering with background color, but I can't figure out how to get the other entries with the same ID.

CodePudding user response：

Create a CF rule that counts if there is any row with same ID and value Yes in column Value2 with COUNTIF. If the count is greater than 0, highlight that row. Those rows are the ones to be deleted.

=COUNTIFS($A$2:$A$18,$A2,$C$2:$C$18,"Yes")>0

Yellow rows would be the ones to be deleted.

COUNTIFS function