PDA

View Full Version : code for determining error in s/sheet

pdeshazier
03-06-2007, 07:36 AM
I have a spreadsheet which contains 3 columns, such as:

COL C COL D COL E
ACI MEDICARE PART B UHCC
ACI TENNCARE MEDICARE
ACI MCR UHCC MEDICARE
ACI MCR MEDICARE

The "rule" is that col C should contain "ACI MCR" if EITHER columns D or E contain the word MEDICARE. If column C = "ACI MCR" and neither column D nor E contain 'MEDICARE', I need the word ERROR returned for the user. Likewise, if column D or column E do contain 'MEDICARE' and column C does not contain "ACI MCR", i need the work ERROR returned. This latter scenario is the most probable. I've tried nested if statements, using MID, AND, OR to try to achive the desired result, but can't get it to work correctly on every scenario. Here's what I currently have as a formula in 2 different columns, but need to combine them so the user only has to look for "ERROR" in one column:

IF(MID(D1,1,8)="MEDICARE",IF(C1="ACI MCR","","ERROR"))
IF(MID(E1,1,8)="MEDICARE",IF(C1="ACI MCR","","ERROR"))

Thanks in advance for any help.

gnod
03-06-2007, 08:01 AM
=IF(ISERR(SEARCH("MEDICARE",D1)),IF(ISERR(SEARCH("MEDICARE",E1)),"ERROR","ACI MCR"),"ACI MCR")

Charlize
03-06-2007, 08:04 AM
in column F : =IF(OR(AND(D1="MEDICARE";C1="ACI MCR");AND(E1="MEDICARE";C1="ACI MCR"));"";"ERROR")
It's possible that you've got to change the ; in , in your formula.

Charlize

pdeshazier
03-06-2007, 08:08 AM
thanks, but it didn't work. it ERRORED the record shown in attached s/sheet.

pdeshazier
03-06-2007, 08:16 AM
thanks, but this also errored the first record as shown in the attached file. i tried changing it to the formula shown below since MEDICARE will only comprise the first 8 chars of those D and E cells, but it still failed.

IF(OR(AND(MID(D1,1,8)="MEDICARE",C1="ACI MCR"),AND(MID(E1,1,8)="MEDICARE",C1="ACI MCR")),"","ERROR")

Bob Phillips
03-06-2007, 08:21 AM
=IF(OR(AND(COUNT(D1:E1,"*MEDICARE*")=0,C1<>"ACI MCR"),AND(COUNT(D1:E1,"*MEDICARE*")<>0,C1="ACI MCR")),"ERROR","ACI MCR")

pdeshazier
03-06-2007, 08:25 AM
thanks, but that too also errored the first record as shown in attached spreadsheet and shouldn't have. does it have anything to do with not isolating the first 8 chars in D and E, or does *MEDICARE* do that?

gnod
03-06-2007, 08:26 AM
thanks, but it didn't work. it ERRORED the record shown in attached s/sheet.

if col D or col E contains the word "MEDICARE", col C should be "ACI MCR".. right?

can you clarify, maybe i don't understand your conditions..

Bob Phillips
03-06-2007, 08:27 AM
There is only one that I see. Are you saying = ACI MCR or contains ACI MCR?

Create a sheet with all the different error conditions and a good condition.

moa
03-06-2007, 08:50 AM
=IF(OR(AND(COUNTIF(D1:E1,"MEDICARE*"),C1<>"ACI MCR"),AND(NOT(COUNTIF(D1:E1,"MEDICARE*")),C1="ACI MCR")),"ERROR","")