Solved

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

Posted on 2016-09-29
4
37 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 500 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
populate Excel dropdown via ADO and VBA 6 17
VBA Help 18 44
VLOOKUP 6 17
Select Next Route by Time 4 19
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
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…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

777 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