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

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.

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)

 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 ! 

 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.

Just reverse Nick's conditions

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

Hi Bob,

Thanks, but it's still not working........ .  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.

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)

Hi Bob,

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

 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,"")

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?

 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.