• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

IF/OR Statement

Just noticed my last ss shows any comments from AP, i only want the comments returned to AO if they are exactly as they are in the brackets. Otherwise "No Narrative Provided"

e.g. AO14 would be "No Narrative Provided" as well as A015

Thanks
Seamus
Example.xls
0
Seamus2626
Asked:
Seamus2626
  • 3
  • 3
1 Solution
 
Rory ArchibaldCommented:
Can you clarify that? That appears to be the opposite of thhe intention of your formula, which is trying to return "No narrative provided" if the cell does match one of the items.
0
 
Seamus2626Author Commented:
I know! Basically what im looking to achieve is:

If the text in AP matches any of the bracketed options in the IF Staement, return AP, If it doesnt exactly match the bracketed options or is blank, return "No Narrative Provided"

Thanks
Seamus
0
 
Rory ArchibaldCommented:
Then you need:

=IF(OR(TRIM(SUBSTITUTE(AP2,CHAR(160),""))="Fidessa Booking instructed, Awaiting Sophis Booking",TRIM(SUBSTITUTE(AP2,CHAR(160),""))="Re - Allocated to different Source code",TRIM(SUBSTITUTE(AP2,CHAR(160),""))="No Narrative Provided",TRIM(SUBSTITUTE(AP2,CHAR(160),""))="Open Investigation",TRIM(SUBSTITUTE(AP2,CHAR(160),""))="fidessa booking to be made",TRIM(SUBSTITUTE(AP2,CHAR(160),""))="Corporate Actions Booking Adjustment required",TRIM(SUBSTITUTE(AP2,CHAR(160),""))="Paris involved in resolution",TRIM(SUBSTITUTE(AP2,CHAR(160),""))="New break requires investigatation (1 - 3 day)"),TRIM(SUBSTITUTE(AP2,CHAR(160),"")), "No Narrative Provided")

because you have character 160 and trailing spaces in column AP (I guess you copied form the web)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Seamus2626Author Commented:
Thanks Rory!

Out of interest, what is Character 160?

Thanks
Seamus
0
 
Rory ArchibaldCommented:
It's a non-breaking space, I believe.
0
 
Seamus2626Author Commented:
Thank you
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now