Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

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

Posted on 2016-09-29
Medium Priority
43 Views
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
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
• 2

LVL 23

Expert Comment

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

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

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

ID: 41832442
awesome thanks again
0

Featured Post

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Suggested Courses
Course of the Month9 days, 3 hours left to enroll