Conditional Formatting

[bpmurphy]

Hello all, I am trying to conditionally format the "Completed?" column based on criteria. If "Completed?" equals N but ONLY if there isn't another row with the same Acct # and a completed of Y. So for the example below Acct # 12345 shows up multiple times but Completed should NOT be highlighted because the third entry for that acct # is Y. Similarly, for acct # 30121 it should be highlighted for N because it's the only entry. Any ideas? Appreciate the help!

[TABLE="width: 200"]
<tbody>[TR]
[TD]Acct #[/TD]
[TD]Accts[/TD]
[TD]Completed?[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]2[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]1[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]3[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]30121[/TD]
[TD]1[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]20984[/TD]
[TD]1[/TD]
[TD]Y[/TD]
[/TR]
</tbody>[/TABLE]

 

[Johnny C]

Re: Conditional Formatting Help

Try... (assuming you're in cell C3)

=AND($C3="N",COUNTIFS($A,$A3,$C,"<>N")>0)

 

[mart37]

Re: Conditional Formatting Help

=and(c2="n",countifs($a$2:$a$6,a2,$c$2:$c$6,"y")=0)

 

[bpmurphy]

Re: Conditional Formatting Help

Try... (assuming you're in cell C3)

=AND($C3="N",COUNTIFS($A,$A3,$C,"<>N")>0)

Thank you, however, that formula doesn't correctly identify singular instances such as the acct #30121 with only one row of data.

 

[Johnny C]

Re: Conditional Formatting Help

Try
=AND($C5="N",COUNTIFS($A:$A,$A5,$C:$C,"<>N")=0)
All you need to do is create the formula in a column and then transfer it to the conditional formatting box.

 

[bpmurphy]

Re: Conditional Formatting Help

Thanks for the ideas, I played around with both your formulas and managed to get it to work as a 1,0 output. Used the conditions of 1 as formatted and 0 as non. Appreciate the help!

 

[Johnny C]

Re: Conditional Formatting Help

Goodo. if you're doing anything fancy with conditional formats with formulae, it's always worth doing it in additional columns or rows to make use it does what you want then transfer it to the conditional formatting.

Just remember to use as many brackets as possible, later versions of Excel are more forgiving but earlier ones needed brackets everywhere to force it to calculate true or false, even on something like =(A5=10).