Error Status

[shantraj.antin@gmail.com]

Dear Ninjas,


Please help how to get the error status as "Pass" or "fail" under Column E of the attached file.


The conditions are,


> If same application # (Application 46) have two error like "Address" and "Agent Id" Errors in column D then my column E should show the result as "Fail" for only once. Other row for the same application # can be blank.

> If Application # has Error Type as "NA" then result in column E should be "Pass"


Please refer the attached file for your reference. Thanks in advance for your support.


https://skydrive.live.com/?cid=7de9a52f4913c67b#cid=7DE9A52F4913C67B&id=7DE9A52F4913C67B%21102

 

[Hui]

E2: =IF(COUNTIFS($C$2:$C$14,C2,$D$2:$D$14,"<>"&D2)>2,"Fail","Pass")

E3: =IF(D3="NA","Pass",IF(COUNTIFS($C$2:$C$14,C3,$D$2:$D$14,"<>"&D3)>2,IF(COUNTIFS($C$2:C2,C3,E2:E2,"Fail")=1,"","Fail"),"Pass"))

Then Copy E3 down