Excel:Find Duplicates in 2 files?

Question

Hello,
My problem with Excel is as follows - I have 2 file with 1 column with serial numbers from 9 digits. I want to compare two columns and in the 1st to come out in an additional column which serial exists in the other file. I can, if necessary, to merge the serial numbers in 1 table.

Answer 1

Let's call your two sheets "file1" and "file2". Assume the data is in column A of each sheet. Then in column B of "file1" put:



=IF(ISNA(VLOOKUP(A2,file2!A:A,1,0)),

"",VLOOKUP(A2,file2!A:A,1,0))



all in one formula.



Copy this down as far as you have data in column A. If you want, you can click Data <-Filter <-Autofilter and select Non-blank to get a compact list.

Answer 2

if you are willing to merge them to 1 sheet

Column A is File 1 starting in cell A1

Column B is File 2 starting in cell B1

then in C1 copy&paste this formula



=IF(COUNTIF(B:B,A1)>0,A1&" is in both","only in column A")



then copy C1 and paste down the column

- this will tell you if A is anywhere in B (it doesnt matter where they are in the column B list)

- you can sort by column C

and all your duplicates will sort together and the "only in column A"'s will sort to the bottom of the list. or autofilter or whatever



you dont have to merge the sheets, you can use this formula, and just reference the different files

=IF(COUNTIF([Book2.xls]Sheet1!$B:$B, A1)>0,A1&" is in both","only in column A")



and you can change whats in the quotes to say what you want.

Answer 3

try exploring excel there is a query constructor to make your life easier use it

as for the duplications



Select Serial,Count= COUNT(*) FROM table GROUP BY Serial HAVING COUNT(*) > 1;