case insensitive advanced filter?

I have a sheet which is being filtered in place via excel 2010 adv filtering using multiple criteria.
One of these criteria is a string of codes: S2272:F1272:11272:41272:62272:a1272 - obviously these codes change from line to line and are generated in VBA based on a number of conditions.

The thing is I have now run out of characters to prefix the code with. In the above case the first character "S" or "F", or "1" etc. I have used all characters from A to Z and 0 to 9.

In order to expand the system any further, I need to start using "a" to "z" lower case. Yes, this might be confusing, but my only alternative is to start using punctuation characters as part of the code and that's never going to fly.

I have implemented the "expanded" system, and it's working fine, other than when I filter the data using excel advanced filter condition "*a????", I get both "A1234" and "a1234". "A" and "a" mean two different things, and if I ask for "a", I don't want "A".


Capture.JPG

Any way out of this?

many thanks,
Guy

Instead of code for upper and lower case or numbers, have you tried to introduce an alpha-numeric coding system such as A1-, A2-, B1-, B2- etc.?

In any case:
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

I suggested that you use a prefix like A1- note the dash to separate the prefix. In this way you are unlimited in the number of prefixes. You could continue past ZZZZZ9999999- that will give you hundreds of millions of prefixes.

Are your users really that inflexible that you have to keep the "1 number thing"?

4500 records are nothing. My career was managing millions of records.