Keep or eliminate duplicates in a column based on data in another column

Question

I have data that looks like the chart at bottom.  In the ‘Status’ column every row will be populated with a string that contains either ‘YES’ or ‘NO’ embedded within in it.

Every user in the list can have from 0 to 10+ instances of both ‘YES’ and ‘NO’ status, but all with have at least one.

I need to filter my data so that:

* For any name that has only ‘NO’ statuses, I need to keep every instance of that name for later review
* Any name that has both a ‘YES’ and a ‘NO’ status, I can eliminate all the ‘NO’ but must keep every instance with ‘YES’ for later review
* Any name that has only a ‘YES’ I need to keep every instance for later review

I’ve started with the Summary tool doing a count of user names and keeping every name that only appears once, but from there I’m struggling with how to eliminate the ones I don’t need without also removing the ones I do.  Appreciate any help, thanks.

NameStatusJonesxxxYESxxzxzxJoneszxzxzxNOxzzzxxxSmithxxxYESxxxzzzSmithxxxzxxxxzxNOzzxzxzzzxzxzJohnsonzzzxzzzzxzNOzzxzxzzzxzxzJohnsonxxxzxYESzzxzxzJohnsonzzxzxzYESzzxzxzJohnsonxxxzxNOzzxzxzJohnsonzzxzxzNOzzxzxzBaileyxxzxxxYESzzxzxzBaileyzxzzzzNOzzxzxzBaileyzxzzzzzzzYESzzxzxzBaileyzzxzxzNOzzxzxzBaileyxxxzxNOzzxzxzPiercezxzzzzzzzYESzzxzxzPiercexxzxxxNOzzxzxzPiercezxzzzzYESzzxzxzPiercezxzzzzzzzNOzzxzxzMcIntyrexxxxNOzzzzzzzzzOreillyxzxzxzxzYESzzzxzxzzOreillyxxxzxNOxxzxzBurnsxxxzxYESzzzBurnsxNOzzxzxzBlakexxxzxYESzzzxzxzzBlakezxzzzzzzzNOzzxzxzBlakexxxxNOzzzzzzzzzBlakexzxzxzxzYESzzzxzxzzBlakexxxzxYESxxzxzBlakezxzzzzzzzNOzzxzxz

CoG · Accepted Answer

You could try something like this: