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

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)

"MMM" <MMM@discussions.microsoft.com> wrote in message
news:7B9A736F-8993-4FA1-B828-3BCDF6CA169E@microsoft.com...
> 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

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

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" <MMM@discussions.microsoft.com> wrote in message
> news:7B9A736F-8993-4FA1-B828-3BCDF6CA169E@microsoft.com...
> > 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
>
>
>