+ Reply to Thread
Results 1 to 9 of 9

filter column visibility with either of 2 values present

  1. #1
    Forum Contributor
    Join Date
    04-01-2015
    Location
    Sydney Australia
    MS-Off Ver
    Office 365 latest
    Posts
    171

    filter column visibility with either of 2 values present

    Hi!

    I`m using the filter function and want to filter visibility of multiple rows dependent on the data visible in 2 columns,
    one column must have the value True, the other column must have 6XL (size).

    at the moment it seems when I filter by True, the 2nd filter (size) only allows me to filter the 6XL from the True rows. However the 6XLs dont reside on the true rows but I want them visible as well...

    so when either column value is present I want that row visible, either TRUE or 6XL (they are in different columns).

    And how would I do this for multiple rows? say I want to filter so that any of 6 different values in 6 different columns is present? they could be a mixture of words or numbers.

    best,

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: filter column visibility with either of 2 values present

    You could add another column and use a formula like this:

    =OR(A2=TRUE,B2="6XL")

    Copy down as needed.

    Then you could filter on this column =TRUE.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Contributor
    Join Date
    04-01-2015
    Location
    Sydney Australia
    MS-Off Ver
    Office 365 latest
    Posts
    171

    Re: filter column visibility with either of 2 values present

    Tony thanks for the help,
    that works great, can I ask:

    how would I write that for multiple columns? say 3 or more columns that I want filtered,
    like this? =OR(A2=TRUE,B2="6XL",C2=100% cotton,D2=88) ?

    do numbers need quotations marks? (I wrote 88 without them)
    why does TRUE have no quotations, but "6XL" does?
    can I have spaces like '100% cotton' as above to match the text in that column,

    best,

  4. #4
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: filter column visibility with either of 2 values present

    You need to write like this :
    Please Login or Register  to view this content.
    Any string needs "" but numerical data can be written without "".

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: filter column visibility with either of 2 values present

    TRUE is a Boolean value, hence does not need quotes. "100% cotton" is Text so it does need quotes, regardless of whether or not there are spaces.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  6. #6
    Forum Contributor
    Join Date
    04-01-2015
    Location
    Sydney Australia
    MS-Off Ver
    Office 365 latest
    Posts
    171

    Re: filter column visibility with either of 2 values present

    thanks guys, awesome, just what I needed to know.

  7. #7
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: filter column visibility with either of 2 values present

    You are welcome and thanks for the rep.

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: filter column visibility with either of 2 values present

    You're welcome. Thanks for the feedback!

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: filter column visibility with either of 2 values present

    You're welcome. Thanks for the rep.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ 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. Replies: 2
    Last Post: 09-03-2014, 03:37 AM
  2. Replies: 0
    Last Post: 01-10-2013, 06:50 PM
  3. Improve visibility of Excel AutoFilter Filter selections
    By Cec Tarasoff in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  4. Improve visibility of Excel AutoFilter Filter selections
    By Cec Tarasoff in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  5. Improve visibility of Excel AutoFilter Filter selections
    By Cec Tarasoff in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  6. Improve visibility of Excel AutoFilter Filter selections
    By Cec Tarasoff in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM
  7. [SOLVED] Improve visibility of Excel AutoFilter Filter selections
    By Cec Tarasoff in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-22-2005, 04:05 PM

Tags for this Thread

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