Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

How Do I Find Duplicates in Two Columns

Latest post Mon, Jun 16 2008 12:02 PM by Ma T. 11 replies.
  • Sat, Jun 14 2008 8:21 PM

    • Ma T
    • Top 150 Contributor
    • Joined on Sun, Jun 15 2008
    • Posts 6
    • Points 126

    How Do I Find Duplicates in Two Columns

     Hi,

    I have 2 columns, A & B.  A contains 455 rows, B contains 900 rows.  I need to identify the records in Column A that do not appear in Column B.  I would like to show them in Column C. What is the best, simplest way to do this?

    Thank You

    • Post Points: 37
  • Sun, Jun 15 2008 1:14 AM In reply to

    • Simey
    • Not Ranked
    • Joined on Sun, Jun 15 2008
    • Melbourne, Australia
    • Posts 1
    • Points 21

    Re: How Do I Find Duplicates in Two Columns

    In column C and do a =or(exact([cell to check in column a],[range of column b])) and enter the formula as an array (Control, Shift Enter). This will provide a True / False answer. Then I guess do a final sort to find the false ones which do not match.

     

    • Post Points: 21
  • Sun, Jun 15 2008 3:27 AM In reply to

    • Nick Hodge
    • Top 10 Contributor
    • Joined on Sun, Dec 23 2007
    • Southampton
    • Posts 543
    • Points 9,415
    • MVP

    Re: How Do I Find Duplicates in Two Columns

    Ma T

    I would use a combination of IF and COUNTIF in Column C. So in C1 enter

    =IF(COUNTIF($B$1:$B$900,A1)=0,A1,"")

    and copy down to C455. This will show only those records that do not appear in column A. You can sort them to bring them together. (If there are dupes in Column A. This will show the name multiple times in C)

    Regards
    Nick Hodge
    Microsoft MVP, Excel
    Southampton, UK

    • Post Points: 21
  • Sun, Jun 15 2008 9:49 AM In reply to

    • Ma T
    • Top 150 Contributor
    • Joined on Sun, Jun 15 2008
    • Posts 6
    • Points 126

    Re: How Do I Find Duplicates in Two Columns

     Hi Nick - I apologize if I am misunderstanding your response, but I am looking for the records in Column A that do not appear in Column B.  In your answer you say "This will show only those records that do not appear in column A." 

     I applied your formula hoping to find blanks in Column C to identify the unique records in Column A, but there is only one blank cell, and I know there are 13 records in Column A that are not in Column B, because I went through them manually (for work).  I am looking for the formula so that  the next time I have to do this task I can use the same formula and have Excel do the work for me ! 

    • Post Points: 21
  • Sun, Jun 15 2008 10:08 AM In reply to

    • Ma T
    • Top 150 Contributor
    • Joined on Sun, Jun 15 2008
    • Posts 6
    • Points 126

    Re: How Do I Find Duplicates in Two Columns

     Hi Simey - Thanks for your response, but it didn't produce the results I need.  I did not create the original spreadsheet, although I do know that the data was exported from other sources.  When I tried to enter the formula as an array, I got an error msg that said I couldn't apply it to merged cells, so I altered it to say " =OR(EXACT(A2,$B$2:$B$900)) and then copied it down, and the results showed everything as FALSE.

    I do know there are 13 records in Column A that do not appear in Column B because I went through them manually (for work).  I would like to get the formula to work because I want to be able to use it again in the future when I have to compare two lists of records in the next report.

    • Post Points: 5
  • Sun, Jun 15 2008 10:33 AM In reply to

    Re: How Do I Find Duplicates in Two Columns

    Just reverse Nick's conditions

     

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

    Regards

    Bob

    • Post Points: 21
  • Sun, Jun 15 2008 1:23 PM In reply to

    • Ma T
    • Top 150 Contributor
    • Joined on Sun, Jun 15 2008
    • Posts 6
    • Points 126

    Re: How Do I Find Duplicates in Two Columns

    Hi Bob,

    Thanks, but it's still not working........Sad .  I'm getting way more records than the 13 I know are not dup's. (and Column C rows are twice as high as A & B.)

    I'm thinking that I'm not explaining myself properly, so I'll give it another shot.  I have 635 rows in Col A.  I have 900 rows in Col B.  Having done it manually, I know there are 13 records (rows) in Col A that are not in Col B, and those 13 are the ones I would like to see in Col C.

    I know there are duplicates in Col A, but if the formula identifies them both individually, I don't care.  It's still much faster to analyze, than all 635.

    Below is a sample of the results I got using the formula you provided, if it helps to make more sense.   Thanks again.

    87RF-21200-0102-001-GA-F  87RF-00065-TSW-001                       87RF-21200-0102-001-GA-F 
    87RF-21200-0102-001-GA-F  87RF-01340-CSS-529L9274                 
    87RF-21200-0116-001-GA-F  87RF-01340-CSS-542L0001                  87RF-21200-0116-001-GA-F 
    87RF-21200-TS-001  87RF-01340-CSS-542L0002                 
    87RF-21200-TS-001  87RF-01340-CSS-542L0003                  87RF-21200-TS-001 
    87RF-21203-1001-001-DD-D  87RF-01340-CSS-542L0004                 
    87RF-21203-1001-001-DD-D  87RF-01340-CSS-542L0005                  87RF-21203-1001-001-DD-D 
    87RF-21203-1002-001-DD-D  87RF-01340-CSS-542L0006                 
                 

    87RF-21203-1003-001-DD-D  87RF-01340-CSS-543L0002                 

    • Post Points: 21
  • Sun, Jun 15 2008 7:15 PM In reply to

    Re: How Do I Find Duplicates in Two Columns

    Try the first formula in C1 and this in C2 and down

     

    =IF(OR(COUNTIF($B:$B,$A2),COUNTIF(C$1:C1,$A2)<>0),"",$A2)

    Regards

    Bob

    • Post Points: 21
  • Sun, Jun 15 2008 10:21 PM In reply to

    • Ma T
    • Top 150 Contributor
    • Joined on Sun, Jun 15 2008
    • Posts 6
    • Points 126

    Re: How Do I Find Duplicates in Two Columns

    Hi Bob,

    Nope, still not working..........[:'(] .  Here's a sample of the results.  Thanks once again for your help.

    87RF-21200-0102-001-GA-F  87RF-00065-TSW-001                       87RF-21200-0102-001-GA-F 
    87RF-21200-0102-001-GA-F  87RF-01340-CSS-529L9274                 
    87RF-21200-0116-001-GA-F  87RF-01340-CSS-542L0001                 
    87RF-21200-TS-001  87RF-01340-CSS-542L0002                 
    87RF-21200-TS-001  87RF-01340-CSS-542L0003                  87RF-21200-TS-001 
    87RF-21203-1001-001-DD-D  87RF-01340-CSS-542L0004                 
    87RF-21203-1001-001-DD-D  87RF-01340-CSS-542L0005                  87RF-21203-1001-001-DD-D 
    87RF-21203-1002-001-DD-D  87RF-01340-CSS-542L0006                 
    87RF-21203-1002-001-DD-D  87RF-01340-CSS-543L0001                  87RF-21203-1002-001-DD-D 
    87RF-21203-1003-001-DD-D  87RF-01340-CSS-543L0002                 
    87RF-21203-1004-001-DD-D  87RF-01340-CSS-543L0003                  87RF-21203-1003-001-DD-D 
    87RF-21203-7026-001-HL-B  87RF-01340-CSS-543L0004                 
    87RF-21203-7026-001-HL-B  87RF-01340-CSS-543L0005                  87RF-21203-7026-001-HL-B 
    87RF-21203-BMN-1001  87RF-01340-CSS-543L0006                 
    87RF-21203-BMN-1001  87RF-01340-CSS-546L1226                  87RF-21203-BMN-1001 
    87RF-21261-7013-001-DD-E  87RF-10230-TS-001                       
    87RF-21261-7031-001-GA-E  87RF-20030-2001-020-RL-D                 87RF-21261-7013-001-DD-E 
    87RF-21261-7032-001-DD-E  87RF-21200-0102-001-GA-F                
    87RF-21261-7032-001-DD-E  87RF-21200-0116-001-GA-F                 87RF-21261-7032-001-DD-E 
    87RF-21261-7033-001-DD-E  87RF-21200-TS-001                       

    • Post Points: 37
  • Mon, Jun 16 2008 12:05 AM In reply to

    • Rob Bovey
    • Top 500 Contributor
    • Joined on Sat, Apr 19 2008
    • USA
    • Posts 4
    • Points 136
    • MVP

    Re: How Do I Find Duplicates in Two Columns

     This won't give you the duplicates all nicely condensed, but it should make them easy enough to pick out. Enter the formula in C1 and copy it down the length of the list in column A:

    =IF(ISERROR(MATCH(A1,B:B,0)),A1,"")

     

    Rob Bovey

    www.appspro.com

    • Post Points: 5
  • Mon, Jun 16 2008 3:07 AM In reply to

    Re: How Do I Find Duplicates in Two Columns

    I get very different results than you so it is difficult to see teh problem.

    Can you post your workbook with the formulae that you entered somewhere on a file posting site so that we can crack this?

     

    Regards

    Bob

    • Post Points: 21
  • Mon, Jun 16 2008 12:02 PM In reply to

    • Ma T
    • Top 150 Contributor
    • Joined on Sun, Jun 15 2008
    • Posts 6
    • Points 126

    Re: How Do I Find Duplicates in Two Columns

     Hello Everyone,

    I would like to thank you all for your suggestions.  I have not been able to get the results I am looking for, with any of the formulas you have each provided.  I have come to the conclusion that it is not the formula(s) that is the problem, but the data.  As I stated in an earlier response I did not create the spreadsheet, although I do know that the data was imported from other sources.  I will have to wait until the next time this needs to be done and watch the originator create the spreadsheet to see if I can determine what/where the problem lies.

    Thanks again for everyone's help.

    • Post Points: 5
Page 1 of 1 (12 items) | RSS
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.