Solved

If search finds a certain word in range then add specific comment to different cell - Continued

Posted on 2016-09-01
14
55 Views
Last Modified: 2016-09-02
This question is a continuation of a previous resolved questions - This code is perfect for what I am doing but.. I have a new issue

See image: in Column AN you have pharmacy and Temporary Lodging, then Column AT you see codes 97530 for both. I want to only put the below comment in for the one that shows "Pharmacy". How would I add this part to the existing code below?   The call out would be " Unable to resolve. Incomatible POS/CPT combination". This example shows just one pharmacy call out but in reality that can be a list of them in this column. Also just like the below code already does, it scans a range AT:AX because the combination could appear in any one of these columns.

=IF(COUNTIF($AV8:$AZ8,"*a9270*"),"A9270 By definition this is a HCPCS for non-covered services. Need instruction on benefit response. Authorization has this code.",IF(SUM(COUNTIF($AV8:$AZ8,{"*Fluoride*","*Tooth*","*Oral*","*Dental*","*denture*"}))>0,"Analysis required. Claims with Dental codes.",IF(COUNTIF($BB8:$BF8,"*BEH03*"),"Command Center issue 773 - CR1851",IF(COUNTIF($BB8:$BF8,"*CLD33*"),"Unable to resolve. Facility claim Rev code w/o CPT/HCPCS",IF(SUM(COUNTIF($AV8:$AZ8,{"*DMEMEDI*","*DMEDIAB*","*RXIMPOTG*","*RXGENRIC*"}))>0,"Command Center Issue #462 - Pharmacy claims CCA to resolve.","Analysis Required")))))
2016-09-01_11-24-40.jpg
0
Comment
Question by:Culwatrnca11
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
14 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 41781052
Hi,

pls try

=IF(COUNTIF($AT8:$AX8,"*Pharmacy*"),"Unable to resolve. Incomatible POS/CPT combination.",IF(COUNTIF($AV8:$AZ8,"*a9270*"),"A9270 By definition this is a HCPCS for non-covered services. Need instruction on benefit response. Authorization has this code.",IF(SUM(COUNTIF($AV8:$AZ8,{"*Fluoride*","*Tooth*","*Oral*","*Dental*","*denture*"}))>0,"Analysis required. Claims with Dental codes.",IF(COUNTIF($BB8:$BF8,"*BEH03*"),"Command Center issue 773 - CR1851",IF(COUNTIF($BB8:$BF8,"*CLD33*"),"Unable to resolve. Facility claim Rev code w/o CPT/HCPCS",IF(SUM(COUNTIF($AV8:$AZ8,{"*DMEMEDI*","*DMEDIAB*","*RXIMPOTG*","*RXGENRIC*"}))>0,"Command Center Issue #462 - Pharmacy claims CCA to resolve.","Analysis Required"))))))

Open in new window

Regards
0
 

Author Comment

by:Culwatrnca11
ID: 41782158
Hi Rgonzo,

I think I am looking for something similar to what is bold below. last line.. My code isnt working so what I added below is for visual..   but if "Pharmacy" is picked up in column "AR" then the range of "AV:AZ" is scanned for DMEMEDI, etc and if found will add the comment "Command Center Issue #462 - Pharmacy claims CCA to resolve".    Does that help?

=IF(COUNTIF($AV2:$AZ2,"*a9270*"),"A9270 By definition this is a HCPCS for non-covered services. Need instruction on benefit response. Authorization has this code.",IF(SUM(COUNTIF($AV2:$AZ2,{"*Fluoride*","*Tooth*","*Oral*","*Dental*","*denture*"}))>0,"Analysis required. Claims with Dental codes.",IF(COUNTIF($BB2:$BF2,"*BEH03*"),"Command Center issue 773 - CR1851",IF(COUNTIF($BB2:$BF2,"*CLD33*"),"Unable to resolve. Facility claim Rev code w/o CPT/HCPCS",IF(SUM(COUNTIF($AR2, "*Pharmacy*")>0,$AV2:$AZ2,{"*DMEMEDI*","*DMEDIAB*","*RXIMPOTG*","*RXGENRIC*"}))>0,"Command Center Issue #462 - Pharmacy claims CCA to resolve.","Analysis Required")))))
0
 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41782166
See if this is what you are trying to achieve.

=IF(COUNTIF($AV2:$AZ2,"*a9270*"),"A9270 By definition this is a HCPCS for non-covered services. Need instruction on benefit response. Authorization has this code.",IF(SUM(COUNTIF($AV2:$AZ2,{"*Fluoride*","*Tooth*","*Oral*","*Dental*","*denture*"}))>0,"Analysis required. Claims with Dental codes.",IF(COUNTIF($BB2:$BF2,"*BEH03*"),"Command Center issue 773 - CR1851",IF(COUNTIF($BB2:$BF2,"*CLD33*"),"Unable to resolve. Facility claim Rev code w/o CPT/HCPCS",IF(AND(COUNTIF($AR2, "*Pharmacy*"),SUM(COUNTIF($AV2:$AZ2,{"*DMEMEDI*","*DMEDIAB*","*RXIMPOTG*","*RXGENRIC*"}))>0),"Command Center Issue #462 - Pharmacy claims CCA to resolve.","Analysis Required")))))

Open in new window

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!

 

Author Comment

by:Culwatrnca11
ID: 41782192
awesome...  Almost there....    

It is now finding Pharmacy in "AR" and DMEMEDI, etc..  in the range "AV:AZ" and then adds the comment "Command Center Issue #462 - Pharmacy claims CCA to resolve" when a match is found but if a match is not found it dosnt add the last comment "Analysis Required".
0
 

Author Comment

by:Culwatrnca11
ID: 41782197
in addition before with the old cold if no criteria was found it would add the comment Analysis Required , it has changed all of those if no match is found to "Command Center Issue #462 - Pharmacy claims CCA to resolve".    So the last statement in the string is not being seen.
0
 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41782198
"Analysis Required" comment will be shown in the formula cell if no condition is evaluated to be true.
So if condition for col. AR and AV:AZ for Pharmacy and DMEMED etc is evaluated as false,  "Analysis Required" should be added as a comment.

but if a match is not found it dosnt add the last comment "Analysis Required".
What does the formula cell return then if it doesn't return "Analysis Required"?
0
 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41782202
Since I have no data to check it out, when I place the same formula on a blank sheet, that means no criteria is met, the formula returns "Analysis Required".
0
 

Author Comment

by:Culwatrnca11
ID: 41782241
but if a match is not found it dosnt add the last comment "Analysis Required".
What does the formula cell return then if it doesn't return "Analysis Required"?

The formula is returning "Command Center Issue #462 - Pharmacy claims CCA to resolve." even if the statement is false. Should return ,"Analysis Required"    So if any part of the code is False it is now returning "Command Center Issue #462 - Pharmacy claims CCA to resolve."not "Analysis Required"

Here is updated code.

=IF(COUNTIF($AV2:$AZ2,"*a9270*"),"A9270 By definition this is a HCPCS for non-covered services. Need instruction on benefit response. Authorization has this code.",IF(SUM(COUNTIF($AV2:$AZ2,{"*Fluoride*","*Tooth*","*Oral*","*Dental*","*denture*"}))>0,"Analysis required. Claims with Dental codes.",IF(COUNTIF($BB2:$BF2,"*BEH03*"),"Command Center issue 773 - CR1851",IF(COUNTIF($BB2:$BF2,"*CLD33*"),"Unable to resolve. Facility claim Rev code w/o CPT/HCPCS",IF(AND(COUNTIF($AP2,"1 - Pharmacy"),SUM(COUNTIF($AV2:$AZ2,{"*DMEMEDI*","*DMEDIAB*","*RXIMPOTG*","*RXGENRIC*"})))>0,"Command Center Issue #462 - Pharmacy claims CCA to resolve.","Analysis Required")))))
0
 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41782251
Here is the correct formula, you misplaced a parenthesis.

=IF(COUNTIF($AV2:$AZ2,"*a9270*"),"A9270 By definition this is a HCPCS for non-covered services. Need instruction on benefit response. Authorization has this code.",IF(SUM(COUNTIF($AV2:$AZ2,{"*Fluoride*","*Tooth*","*Oral*","*Dental*","*denture*"}))>0,"Analysis required. Claims with Dental codes.",IF(COUNTIF($BB2:$BF2,"*BEH03*"),"Command Center issue 773 - CR1851",IF(COUNTIF($BB2:$BF2,"*CLD33*"),"Unable to resolve. Facility claim Rev code w/o CPT/HCPCS",IF(AND(COUNTIF($AP2,"1 - Pharmacy"),SUM(COUNTIF($AV2:$AZ2,{"*DMEMEDI*","*DMEDIAB*","*RXIMPOTG*","*RXGENRIC*"}))>0),"Command Center Issue #462 - Pharmacy claims CCA to resolve.","Analysis Required")))))

Open in new window

0
 

Author Comment

by:Culwatrnca11
ID: 41782267
Here is what happens.  Notice I the difference between the two below for )

*RXGENRIC*"}))>0),"      when it is like this, when False it produces "Analysis Required" but does not work when true, does not find matches and return "Command Center Issue #462 - Pharmacy claims CCA to resolve.".

*RXGENRIC*"})))>0,"      when it is like this when true it returns comment "Command Center Issue #462 - Pharmacy claims CCA to resolve." which is almost working but returns same comment when false.

would it be easier to send you the file
0
 
LVL 30

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41782279
*RXGENRIC*"})))>0 makes no sense as >0 is used for the SUM formula.
Notice the AND in the last conditions so if COUNTIF($AP2,"1 - Pharmacy") is True AND SUM(COUNTIF($AV2:$AZ2,{"*DMEMEDI*","*DMEDIAB*","*RXIMPOTG*","*RXGENRIC*"}))>0 is also True i.e. both the conditions are true at the same time, then only it will return "Command Center Issue #462 - Pharmacy claims CCA to resolve.".
If that is not the case i.e. if you want one of them to be true, please replace AND with OR to see if that resolves your issue.

If that doesn't work, please upload a small sample file with the formula implemented and highlight the formula with yellow color and place the desired output in another cell manually to compare it with the formula.
0
 

Author Comment

by:Culwatrnca11
ID: 41782291
It was the AND...  replaced it with OR and put the    "}))>0),       the way you had it. Working perfectly now.. thank you... very impressive...  thank you again..

=IF(COUNTIF($AV2:$AZ2,"*a9270*"),"A9270 By definition this is a HCPCS for non-covered services. Need instruction on benefit response. Authorization has this code.",IF(SUM(COUNTIF($AV2:$AZ2,{"*Fluoride*","*Tooth*","*Oral*","*Dental*","*denture*"}))>0,"Analysis required. Claims with Dental codes.",IF(COUNTIF($BB2:$BF2,"*BEH03*"),"Command Center issue 773 - CR1851",IF(COUNTIF($BB2:$BF2,"*CLD33*"),"Unable to resolve. Facility claim Rev code w/o CPT/HCPCS",IF(OR(COUNTIF($AP2,"1 - Pharmacy"),SUM(COUNTIF($AV2:$AZ2,{"*DMEMEDI*","*DMEDIAB*","*RXIMPOTG*","*RXGENRIC*"}))>0),"Command Center Issue #462 - Pharmacy claims CCA to resolve.","Analysis Required")))))
0
 

Author Closing Comment

by:Culwatrnca11
ID: 41782293
awesome... thank you again.
0
 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41782294
You're welcome. Glad to help.
Thanks for the feedback.
0

Featured Post

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!

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

756 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