Conditional Formatting for Array containing Text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I've been trying to tackle this for a long time; will appreciate your help.

My data (text) is in Column B (B3:B13)

G
Y
Y
R
G
G
G
G
Y
G
Y

I want to set the following conditions: If there is even 1 "R" in the array,
return "R" in the cell where I am entering the formula. Then, if there is no
"R", return "Y" if there is even a single "Y", then, if there is no "R" OR
"Y", return "G" if there is even a single "G", then, return "C" if all cells
contain "C", then, if there is no "R", "Y","G" or "C", return N

Hope I've been able to explain this clearly. Thanks in advance for your help.
 
Does this do what you want?

=IF(SUM(COUNTIF(B3:B13,{"C","G","Y","R"})),LOOKUP(ROWS(B3:B13)+1,MATCH({"C","G","Y","R"},B3:B13,0),{"C","G","Y","R"}),"N")
 
=IF(COUNTIF($B$3:$B$13,"R")>0,"R",IF(COUNTIF($B$3:$B$13,"Y")>0,"Y",IF(COUNTI
F($B$3:$B$13,"G")>0,"G",IF(COUNTIF($B$3:$B$13,"C")=ROWS($B$3:$B$13),"C","N")
)))


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
Thanks Bob, it worked wonderfully
--
MMM


Bob Phillips said:
=IF(COUNTIF($B$3:$B$13,"R")>0,"R",IF(COUNTIF($B$3:$B$13,"Y")>0,"Y",IF(COUNTI
F($B$3:$B$13,"G")>0,"G",IF(COUNTIF($B$3:$B$13,"C")=ROWS($B$3:$B$13),"C","N")
)))


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
Click to expand...
 
Back
Top