Advanced filtering problem

M

matangola

New member
Joined
Jul 28, 2015
Messages
7
Reaction score
0
Points
0
Lets say I have the following table:

ABC
1110
2011
3316
4022
5523

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:
New1New2
03
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:
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.
 
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
 
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)
 
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
 
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?
 
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:
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?
 
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.
 
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 : )
 
Ok then: It is actually a simplified example of a high throughput data set where the C column is filled with numbers that are scores of something.
This data should be rearranged the way I explained but in a new different table.

So I would like to achieve three things:

1. new arrangement of the data that should look like the "new1, new2" columns.
2. The original matrix should stay the same for the sake of scoring.
3. If we will change a value (score) in the C column it will automatically be changed in our new table.
 
attachment.php

It should look like that
 

Attachments

  • filter problem.png
    filter problem.png
    36.5 KB · Views: 15
You must log in or register to reply here.
Back
Top