If then or vlookup?

[flyers2thecup22]

Hello All, been a while since i've posted. Had to make a new account.

I'm trying to figure out a way to identify a person who has "x" roles. Using the example data below, what i'm trying to do is identify an ID (column A) if they have either role3 or role4 ONLY. below i would want to somehow 'delete' or mark John Smith since he has role1, role2 and role5 in addition to role3 and role4. my final list should be those that have role3 and/or role4 ONLY. hopefully that makes some kind of sense. Is there a way to do that?

Thank you for your time!

A	B	C	D
ID	Last Name	First Name	Role
XXXXXXX	Smith	John	Role1
XXXXXXX	Smith	John	Role2
XXXXXXX	Smith	John	Role3
XXXXXXX	Smith	John	Role4
XXXXXXX	Smith	John	Role5
AAAAAAA	Doe	Jane	Role3
AAAAAAA	Doe	Jane	Role4

 

[Crystalyzer]

Why not filter the data by roles you don't want and delete those rows?

 

[flyers2thecup22]

Thank you for your response.

If I filter and hide role1, role2 and role5, that will still leave john smith visible with role3 and role4. I want him totally gone since he has role3 and role4.

 

[Crystalyzer]

How about a "Helper Column" in column "A" with the formula below then filter for all the non-zeros and delete those rows?

Book6
	A	B	C	D	E
1	Helper Column	ID	Last Name	First Name	Role
2	3	1	Smith	John	Role1
3	3	1	Smith	John	Role2
4	3	1	Smith	John	Role3
5	3	1	Smith	John	Role4
6	3	1	Smith	John	Role5
7	0	2	Doe	Jane	Role3
8	0	2	Doe	Jane	Role4
Sheet1

Cell Formulas
Range	Formula
A2:A8	A2	=COUNTIFS($B$2:$B$8,"="&B2,$E$2:$E$8,"<>Role3",$E$2:$E$8,"<>Role4")

 

[flyers2thecup22]

Awesome! That worked perfectly. Thank you very much for the help! Saved me hours/days of manually going through a spreadsheet with over 125k rows.