Solved

IF/OR Statement

Posted on 2011-02-15
6
284 Views
Last Modified: 2012-05-11
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
Comment
Question by:Seamus2626
  • 3
  • 3
6 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34898317
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
 

Author Comment

by:Seamus2626
ID: 34898347
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 34898409
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Closing Comment

by:Seamus2626
ID: 34898440
Thanks Rory!

Out of interest, what is Character 160?

Thanks
Seamus
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34898497
It's a non-breaking space, I believe.
0
 

Author Comment

by:Seamus2626
ID: 34898532
Thank you
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now