# Thread: Advanced filtering problem

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  Reply With Quote

2. 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.  Reply With Quote

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  Reply With Quote

4. 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)  Reply With Quote

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  Reply With Quote

6. 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?  Reply With Quote

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.  Reply With Quote

8. 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?  Reply With Quote

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.  Reply With Quote

10. 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 : )  Reply With Quote

#### Tags for this Thread

if function, index-match, subtotal, vba, vlookup #### Posting Permissions

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