Solved

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

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

Industry Leaders: 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

Suggested Solutions

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

756 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