+ Reply to Thread
Results 1 to 5 of 5

case insensitive advanced filter?

  1. #1
    Registered User
    Join Date
    12-10-2008
    Location
    Aberdeen
    Posts
    62

    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
    Last edited by bluphoto; 02-27-2015 at 11:59 AM.

  2. #2
    Registered User
    Join Date
    12-10-2008
    Location
    Aberdeen
    Posts
    62

    Re: case insensitive advanced filter?

    I am thinking that perhaps, somehow, I could use "mid" to find the lower case "a" in the string of tags, and use that in my condition formula.

    If that might work, then some pointers would be relly appreciated.

  3. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: case insensitive advanced filter?

    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
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  4. #4
    Registered User
    Join Date
    12-10-2008
    Location
    Aberdeen
    Posts
    62

    Re: case insensitive advanced filter?

    Hi there - thanks for the suggestion but I'm already doing that.

    I'm using A0-A9 through to Z0-Z9 for entries read directly from a client system - we have already tagged around 4500 records on the client system and trained around 30 international users on the tagging process.

    Tags are al lpreceded by ##, to a tag looks like ##G1 for example. My sheet uses the ##1>##9 tags (ie ##10, ##11, ##12, ##13 up to ##90, ##91, ##92, ##93 etc) for "calculated" reports - ie aggregating quantities of records which may include all C1+C2+C3+F1+F2+F3 for example.

    eg qty##C1 + qty##F1 = qty##11; qty##C2 + qty##F2 = qty##12 and so on. The users don't know about the tags which are prefixed with numbers only. Those only exist in my sheet and do not exist in the global database.

    My problem is that 10 calculated tags is no longer enough, so I am trying to find other tag numbers not used previously - hence my suggestion of using ##a0, ##b1 etc.

    Introducing a new tagging system for the users at this stage is really not going to fly They need to keep the "one letter one number" thing.

    FYI - the records represent "problems" in a software action tracker. The "letter" tags represent different areas of the software, and the number represents criticality.

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: case insensitive advanced filter?

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Use Like to Find a String that is Case Insensitive
    By Excel Guy 123 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-29-2014, 02:28 PM
  2. How to make case insensitive within a MACRO
    By andreadouglas26 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-05-2012, 01:16 PM
  3. Find (case and diacritics insensitive) duplicates in one column
    By greek in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-15-2011, 04:00 AM
  4. Case insensitive search for string within folder of excel files
    By vba_novice in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-22-2011, 11:21 PM
  5. [SOLVED] VBA and Case Insensitive
    By robin.coe@gmacinsurance.com in forum Excel General
    Replies: 2
    Last Post: 03-09-2006, 01:35 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1