• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 77
  • Last Modified:

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

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
Culwatrnca11
Asked:
Culwatrnca11
  • 7
  • 6
1 Solution
 
Rgonzo1971Commented:
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
 
Culwatrnca11Data AnalystAuthor Commented:
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
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Culwatrnca11Data AnalystAuthor Commented:
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
 
Culwatrnca11Data AnalystAuthor Commented:
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
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
"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
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
 
Culwatrnca11Data AnalystAuthor Commented:
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
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
 
Culwatrnca11Data AnalystAuthor Commented:
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
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
*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
 
Culwatrnca11Data AnalystAuthor Commented:
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
 
Culwatrnca11Data AnalystAuthor Commented:
awesome... thank you again.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad to help.
Thanks for the feedback.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now