Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Advanced filtering problem

  1. #1

    Advanced filtering problem



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Lets say I have the following table:

    A B C
    1 1 1 0
    2 0 1 1
    3 3 1 6
    4 0 2 2
    5 5 2 3

    I would like to create 2 new columns with the help of a formula. one column for all the values=1 in B column and
    one column for all the values=2 in B column. Those columns will return the matched values of column C by the order of column A but
    when A=0 formula would do nothing and the next calculation would be written in that same cell.
    In this example it should look like that:
    New1 New2
    0 3
    6

    I tried to use something like that (for new1):
    Code:
    =IF(AND(B1=1,A1<>0),VLOOKUP(B1,$B1:$C$5,2,FALSE))
    I dragged the formula and then I tried filtering the "FALSE" but it is affecting the original matrix.

    Please help me.
    Best
    Last edited by matangola; 2015-07-29 at 12:15 AM.

  2. #2
    Acolyte candybg's Avatar
    Join Date
    Jul 2015
    Posts
    92
    Articles
    0
    Excel Version
    2010 and 2016
    Right off it looks like you are trying to use B1:C5 as a lookup table - but you can't have the same value in B with different values in C.

  3. #3
    Actually It works as a lookup table, the problem is that where A=0 I get "False" that I'm not able to get rid of later.

    Thanks for replying

  4. #4
    Acolyte candybg's Avatar
    Join Date
    Jul 2015
    Posts
    92
    Articles
    0
    Excel Version
    2010 and 2016
    Try this in D1:
    =IF(A1<>0,IF(B1=1,C1,0),0)
    and try this in E1:
    =IF(A1<>0,IF(B1=2,C1,0),0)

  5. #5
    Ok so that does work but it still holds the same problem:
    1. value_if_false is "0" so I replaced it because 0 is also a score in C column and we won't able to get rid of the A=0 results that way (so that is fixed if I change what you wrote).

    2. you can't delete/filter the "false" results and that is my main problem. I want continuous arrays as I wrote in the example.

    Thanks

  6. #6
    Acolyte candybg's Avatar
    Join Date
    Jul 2015
    Posts
    92
    Articles
    0
    Excel Version
    2010 and 2016
    I guess I'm not clear what outcome you want. So your goal is to "filter" the FALSE, meaning the instances where A=0 and B=1 or B=2?

  7. #7
    Yes. More accurate way to put it is that I want just to pull out the C values where A is not equal "0", one column will be the C values where B=1 and the other column the C values where B=2 (exactly as the table I wrote in the example: new1 and new2).

    If there is a way to do that without the step of filtering that would be great.



    I'll just say that there's the possibility to filter the original matrix first and then using the formulas but it still creates problems later on.
    Last edited by matangola; 2015-07-29 at 01:41 AM.

  8. #8
    Acolyte candybg's Avatar
    Join Date
    Jul 2015
    Posts
    92
    Articles
    0
    Excel Version
    2010 and 2016
    So if you put a data filter on Col A, filter so you will see all EXCEPT the zeros. Does that work for you or do you need help to learn how to put a data filter on Col A?

  9. #9
    As you can see I've edited my reply because I knew you're getting there
    I've tried it but it seems to still creating a problem when dragging the formula after filtering.

  10. #10
    Acolyte candybg's Avatar
    Join Date
    Jul 2015
    Posts
    92
    Articles
    0
    Excel Version
    2010 and 2016
    I think what I would do is put in your two formulas in the 2 new columns, then filter on A to get rid of the zeros - or did you mean that you don't want a DATA FILTER?
    OR
    you could just set up a pivot table.
    Again, what is your goal with the figures that you get? How is the info used? Why are you doing this? I still feel like if I had the whole picture, I could propose another/alternate/better solution : )

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

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