Hi-

I am trying to flag a record as being either €œCCO€� or €œCCT€�, where any
record with either of the following would be flagged as CCT (else would be
CCO):
1. Domain value is at least one of the following: "Customer Contact
Transformation (CCT/ACCS€�, €œCustomer Contact Transformation","Customer
Contact Transformation (Scale Play)"
OR
2. Campaign = "Customer Contact Transformation (CCT)"

I had used the following if statement:
=IF(N10&O10="","",IF(OR(N10="Customer Contact Transformation
(CCT/ACCS)",N10="Customer Contact Transformation",N10="Customer Contact
Transformation (Scale Play)",O10="Customer Contact Transformation
(CCT)"),("CCT"),("CCO")))

The problem with this, however, is that the Domain field can have multiple
values (i.e. can have one of the CCT related values in #1 plus another
unrelated value). This statement is only looking for exact matches; hence I
am not flagging all those that I need to because I want it to = CCT if any
one of the Domain values contains CCT or the Campaign value contains CCT.

Is there a way to structure IF statements to look for records that contain
certain text (i.e. like in SQL how you can do ïƒ* is like €˜%CCT%)? Perhaps I
need to do something more complex than an IF statement€¦not sure what this
would be though.

I would really appreciate any help with this. Thanks in advance.

Amanda

How about something along the lines of

IF(OR(ISERROR(FIND("CCT",A1))=FALSE,ISERROR(FIND(" CCO",A1))=FALSE),"somethin
g","something else")

You may need to fiddle with that syntaxt, but the idea is to have the
formula search the string for the three critical values.

PC

"Amanda Guenthner" wrote in
message ...
Hi-

I am trying to flag a record as being either "CCO" or "CCT", where any
record with either of the following would be flagged as CCT (else would be
CCO):
1. Domain value is at least one of the following: "Customer Contact
Transformation (CCT/ACCS", "Customer Contact Transformation","Customer
Contact Transformation (Scale Play)"
OR
2. Campaign = "Customer Contact Transformation (CCT)"

I had used the following if statement:
=IF(N10&O10="","",IF(OR(N10="Customer Contact Transformation
(CCT/ACCS)",N10="Customer Contact Transformation",N10="Customer Contact
Transformation (Scale Play)",O10="Customer Contact Transformation
(CCT)"),("CCT"),("CCO")))

The problem with this, however, is that the Domain field can have multiple
values (i.e. can have one of the CCT related values in #1 plus another
unrelated value). This statement is only looking for exact matches; hence
I
am not flagging all those that I need to because I want it to = CCT if any
one of the Domain values contains CCT or the Campaign value contains CCT.

Is there a way to structure IF statements to look for records that contain
certain text (i.e. like in SQL how you can do ? is like '%CCT%')? Perhaps
I
need to do something more complex than an IF statement.not sure what this
would be though.

I would really appreciate any help with this. Thanks in advance.

Amanda

On Sat, 4 Jun 2005 10:40:01 -0700, "Amanda Guenthner"
wrote:

Is there a way to structure IF statements to look for records that contain
certain text (i.e. like in SQL how you can do ? is like ‘%CCT%’)?

Several methods:

=COUNTIF(range_to_search,"*CCT*") will return 0 if CCT is not present. (Note
the "*" wild card characters).

=FIND("CCT",G1) will return #VALUE! if CCT not present, otherwise it will
return a positive integer. (Note NO wild card characters allowed).

=SEARCH("CCT",G1) will do the same as FIND, but is case-insensitive.


--ron

Don't know if I follow all your conditions, but try this concept:

=IF(N10&O10="","",IF(OR(COUNTIF(N10,"Customer Contact
Transformation*")0,O10="Customer Contact Transformation
(CCT)"),"CCT","CCO"))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Amanda Guenthner" wrote in
message ...
Hi-

I am trying to flag a record as being either "CCO" or "CCT", where any
record with either of the following would be flagged as CCT (else would be
CCO):
1. Domain value is at least one of the following: "Customer Contact
Transformation (CCT/ACCS", "Customer Contact Transformation","Customer
Contact Transformation (Scale Play)"
OR
2. Campaign = "Customer Contact Transformation (CCT)"

I had used the following if statement:
=IF(N10&O10="","",IF(OR(N10="Customer Contact Transformation
(CCT/ACCS)",N10="Customer Contact Transformation",N10="Customer Contact
Transformation (Scale Play)",O10="Customer Contact Transformation
(CCT)"),("CCT"),("CCO")))

The problem with this, however, is that the Domain field can have multiple
values (i.e. can have one of the CCT related values in #1 plus another
unrelated value). This statement is only looking for exact matches; hence I
am not flagging all those that I need to because I want it to = CCT if any
one of the Domain values contains CCT or the Campaign value contains CCT.

Is there a way to structure IF statements to look for records that contain
certain text (i.e. like in SQL how you can do ? is like '%CCT%')? Perhaps I
need to do something more complex than an IF statement.not sure what this
would be though.

I would really appreciate any help with this. Thanks in advance.

Amanda

Thanks - that did the trick. Although I did need to create another column
that had the =COUNTIF(range_to_search,"*CCT*") formula. I then referenced
this new column with a more simple if statement (i.e.
IF(A10,("CCT"),("CCO"))).

Is there a way to combine the logic you provided to do the wildcard search
for the string embedded in my initial IF statement? No worries if this is
really complicated.

Thanks again, Amanda

"Ron Rosenfeld" wrote:

On Sat, 4 Jun 2005 10:40:01 -0700, "Amanda Guenthner"
wrote:

Is there a way to structure IF statements to look for records that contain
certain text (i.e. like in SQL how you can do ? is like €˜%CCT%)?

Several methods:

=COUNTIF(range_to_search,"*CCT*") will return 0 if CCT is not present. (Note
the "*" wild card characters).

=FIND("CCT",G1) will return #VALUE! if CCT not present, otherwise it will
return a positive integer. (Note NO wild card characters allowed).

=SEARCH("CCT",G1) will do the same as FIND, but is case-insensitive.


--ron

On Sat, 4 Jun 2005 16:47:01 -0700, "Amanda Guenthner"
wrote:

Is there a way to combine the logic you provided to do the wildcard search
for the string embedded in my initial IF statement? No worries if this is
really complicated.

You can combine things using OR constructs. (Adding things the way I do below
is the equivalent of OR).

However, since I don't know all of the possibilities, you may have to modify my
suggestion. It should give you some ideas, though:

=IF(N10&O10="","",
IF(COUNTIF(N10,"*CCT*")+
COUNTIF(N10,"*Customer Contact Transformation*")+
COUNTIF(O10,Customer Contact Transformation),
"CCT","CCO"))



--ron