Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Incorporating a number range, not cell range into search like 15271 - 15278

Posted on 2016-09-29
4
Medium Priority
?
45 Views
Last Modified: 2016-10-06
Incorporating a number range into search like 15271 - 15278, ~15278 can this be done? or do you have to list them like 15271, 15272, etc? Below is an example of what I am trying to do.

IF(SUM(COUNTIF($AV2:$AZ2,{"*15271-15278*","*32554-32557*","*52287*","*64615*"}))>0,"Claim can be released with BEI06 or BEI34.","Please Review")

Thank you,
0
Comment
Question by:Culwatrnca11
  • 2
4 Comments
 
LVL 23

Expert Comment

by:Danny Child
ID: 41821977
Would using the AutoFilter be a quick way to do this?

http://www.excel-easy.com/data-analysis/filter.html

and using the =SUBTOTAL(9,<range>) function is pretty cool for just totalling the visible cells after a filter.
it does other things too:
https://blog.udemy.com/excel-subtotal-function/
0
 

Author Comment

by:Culwatrnca11
ID: 41822000
Hi Dan

Thanks for responding..   I have an excel spreadsheet with code that searches colomn ranges for specific criteria then auto populates a comment, saving the person manually doing this audit 2 hours of work. I need to be able to incorporate a range of numbers if possible in my code. With some help from the experts I have been able to get away with using individual numbers, etc.. to accurately place each comment. Here are some examples of my other code. Are number ranges possible in SQL?

IF(SUM(COUNTIF($AV2:$AZ2,{"*D0140*","*D0150*","*D0340*","*D1120*","*D1206*",   "*D2161*","*D2394*","*D5760*","*D7210*","*D9999*"}))>0,"Dental codes – Pending BCIF currently under review by Coding Team.",

IF(AND(SUM(COUNTIF($AP2,{"*11*","*15*","*17*","*20*","*49*","*50*","*71*","*72*"}))>0,SUM(COUNTIF($AV2:$AZ2,{"*G0480*","*G0479*","*G0477*","*G0478*","*G0481*","*G0482*","*G0483*"}))>0),"Unable to resolve. Need InfoCode for LABOUTP. BMSD is mapped. Benefit Engine not mapped.","Please Review"))
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 2000 total points
ID: 41823981
I think you could achieve this by setting up a lookup table with all the valid matching numbers (substrings in your searched data) and then use an array formula to check them all for possible matches.

You would set up a table with ALL values - you couldn't enter just a "start - end" range.  That is the downside.  The upside is that the list is more transparent and editable than it would be embedded within a compound function like now.

Once the table is set up - and I'd recommend making it an Excel Table (Menu: Insert, Table), you'd re-write the function like so:
{=IF(SUM(COUNTIF(AV2:AZ2,"*"&Table1&"*"))>0,"Claim can be released with BEI06 or BEI34.","Please Review")}

To make it an array function, you have to use [Ctrl]+[Shift]+[Enter] to input it in the cell.  You'll see the curly brackets around it.  You can then copy that formula down.

In my example above, "Table1" is the default range name given the table with all the matching numbers, but you could also use a Sheet!Range reference if you wanted.

I've attached an example file.  It has codes randomly generated; just press [F9] to see new ones.

Regards,
-Glenn
EE-Q_28973326.xlsx
0
 

Author Closing Comment

by:Culwatrnca11
ID: 41832442
awesome thanks again
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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

577 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