Excel Help

[Dubb]

I have 4 columns of text in excel, and I need to filter out values that are in both column C an D but not in either column A or B. Reading through the Vlookup help, I can't figure out how this would work - is Vlookup the right function to use for this?

Thx

 

[armstrda]

Is the text value on the same row in both columns if they were the same?

 

[Billb2]

Try the IF statement.

If("text" = "????", whatever, something else)

 

[Dubb]

nope...otherwise I'd just sort and go down it visually.

 

[armstrda]

So what are you trying to get out of this? You want a new 4 column table that has the same data with the matches between C and D removed?

 

[armstrda]

If you want a new table, do something like this:

G1 - put in =A1
H1 - put in =B1
I1 - put in this formula =IF(ISNA(MATCH(C1,D😀,0)),C1,"")
J1 - put in this formula =IF(ISNA(MATCH(D1,C:C,0)),D1,"")


Copy those down the rows. I think yoiu need the Analysis ToolPak to get the ISNA function.

 

[Dubb]

I'd like to get list of cell values that fit the requirements outlined in the OP. I think what you posted eliminates the matches between C & D, where I want to take those matches and then eliminate any that are present in either column A or B.

 

[armstrda]

oh, ok, let's try this (pretty nasty but I think it does what you want)
This first one will check the values in column C
=IF(OR(ISNUMBER(MATCH(IF(ISNUMBER(MATCH(C1,D😀,0)),C1,""),A:A,0)),ISNUMBER(MATCH(IF(ISNUMBER(MATCH(C1,D😀,0)),C1,""),B:B,0))),C1,"")
This one will check the values in column D
=IF(OR(ISNUMBER(MATCH(IF(ISNUMBER(MATCH(D1,C:C,0)),D1,""),A:A,0)),ISNUMBER(MATCH(IF(ISNUMBER(MATCH(D1,C:C,0)),D1,""),B:B,0))),D1,"")

If this isn't what you want, can you post an example spreadsheet with some examples of A-D and what you want as an output based on that?

 

[Dubb]

wow, this is alot more complicated than I thought it would be... Thanks for the effort on this.

Say we have the following setup (subbing #s for text):

Col A: 7,2,1,3,4,6

Col B: 1,2,4,6,7

Col C: 5,2,3,1,7

Col D: 2,3,5,6

so the function would take 2,3, and 5 as common between C and D, and then remove 2 and 3 as they are present in either or both A and B, leaving only 5 in the final list

Tonight when I have time I'll try to put together a way to do it in two steps.

 

[armstrda]

OK, i didn't realize you just wanted the combination. This shoudl do it for you. Put this in column E and copy down (not that efficient, but brute force works 🙂 ):

=IF(AND(NOT(OR(ISNUMBER(MATCH(IF(ISNUMBER(MATCH(C1,D😀,0)),C1,""),A:A,0)),ISNUMBER(MATCH(IF(ISNUMBER(MATCH(C1,D😀,0)),C1,""),B:B,0)))),ISNUMBER(MATCH(C1,D😀,0))),C1,"")

 

[PowerEngineer]

You could also try the COUNTIF function.

=IF(AND(COUNTIF(D😀,C1)>0,COUNTIF(A:A,C1)=0,COUNTIF(B:B,C1)=0), "KEEP","REMOVE")

Should work... 🙂

 

[armstrda]

Yep, that works too.

 

[Dubb]

Well, thanks all - that worked superbly. Much appreciated.