Solved

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

Posted on 2016-09-01
14
49 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 49

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 29

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 29

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 29

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 29

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 29

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 29

Expert Comment

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

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

778 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