Excel: Need to compare two columns and output what is different

Factual Questions
1

This one is giving me a headache.

I have two columns of differing lengths filled with e-mail addresses. I need to compare Column-A to Column-B and output any addresses in Column-B that are not already present in Column-A.

In Column-C I used:

=IF(COUNTIF(B:B,A1),A1,0)

This outputs addresses that are in both to Column-C…the reverse of what I want.

I also tried:

=IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),"",A1)

In that one the lists are in Column-A and Column-C and the results come in Column-B but it does not work for lists of differing lengths it seems.

Any ideas?

2

The MATCH function worked fine for me. What issue were you having with lists of differing lengths?

If you are finding results in column A that don’t exist in column C, then copy the formula in column B for each row in column A. If the list length is dynamic, you can include another check to return blank if the cell in A is blank.

3

Use Vlookup on both sides of the emails. The idea is to say “Lookup A1 in column B. If not found, return #N/A”.

Something like =vlookup(A1, B:B, 1, FALSE). Then you can just filter for all the #N/As. Those are the ones in A not found in B.

To find the ones in B not found in A, do =vlookup(B1, A:A, 1, False).

4

IF(ISERROR(MATCH…)) worked for me too:

Column A: Shorter list of values
Column B: Longer list of values
C1: =IF(ISERROR(MATCH(B1,$A:$A,0)),B1,"")
C2: =IF(ISERROR(MATCH(B2,$A:$A,0)),B2,"")
…

5

The formula I wrote for column C is:


=IF(LOOKUP(B1,A:A)=B1,"",B1)

Copy it down for all cells in column B. This looks to see if the value of the Bx cell to its left is anywhere in column A. If it is, cell Cx is blank. If it is not, cell Cx has the value of cell Bx.

The thing with LOOKUP is that it will return the next highest value if it doesn’t find an exact value, so you have to test the return of the LOOKUP function against the original value to see if they match or not to know if you actually found what you were looking for or not.

To get just the results, of course, you’ll have to do a Copy, Paste Special (Values) to column D.

6

I may be oversimplifying what you need but all I would do is put in C1: “=B1=A1”
This will return TRUE if B1 and A1 are the same and FALSE if they differ.

Then just copy the formula all the way down and filter for FALSE

7

The lists are differing lengths and not the same so a side-by-sdie comparison (A1:B1, A2:B2, etc.) will not work.

As for the MATCH function I only got returns till the end of the shorter list and I already know that what I did get was far too few addresses.

8

And upon review it looks like I was oversimplifying it :smack:

9

If Column A are B are two separate lists, put this into column C and copy for every row in column B:

=IF(ISERROR(MATCH(B1,A$1:A$10,0)),B1, “”)
where A$1:A$10 is the total length of the A list.

You then need to append Columns A and C for the complete list. This will work no matter the length of each column.

(And for the suggestion to use LOOKUP or VLOOKUP, that works fine as long as the list is alphabetized. MATCH will work whether alphabetical or not.)

10

And for the really simple approach, if you have Excel 2007, put all the addresses in a single column and click the Remove Duplicates button on the Data ribbon.

11

I do not know what is happening. I did as you directed and got this result.

Notice the first line is the same e-mail address and it did not copy over then later it copies over an address in both columns.

Confused…

12

I did that but that is a list composed of both lists. Turns out they only want the new ones on the second list (for various and unfortunately legitimate reasons).

13

I stand by my solution.

14

You’re almost there. You just need to change the range from A$1:A$10 to A$1:A$16 (or however long your list is) to cover the entire first column.

15

This worked.

Thanks everyone for the help!

16

As long as the list is in alphabetical order, this works fine too.

17

Good point, I should’ve specified originally. It looks like it was, so it worked out.