+ Reply to Thread
Results 1 to 11 of 11

Advanaced Filtering

  1. #1
    Registered User
    Join Date
    01-13-2011
    Location
    Michigan,USA
    MS-Off Ver
    Excel 2013
    Posts
    8

    Post Advanaced Filtering

    Hello

    I'm using this formula =COUNTIF(Formula!R10:R54,'Quality Scan 1'!S2)=0

    The criteria is in a column called Test Regulations. Here is the criteria.

    Test Regulations
    ANCAP
    China NCAP
    CMVSS 208/GME
    CMVSS 215
    Door Pad/Arm Rest
    Drop Silo Component Development
    Drop Silo Decklid Development
    Drop Silo Hood Development
    Drop Silo Rail Development
    ECE R12
    ECE R17
    ECE R21
    ECE R25
    ECE R42
    ECE R631/2009
    ECE R78 Euro-NCAP (Adult Headform)
    ECE R78 Euro-NCAP (Child Headform)
    ECE R78 Euro-NCAP (Foam Cert)
    ECE R78 Euro-NCAP (Upper Legform)
    ECE R78/2009
    EEC 77/389
    Ejection Mitigation
    EuroNCAP
    FMVSS 201 (IP)
    FMVSS 201 (Seatback)
    FMVSS 201U
    FMVSS 201L
    FMVSS 202A
    FMVSS 203
    GSO41
    GTR
    JNCAP
    KMVSS 20
    KMVSS 88
    KMVSS 89
    KMVSS 93/1
    KMVSS 98
    Korean NCAP
    None
    OPEC-GMNA
    OPEC-GMNA GMN 9410
    Site Development
    TRIAS 63 (Article 18)
    TWG
    US (Part 581)

    When I run the advanced filter it catches almost everything. It misses some the words call GTR. It will filter out some called GTR but not all of them. I cannot seem to figure out why. It filter some but not others. This is part of a sheet that contains 3500 rows and 33 columns. I do import this data from another program and it lets me save it only as a csv file. I then convert it to an xlsm file. Any help would be great.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Advanaced Filtering

    If it is filtering some, but not all, of a certain criteria, then probably the 1's not being filters are different - but not obviously so. Check for leading/trailing spaces, spelling etc. If you know that 1 is being filtered and 1 is not, and you think they are the same, use =EXACT)cell-ref1, cell-ref2) to see if they really are the same
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Advanaced Filtering

    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 then scroll down to Manage Attachments to open the upload window.
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  4. #4
    Registered User
    Join Date
    01-13-2011
    Location
    Michigan,USA
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: Advanaced Filtering

    Been out sick a few days. Sorry for the delay. I will give it a try and let you know.

  5. #5
    Registered User
    Join Date
    01-13-2011
    Location
    Michigan,USA
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: Advanaced Filtering

    Quote Originally Posted by FDibbins View Post
    If it is filtering some, but not all, of a certain criteria, then probably the 1's not being filters are different - but not obviously so. Check for leading/trailing spaces, spelling etc. If you know that 1 is being filtered and 1 is not, and you think they are the same, use =EXACT)cell-ref1, cell-ref2) to see if they really are the same
    Ok I tried it and it shows false. I take that they don't match. They look the same. Not sure how to find out what's different between the two I checked. I will try and upload an test find that shows it does not work. Thanks you guy for all your help. I have been trying to fix this for a while.
    Attached Files Attached Files

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Advanaced Filtering

    Thanks for the file

    the 1st GTR has a training space

  7. #7
    Registered User
    Join Date
    01-13-2011
    Location
    Michigan,USA
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: Advanaced Filtering

    Quote Originally Posted by FDibbins View Post
    Thanks for the file

    the 1st GTR has a training space
    Not sure what training space is. Is that the same as trailing space? Is there a way to fix a column of cells to rid it of them?

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Advanaced Filtering

    oops yes, I meant traiLing - typo, apologies

    where is this data coming from?
    There are a few ways I can think of, but all could potentially diminish accuracy, as they would involve using wild cards. Depending on how diverse or similar your data is, this could be no problem, or a big problem

    What exactly are you trying to do here?

  9. #9
    Registered User
    Join Date
    01-13-2011
    Location
    Michigan,USA
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: Advanaced Filtering

    Quote Originally Posted by FDibbins View Post
    oops yes, I meant traiLing - typo, apologies

    where is this data coming from?
    There are a few ways I can think of, but all could potentially diminish accuracy, as they would involve using wild cards. Depending on how diverse or similar your data is, this could be no problem, or a big problem

    What exactly are you trying to do here?
    I import the data from a web base program. It only gives me the choice to save it as a csv file.

    The tab called TestCrit is always going to be the same. I will be using that to filter out the TestSheet. I'm looking for people who are not entering exactly what is from the TestCrit tab. So it shows only what's not on the TestCrit. Does that make sense.

  10. #10
    Registered User
    Join Date
    01-13-2011
    Location
    Michigan,USA
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: Advanaced Filtering

    Do you think I could use this code and just run it as a macro on the data before I do the filtering. Or is there a way to have this auto load this code when I import the data?

    Sub NoSpaces()
    Dim c As Range
    For Each c In Selection.Cells
    c = Trim(c)
    Next
    End Sub

  11. #11
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Advanaced Filtering

    btw. update your profile about Excel version. Ex 2003 doesn't support xlsx extension.

+ 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. (edited title) BUG filtering for blanks (or filtering out all empty rows)
    By zextrot in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-22-2017, 01:51 PM
  2. [SOLVED] Need help filtering data by filtering based on the last digit of a column/true statement
    By Stephen R in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-11-2014, 07:43 AM
  3. [SOLVED] Easy filtering method via vba/formula/advanced filtering?
    By jedemeyer1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-10-2014, 12:35 AM
  4. [SOLVED] trouble filtering a list. Why isn't column filtering?
    By Anne Troy in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2005, 03:05 PM
  5. [SOLVED] trouble filtering a list. Why isn't column filtering?-number of criteria
    By Pat in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-18-2005, 11:05 AM

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