Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
How do I apply conditional formatting to a text box | Excel Worksheet Functions | |||
Conditional Formatting Error | Excel Worksheet Functions | |||
Conditional Formatting based on Text within Text | Excel Discussion (Misc queries) | |||
conditional formatting conflict? | Excel Worksheet Functions |