+ Reply to Thread
Results 1 to 20 of 20

Cant filter 2 columns even with advance filter

  1. #1
    Forum Contributor
    Join Date
    07-03-2015
    Location
    London
    MS-Off Ver
    MS proplus
    Posts
    147

    Cant filter 2 columns even with advance filter

    Hi. I have two columns of teams Home & Away. I need to filter one team to show all their fixtures at once. Advanced filter doesnt work and still only filters one of the columns. I have W7 and MS Pro Plus, thanks for any help. Paul. Pasted for convenience?

    I uploaded a file.
    Attached Files Attached Files
    Last edited by pingpong1; 07-03-2015 at 01:43 PM.

  2. #2
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Cant filter 2 columns even with advance filter

    hi pingpong,
    the pasted info is more of an inconvenience.
    so

    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
    Regards,
    Vikas Gautam
    Excel-buzz.blogspot.com

    Excel is not a matter of Experience, its a matter of Application.

    Say Thanks, Click * Add Reputation

  3. #3
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Thumbs up Re: Cant filter 2 columns even with advance filter

    Okay I am sure you need an OR condition.
    So check the attached file for new criteria range for your advanced filters.

    you can perform it using vba and advanced filter in much quick way.
    here is a link for that.
    Advanced filter through vbal

    at the end of article, there are 2 links provided.
    have a look at them for learning more criterias.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    07-03-2015
    Location
    London
    MS-Off Ver
    MS proplus
    Posts
    147

    Re: Cant filter 2 columns even with advance filter

    I can see the result in the file but not how you did it. Also I would need the opposing team showing as well. Unfortunatley I have no VBA skills to apply, sorry. I cannot see where an OR statement would go in the advanced filter. Any further help appreciated Vikas

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Cant filter 2 columns even with advance filter

    h1 = name

    H2 =if(or($C2=$H$1,$F2=$H$1),$H$1,"")

    after that filter on colum H.

    see the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  6. #6
    Forum Contributor
    Join Date
    07-03-2015
    Location
    London
    MS-Off Ver
    MS proplus
    Posts
    147

    Re: Cant filter 2 columns even with advance filter

    Appreciated Oldere. I see the formula but dont see how that filters my data table or places the filtered results in another location. My Excel is not great

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Cant filter 2 columns even with advance filter

    See the attached file with a filter on the data.

  8. #8
    Forum Contributor
    Join Date
    07-03-2015
    Location
    London
    MS-Off Ver
    MS proplus
    Posts
    147

    Re: Cant filter 2 columns even with advance filter

    Oldere. You English may not be good but understanding a filter formulae is. I had to see how to put it in my original workbook but I have done that now and saved it. So next season I should be all set to go, thanks.

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Cant filter 2 columns even with advance filter

    You're welcome. We appreciate the feedback!

    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

    It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.

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

    Re: Cant filter 2 columns even with advance filter

    I used the filter that was on your data when I opened the file and sorted the data on the HOME column then created the Advanced Filter Criteria in H1:M3 and chose to not filter in place to show the results more clearly.

    The advanced filter dialogue:
    Filter.JPG

    RESULT:

    Advanced Filter.JPG
    Attached Files Attached Files
    Last edited by newdoverman; 07-03-2015 at 03:24 PM.
    <---------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

  11. #11
    Forum Contributor
    Join Date
    07-03-2015
    Location
    London
    MS-Off Ver
    MS proplus
    Posts
    147

    Re: Cant filter 2 columns even with advance filter

    All done, thank you both, Paul.

  12. #12
    Forum Contributor
    Join Date
    07-03-2015
    Location
    London
    MS-Off Ver
    MS proplus
    Posts
    147

    Re: Cant filter 2 columns even with advance filter

    That looks simpler Newdoverman, I will ponder this too. I wish I could print it lol.

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

    Re: Cant filter 2 columns even with advance filter

    I have edited the post while you were obviously seeing an older post . The Excel file is included.

  14. #14
    Forum Contributor
    Join Date
    07-03-2015
    Location
    London
    MS-Off Ver
    MS proplus
    Posts
    147

    Re: Cant filter 2 columns even with advance filter

    So how would this work with the whole season (attached) do you have to put all teams in,
    can you show me please, P.

    I called it "All Teams" to avoid confusion
    Attached Files Attached Files

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

    Re: Cant filter 2 columns even with advance filter

    In the filter, you are choosing what it is that you want the data for. I picked two teams from the Home column. If you want more teams, add them to the criteria and increase the criteria range in the filter dialogue to include all items.
    Attached Files Attached Files

  16. #16
    Forum Contributor
    Join Date
    07-03-2015
    Location
    London
    MS-Off Ver
    MS proplus
    Posts
    147

    Re: Cant filter 2 columns even with advance filter

    Ah Thanks. I see I am not being clear enough. What I need to see is all (say Avengers Assemble) matches only both home and away in date order. From what you filtered I got all the home games but none of the away games. So under the first of the AA home games I would expect to see alternately one home game then one away game in date order, does that make sense? If you can do it just do it for one team then I will try and grasp it. Thanks Newdoverman

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

    Re: Cant filter 2 columns even with advance filter

    If you were wanting the home AND away for one team this is what you would enter and what you would have as a result:

    H
    I
    J
    K
    L
    M
    1
    WEEK
    DATE
    HOME
    DAY
    V
    AWAY
    2
    Avengers Assemble
    3
    Avengers Assemble
    4
    5
    6
    WEEK
    DATE
    HOME
    DAY
    V
    AWAY
    7
    WEEK 1
    02/06/2015
    Avengers Assemble
    MONDAY
    v
    The Good the Bad & The Ugly
    8
    WEEK 3
    10/06/2015
    Avengers Assemble
    TUESDAY
    v
    Whiff Waffs
    9
    WEEK 5
    18/06/2015
    Avengers Assemble
    MONDAY
    v
    The Bad E's
    10
    WEEK 6
    21/06/2015
    Avengers Assemble
    MONDAY
    v
    FREE WEEK
    11
    WEEK 9
    04/07/2015
    Avengers Assemble
    MONDAY
    v
    Red Dragons
    12
    WEEK 11
    12/07/2015
    Avengers Assemble
    MONDAY
    v
    The Undecideds
    13
    WEEK 14
    23/07/2015
    Avengers Assemble
    MONDAY
    v
    Hot Wing Gang
    14
    WEEK 13
    19/07/2015
    FREE WEEK
    MONDAY
    v
    Avengers Assemble
    15
    WEEK 7
    25/06/2015
    Hot Wing Gang
    MONDAY
    v
    Avengers Assemble
    16
    WEEK 2
    06/06/2015
    Red Dragons
    THURSDAY
    v
    Avengers Assemble
    17
    WEEK 12
    16/07/2015
    The Bad E's
    THURSDAY
    v
    Avengers Assemble
    18
    WEEK 8
    30/06/2015
    The Good the Bad & The Ugly
    MONDAY
    v
    Avengers Assemble
    19
    WEEK 4
    14/06/2015
    The Undecideds
    TUESDAY
    v
    Avengers Assemble
    20
    WEEK 10
    08/07/2015
    Whiff Waffs
    MONDAY
    v
    Avengers Assemble

  18. #18
    Forum Contributor
    Join Date
    07-03-2015
    Location
    London
    MS-Off Ver
    MS proplus
    Posts
    147

    Re: Cant filter 2 columns even with advance filter

    That is precisely what I want (that was fast !) I might need to come back to you about how to filter (which columns or what formula)but meanwhile thanks Ron

  19. #19
    Forum Contributor
    Join Date
    07-03-2015
    Location
    London
    MS-Off Ver
    MS proplus
    Posts
    147

    Re: Cant filter 2 columns even with advance filter

    Well that was easy GREAT thanks Paul.

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

    Re: Cant filter 2 columns even with advance filter

    You're welcome

+ 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. advance filter
    By billisnice in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-24-2015, 12:11 AM
  2. Replies: 3
    Last Post: 09-07-2009, 06:28 AM
  3. Advance Filter
    By tt388 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-28-2008, 07:32 AM
  4. Advance Filter
    By betmck in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-18-2007, 02:48 PM
  5. Advance filter search does not filter an exact match
    By cfiiland in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-10-2005, 08:05 AM

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