# Excel: Need to compare two columns and output what is different **URL:** \ **Category:** Factual Questions\ **Created:** [July 2, 2010, 5:02pm UTC](https://boards.straightdope.com/t/excel-need-to-compare-two-columns-and-output-what-is-different/545320 "2010-07-02T17:02:51Z")\ **Posts on this page:** 17\ **Page:** 1
**Author:** ![Whack-a-Mole](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/whack-a-mole/32/141_2.png) [@Whack-a-Mole](https://boards.straightdope.com/u/Whack-a-Mole)\ **Post date:** [July 2, 2010, 5:02pm UTC](https://boards.straightdope.com/t/excel-need-to-compare-two-columns-and-output-what-is-different/545320/1 "2010-07-02T17:02:51Z")
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? ---
**Author:** ![TroutMan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troutman/32/6721_2.png) [@TroutMan](https://boards.straightdope.com/u/TroutMan)\ **Post date:** [July 2, 2010, 5:15pm UTC](https://boards.straightdope.com/t/excel-need-to-compare-two-columns-and-output-what-is-different/545320/2 "2010-07-02T17:15:02Z")
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. ---
**Author:** ![Chessic\_Sense](https://avatars.discourse-cdn.com/v4/letter/c/7c8e57/32.png) [@Chessic\_Sense](https://boards.straightdope.com/u/Chessic_Sense)\ **Post date:** [July 2, 2010, 5:24pm UTC](https://boards.straightdope.com/t/excel-need-to-compare-two-columns-and-output-what-is-different/545320/3 "2010-07-02T17:24:23Z")
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). ---
**Author:** ![Caldazar](https://avatars.discourse-cdn.com/v4/letter/c/8e8cbc/32.png) [@Caldazar](https://boards.straightdope.com/u/Caldazar)\ **Post date:** [July 2, 2010, 5:26pm UTC](https://boards.straightdope.com/t/excel-need-to-compare-two-columns-and-output-what-is-different/545320/4 "2010-07-02T17:26:53Z")
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,"") … ---
**Author:** ![KneadToKnow](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/kneadtoknow/32/3999_2.png) [@KneadToKnow](https://boards.straightdope.com/u/KneadToKnow)\ **Post date:** [July 2, 2010, 5:28pm UTC](https://boards.straightdope.com/t/excel-need-to-compare-two-columns-and-output-what-is-different/545320/5 "2010-07-02T17:28:55Z")
The formula I wrote for column C is: ```auto =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. ---
**Author:** ![zoid](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zoid/32/336_2.png) [@zoid](https://boards.straightdope.com/u/zoid)\ **Post date:** [July 2, 2010, 5:48pm UTC](https://boards.straightdope.com/t/excel-need-to-compare-two-columns-and-output-what-is-different/545320/6 "2010-07-02T17:48:09Z")
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 ---
**Author:** ![Whack-a-Mole](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/whack-a-mole/32/141_2.png) [@Whack-a-Mole](https://boards.straightdope.com/u/Whack-a-Mole)\ **Post date:** [July 2, 2010, 5:53pm UTC](https://boards.straightdope.com/t/excel-need-to-compare-two-columns-and-output-what-is-different/545320/7 "2010-07-02T17:53:14Z")
> [@zoid](#): > > 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 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. ---
**Author:** ![zoid](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zoid/32/336_2.png) [@zoid](https://boards.straightdope.com/u/zoid)\ **Post date:** [July 2, 2010, 5:55pm UTC](https://boards.straightdope.com/t/excel-need-to-compare-two-columns-and-output-what-is-different/545320/8 "2010-07-02T17:55:11Z")
And upon review it looks like I was oversimplifying it :smack: ---
**Author:** ![TroutMan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troutman/32/6721_2.png) [@TroutMan](https://boards.straightdope.com/u/TroutMan)\ **Post date:** [July 2, 2010, 6:03pm UTC](https://boards.straightdope.com/t/excel-need-to-compare-two-columns-and-output-what-is-different/545320/9 "2010-07-02T18:03:29Z")
> [@Whack-a-Mole](#): > > 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. 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.) ---
**Author:** ![TroutMan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troutman/32/6721_2.png) [@TroutMan](https://boards.straightdope.com/u/TroutMan)\ **Post date:** [July 2, 2010, 6:17pm UTC](https://boards.straightdope.com/t/excel-need-to-compare-two-columns-and-output-what-is-different/545320/10 "2010-07-02T18:17:31Z")
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. ---
**Author:** ![Whack-a-Mole](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/whack-a-mole/32/141_2.png) [@Whack-a-Mole](https://boards.straightdope.com/u/Whack-a-Mole)\ **Post date:** [July 2, 2010, 6:23pm UTC](https://boards.straightdope.com/t/excel-need-to-compare-two-columns-and-output-what-is-different/545320/11 "2010-07-02T18:23:53Z")
> [@TroutMan](#): > > 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.) I do not know what is happening. I did as you directed and got [this result](http://img704.imageshack.us/img704/7633/comparecolumns.jpg). 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… ---
**Author:** ![Whack-a-Mole](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/whack-a-mole/32/141_2.png) [@Whack-a-Mole](https://boards.straightdope.com/u/Whack-a-Mole)\ **Post date:** [July 2, 2010, 6:25pm UTC](https://boards.straightdope.com/t/excel-need-to-compare-two-columns-and-output-what-is-different/545320/12 "2010-07-02T18:25:11Z")
> [@TroutMan](#): > > 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. 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). ---
**Author:** ![KneadToKnow](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/kneadtoknow/32/3999_2.png) [@KneadToKnow](https://boards.straightdope.com/u/KneadToKnow)\ **Post date:** [July 2, 2010, 6:29pm UTC](https://boards.straightdope.com/t/excel-need-to-compare-two-columns-and-output-what-is-different/545320/13 "2010-07-02T18:29:51Z")
I stand by my solution. ---
**Author:** ![TroutMan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troutman/32/6721_2.png) [@TroutMan](https://boards.straightdope.com/u/TroutMan)\ **Post date:** [July 2, 2010, 6:40pm UTC](https://boards.straightdope.com/t/excel-need-to-compare-two-columns-and-output-what-is-different/545320/14 "2010-07-02T18:40:45Z")
> [@Whack-a-Mole](#): > > I do not know what is happening. I did as you directed and got [this result](http://img704.imageshack.us/img704/7633/comparecolumns.jpg). > > 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… 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. ---
**Author:** ![Whack-a-Mole](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/whack-a-mole/32/141_2.png) [@Whack-a-Mole](https://boards.straightdope.com/u/Whack-a-Mole)\ **Post date:** [July 2, 2010, 6:45pm UTC](https://boards.straightdope.com/t/excel-need-to-compare-two-columns-and-output-what-is-different/545320/15 "2010-07-02T18:45:36Z")
> [@KneadToKnow](#): > > I stand by my solution. This worked. Thanks everyone for the help! ---
**Author:** ![TroutMan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troutman/32/6721_2.png) [@TroutMan](https://boards.straightdope.com/u/TroutMan)\ **Post date:** [July 2, 2010, 6:48pm UTC](https://boards.straightdope.com/t/excel-need-to-compare-two-columns-and-output-what-is-different/545320/16 "2010-07-02T18:48:46Z")
> [@KneadToKnow](#): > > I stand by my solution. As long as the list is in alphabetical order, this works fine too. ---
**Author:** ![KneadToKnow](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/kneadtoknow/32/3999_2.png) [@KneadToKnow](https://boards.straightdope.com/u/KneadToKnow)\ **Post date:** [July 2, 2010, 6:51pm UTC](https://boards.straightdope.com/t/excel-need-to-compare-two-columns-and-output-what-is-different/545320/17 "2010-07-02T18:51:40Z")
> [@TroutMan](#): > > As long as the list is in alphabetical order, this works fine too. Good point, I should’ve specified originally. It looks like it was, so it worked out.