Comparing 2 Columns

[lienlee]

I want to compare to columns. For example,
A and B. and in Column C it will show all the data that A doesnt have in B.

So B is the complete source data and A is a new data that may have the same data as B. But i want to see all the new data that A has in Column C

Note: the column A,B,C may be changed, so a code that can switch between columns would be appreciated.

Thanks for your reply and help

[Bob Phillips]

TRy this ARRAY formula

=IF(ISERROR(SMALL(IF(NOT(ISNA(MATCH($A$1:$A$20,$B$1:$B$20,0))),ROW($A$1:$A$ 20)),ROW(A1))),"",
INDEX($A$1:$A$20,SMALL(IF(NOT(ISNA(MATCH($A$1:$A$20,$B$1:$B$20,0))),ROW($A$ 1:$A$20),""),ROW(A1))))

[MSTlxtHelper]

Would this work as well?

=IF(ISNA(VLOOKUP(B1,$A$1:$A$10,1,FALSE)),B1,"Has it!")

If you type the formula above into column C and change the ranges as needed then fill down column C will contain the values that A doesn't have a string where the value is in both.

Were you looking for a VBA procedure that could do the same thing? Or without the string values?

[lienlee]

TRy this ARRAY formula

=IF(ISERROR(SMALL(IF(NOT(ISNA(MATCH($A$1:$A$20,$B$1:$B$20,0))),ROW($A$1:$A$ 20)),ROW(A1))),"",
INDEX($A$1:$A$20,SMALL(IF(NOT(ISNA(MATCH($A$1:$A$20,$B$1:$B$20,0))),ROW($A$ 1:$A$20),""),ROW(A1))))
I seem to get an error with that #value

[lienlee]

Would this work as well?

=IF(ISNA(VLOOKUP(B1,$A$1:$A$10,1,FALSE)),B1,"Has it!")

If you type the formula above into column C and change the ranges as needed then fill down column C will contain the values that A doesn't have a string where the value is in both.

Were you looking for a VBA procedure that could do the same thing? Or without the string values? A VBA procedure is fine as long as i can change the columns to fit any column i wish.

What you gave me im a bit confused
When its false..does that mean its in A and Not B? "Has it"?
and when it exists in both you print out B1?

[Bob Phillips]

I seem to get an error with that #value

Did you array-enter it?

[lienlee]

Did you array-enter it?
how do i do that?

[austenr]

Bob, what does SMALL do?

[austenr]

If you are going to be switching rows like that, named ranges might make it more managable.

[Bob Phillips]

Bob, what does SMALL do?

Hi Austen,

The formula builds an array of all matching rows, and the SMALL(....,ROW(An)) picks them off one by one as the ROW is incremented.

As an example, say that column A has the values A, B, C, D, E, and let's say column B has A, C, E.

This part of the formula

IF(NOT(ISNA(MATCH($A$1:$A$20,$B$1:$B$20,0))),ROW($A$1:$A$20))

evaluates to the array of values

{1;FALSE;3;FALSE;5;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FA LSE;FALSE;FALSE;FALSE;FALSE;FALSE}

taking SMALL(...,ROW(A1)), SMALL(...,ROW(A2)), etc. we pick out those row numbers, 1,3,5, which we feed in turn to the INDEX function to get the non-matching values.

[austenr]

Impressive. You learn something new every day on here.

[MSTlxtHelper]

"Has it" is a dummy string to tell you it is in both.

If the value is not in column A the formula returns the value in column B.