Results 1 to 3 of 3

Thread: Complex Filter

  1. #1
    New Lounger
    Join Date
    Aug 2010
    Location
    Brisbane
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Complex Filter

    is it possible (using the attached sheet) to set up a filter or a lookup that will sort the table to only show me a filtered range which includes a combination of
    1. Student names
    2. Course names
    3. and cells where "enrol" is contained

    What I want is to filter the sheet so I see only the cells which contain the entry "ENROL" and see the name of student the cell relates to and also the course heading above the column where the entry "ENROL" is recorded
    Attached Files Attached Files
    Last edited by icets; 2011-12-28 at 11:10. Reason: Forgot to attach sheet

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 481 Times in 458 Posts
    You could use a check column, say column [Z] to add a formula to test for your conditions.
    e.g. add the formula
    =MATCH("Enrol",E3:Y3,FALSE)>0
    ..to cell [z3] and copy this down.

    Then filter on this column (TRUE) to show only those rows that include "Enrol" somewhere between cols [E] to [Y]
    Adjust formula for any other conditions e.g. non-blank course name etc

    zeddy

  3. #3
    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
    Or use:
    =COUNTIF(E3:Y3,"*enrol*")>0

    To see if there is at least 1 cell containing "enrol". Or without the ">0" you can see how many "enrol"s are in it and filter out the ones with 0. This would allow sorting on the column to see who has the most...

    Steve

Posting Permissions

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