Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Feb 2005
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple values in a column for Filter? (2000)

    I have to imagine that this question has been dealt with before, and if so I apologize for the duplication - I've searched quite a ways back & haven't been able to spot it. Please feel free simply to refer me to an earlier thread. I'm a *very* basic Excel user, and haven't been able to find this in Help text either (surprise, surprise... lol)

    I have a spreadsheet with a column for Source (a code indicating where I got this contact information), and want to be able to Filter based on that code. No problem.

    Except... It turns out that I need to be able to enter more than one code in this column. So far that's taking the format of:

    A, D, A5, 9001 etc... [yes, the codes have varying structures -- and yes, I could change that situation if need be]

    Can anyone suggest a way to be able to filter on any one of those codes at a time? e.g. today I might want to filter on all records with A, tomorrow for all records with 9001 somewhere in the cell, etc. As far as I can tell so far I only need to be able to filter for one value at a time.

    I know it would be most clean to use a separate column for each of the Sources, but there are just too many for that to be practical -- I'm at about 39 values, and growing.

    I've done very little with macros, so if a proposed solution requires one, I'd appreciate a detailed explanation.

    thanks for any possible help!

    Bob

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Multiple values in a column for Filter? (2000)

    Create a new column and add the "search string" in a cell.

    Have the column use search with this "search string cell" to see if it contains it. Then you can filter on this cell (true/false).

    See the simple attached example

    Steve

  3. #3
    New Lounger
    Join Date
    Feb 2005
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple values in a column for Filter? (2000)

    Amazing! Thanks very much for the quick solution. You've certainly validated my faith in this site -- there's no way I would have ever figured out this technique on my own.

    This seems like a simple, elegant solution (once you know the functions, that is...) -- Thanks again.

    Bob

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Multiple values in a column for Filter? (2000)

    Hi Steve,

    Why do you concatenate a comma to both strings in the search function? I tried deleting the comma from both arguments and it seemed to give the same results.

    TIA

    Fred

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiple values in a column for Filter? (2000)

    To avoid partial matches. If you enter 90 in cell B1, the formula including commas will return FALSE, but the formula without the commas will return TRUE for the cells containing 9001.

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Multiple values in a column for Filter? (2000)

    Thanks Hans. I got the 2nd part of your statement (formula without the commas returning true, hence the question) but didn't think of the partial matches in the first part of your statement.

    Fred

Posting Permissions

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