Formula to highlight cells if one cell contains specific text and another cell has only one of two requirements

[kmsprague]

Hi all,
I'm working with a list where I need to catch anyone who isn't meeting a requirement. In short, the requirement is that if you are enrolled in "ABC" plan then you also have to be enrolled in "DEF" plan. So if a person is correct with their enrollment, they will have more than one line listed in the report. What I want to do is have the cells in column A highlight for a person if they have ABC but don't have DEF. I've tried this formula in conditional formatting, but it isn't narrowing it down as I'd hoped. =AND(UNIQUE(A2:A3),C2,"*ABC*") I'm not sure if using unique is the way to go, so I'm open to all ideas/suggestions. Thank you in advance for your help!

In the example below, Jennifer Lopez's personnel number would be highlighted because she doesn't have both plans.

 

[DanteAmor]

Try:

Libro1
	A	B	C
1	Num	Name	Plan
2	1	Brooks	ABC
3	1	Brooks	DEF
4	3	Jen	ABC
5	4	Will	DEF
6	8	Dam	DEF
7	8	Dam	ABC
Hoja1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A7	Expression	=COUNTIFS(A:A,A2,C:C,"ABC")+COUNTIFS(A:A,A2,C:C,"DEF")=1	text	NO

 

[kmsprague]

Try:

Libro1
	A	B	C
1	Num	Name	Plan
2	1	Brooks	ABC
3	1	Brooks	DEF
4	3	Jen	ABC
5	4	Will	DEF
6	8	Dam	DEF
7	8	Dam	ABC
Hoja1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A7	Expression	=COUNTIFS(A:A,A2,C:C,"ABC")+COUNTIFS(A:A,A2,C:C,"DEF")=1	text	NO

Hi there! This was very helpful! I only ran into one problem, though. There are other plans listed in the report, so this highlighted everyone who didn't have one of these plans. I guess what I'm trying to say is to highlight anyone who has ABC without DEF, but not the other way around. Someone can have DEF on its own, so it's really the combination I'm trying to look for. I'm hope this makes sense.

 

[Peter_SSs]

Like this?

24 01 17.xlsm
	A	B	C
1	Num	Name	Plan
2	1	Brooks	ABC
3	1	Brooks	DEF
4	3	Jen	ABC
5	4	Will	DEF
6	8	Dam	DEF
7	8	Dam	ABC
Check

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A7	Expression	=AND(C2="ABC",COUNTIFS(A$2:A$7,A2,C$2:C$7,"DEF")=0)	text	NO

 

[DanteAmor]

Try:

=AND(C2="ABC",COUNTIFS(A:A,A2,C:C,"ABC")+COUNTIFS(A:A,A2,C:C,"DEF")=1)

 

[kmsprague]

Try:

=AND(C2="ABC",COUNTIFS(A:A,A2,C:C,"ABC")+COUNTIFS(A:A,A2,C:C,"DEF")=1)

Thank you! Thank you! This did the trick! Appreciate your help!

 

[kmsprague]

Like this?

24 01 17.xlsm
	A	B	C
1	Num	Name	Plan
2	1	Brooks	ABC
3	1	Brooks	DEF
4	3	Jen	ABC
5	4	Will	DEF
6	8	Dam	DEF
7	8	Dam	ABC
Check

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A7	Expression	=AND(C2="ABC",COUNTIFS(A$2:A$7,A2,C$2:C$7,"DEF")=0)	text	NO

Yes, this works! Thank you!

 

[Peter_SSs]

You're welcome. Glad we could help.

Just wondering with your data, is something like this possible where 3/Jen is listed with "ABC" twice (or more) but not with "DEF"?
IF YES, my formula would highlight both ..

24 01 17.xlsm
	A	B	C
1	Num	Name	Plan
2	1	Brooks	ABC
3	1	Brooks	DEF
4	3	Jen	ABC
5	4	Will	DEF
6	8	Dam	DEF
7	3	Jen	ABC
Check

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A7	Expression	=AND(C2="ABC",COUNTIFS(A$2:A$7,A2,C$2:C$7,"DEF")=0)	text	NO

.. and Dante's would highlight neither

24 01 17.xlsm
	A	B	C
1	Num	Name	Plan
2	1	Brooks	ABC
3	1	Brooks	DEF
4	3	Jen	ABC
5	4	Will	DEF
6	8	Dam	DEF
7	3	Jen	ABC
Check (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A7	Expression	=AND(C2="ABC",COUNTIFS(A:A,A2,C:C,"ABC")+COUNTIFS(A:A,A2,C:C,"DEF")=1)	text	NO

IF NO, then both formulas would work but there is an extra check in Dante's formula that is not required.

 

[kmsprague]

Thankfully, that isn't an issue at this time, but if that changes I sure appreciate this option! Thank you very much!