Solved

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

Posted on 2016-09-01
14
38 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
  • 7
  • 6
14 Comments
 
LVL 48

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 28

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
 

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 28

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 28

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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 28

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 28

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 28

Expert Comment

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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…

758 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

21 Experts available now in Live!

Get 1:1 Help Now