View original (French)

Does not contain, based on three criteria....

Philippe POIRET -  
Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   -
Hello,
the beginner that I am is in a deadlock...
I am using Excel 2007

I have a table (about 2500 rows). One of the columns contains data that I want to sort, or rather exclude.

If I want to extract all the rows from the table that contain (in this specific column) for example "AA", "BB", and "CC", I want to use the "does not contain" filter.
It works perfectly, but I only (to my knowledge, hence my question) have the option to specify two criteria: does not contain "AA" and does not contain "BB". If I try to filter again, after the first sort, for does not contain "CC" on the same column, it does not work.
I found a workaround, but it’s not very elegant: I duplicate the column that is being sorted first. On the first one, I remove all the AA and all the BB, and on the duplicated column, all the CC...

Is there a way to make all three exclusions at once?

I hope I was clear...
Thank you in advance

Configuration: Windows XP / Chrome 42.0.2311.90

2 answers

  1. Philippe POIRET
     
    oh yes...sorry!
    Of filter of course, since I want to exclude lines.
    1
    1. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      So why, in the list that appears when you select the list via the triangle on the column title, where everything is checked, not simply uncheck AA BB CC?
      0
      1. Philippe POIRET > Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention  
         
        AA, BB, and CC were examples for synthesis.
        The contents of the cells in this column are more complex.
        Like: "AINT VALID INIT STOP AA / ANNU "
        And maybe 300 rows later:
        "AINT VALID INIT STOP BB / CLOC "

        Etc...
        0
    2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      Vi... well when you say everything, it changes
      From what I know I can only give you a "tip" that uses an out-of-scope column
      example to adapt to your data
      in A we start at A2 with your data to process
      in C from C1 to C3 (or more if needed, there is no limit here) your texts that manage the texts in A to eliminate (even if it's partial)
      in B starting from B2 on the usable height:

      =SUMPRODUCT((COUNTIF(A2,"*"&$C$1:$C$3&"*")*1)

      this formula returns 0 for cells A that do not contain any of the texts in A and at least 1 for the others
      So all that remains is to sort by eliminating the 0s in B

      you can of course embellish it with a condition:
      =IF(SUMPRODUCT((COUNTIF(A2,"*"&$C$1:$C$3&"*")*1)=0,"yes","no")

      and even increment a numbering that will allow you to output a list of "to keep" only:

      =IF(SUMPRODUCT((COUNTIF(A2,"*"&$C$1:$C$3&"*")*1)=0,MAX($B$1:B1)+1,0)

      then just search for the numbers from 1 to .... to reissue the cleaned list
      for example in D2:

      =IFERROR(INDEX(A:A,MATCH(ROW(A1),C:C,0)),"")
      to drag down to the usable height

      come back if you need more details

      regards
      0
    3. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      ... a small example to illustrate the previous explanations might be clearer
      https://www.cjoint.com/c/EGrpvH8xODg
      0