Conditional Formatting for Array containing Text
 

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.

--
MMM

Conditional Formatting for Array containing Text
 

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")


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=507489

Conditional Formatting for Array containing Text
 

=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)

"MMM" wrote in message
...
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.

--
MMM

Conditional Formatting for Array containing Text
 

Thank you daddylonglegs. It worked like a dream.
--
MMM


"daddylonglegs" wrote:


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")


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=507489

Conditional Formatting for Array containing Text
 

Thanks Bob, it worked wonderfully
--
MMM


"Bob Phillips" wrote:

=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)

"MMM" wrote in message
...
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.

--
MMM