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
4
Medium Priority
?
43 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
[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
  • Learn & ask questions
  • 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

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.

721 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