Autofilter by Alphanumeric and non-standard numbers

[jomili]

I need help trying to filter the attached sheet. Column B ("CPS Unit") has data formatted with two digit numbers (00 through 99) and alpha characters (A1 through ZZ). I need to be able to create customer filters to look at ranges such as 00 through 89, or 90 through B9, or C0 through H9, etc. I've tried everything I know to try, and am coming up empty, so hope someone here can figure out what I can't. I'd prefer a programmatic solution, as I'll be running multiple scenarios, but will take whatever I can get.

Thanks in advance for your help.

[Leith Ross]

Hello jomili,

Why can't you use AutoFilter? If you want a code solution then how or where will the user input their selection?

[jomili]

Leath,

Thanks for chiming in. I'm about to leave for the day, so won't respond until tomorrow if you reply.

I'd love to use Autofilter. That's my problem, I can't get it to work on a mixture of numeric and alpha numeric characters. For instance, if I want to find all the values in the range 00 to 89, if I do a custom filter and put in "Is greater than or equal to" and put in either a "0" or "00", then do an "And" "Less than or equal to 89", I get NO results. Same if I try for C0 and H9.

I'll have sort for 7 different ranges altogether, in 7 different steps. The user's won't have to input anything, my macro will perform the filters for their specific region. So, I'll need to automate the filter for the following ranges:

Please Login or Register  to view this content.

I think I can write all the rest of the code, I just can't figure out how to get an autofilter to pull up those specific ranges. You've been a big help before, so I hope you can help me on this one.

Thanks,
John

[shg]

You could insert a column to convert the code to a number:

=SUM({100,1} * (CODE(MID(B5, {1,2}, 1)) - 48 - 7 * (MID(B5, {1,2}, 1) >= "A")))

00 --> 0
ZZ --> 3535

Then filter on that.

A couple of exemplar cells with the same formula adjacent would give you the values you need for the autofilter.

[jomili]

SHG,

Good idea in theory, but I don't know that I could get it to work in practice. For instance, the range 00 through 99 is part of the set. So, if I get, as in your example, 35 to stand for Z ("3535" for "ZZ"), 35 is already also it's own value in the range 00-99, so how would I know in one instance 35 means 35, and in another instance 35 means Z?

[shg]

You would filter on the column that has the formula. There is no ambiguity.

[jomili]

SHG,

Please pardon my arrogance! I shot down your idea without giving it a try.

My thought was that your formula would convert the alpha pieces of the alphanumeric code, and I couldn't see past my own thought (that's the true definition of arrogance). When I actually TRIED what you were saying, it worked beautifully. It gives me an alternate numerical value by which I can filter for what I need, and solves my problem nicely.

Now, if you don't mind, would you please explain how your formula works? I'd like to understand it in case I ever need to replicate or adapt. And, so I don't forget, THANK YOU VERY MUCH FOR YOUR HELP on this, and please forgive me for being a butt.

Thanks,
John

[shg]

It just converts the first and second characters to values 0 to 35, multiplies the first by 100, and totals the result. The easiest way to understand it is to watch it evaluate using the formula auditing toolbar.

[jomili]

Thanks for the direction, and for the help.