Finding Duplicated Value from two different Excel file

[bad]

I need to create a formula (or marco) to find a duplicated values between two Excel files.

One file is a list of 5000 SSNs and any duplicated SSN needs to be checked against the second file which has 30,000 SSN and deleted from either the first or second file.

Any suggestions how to automatically delete the duplicated SSNs?

Dana
USA

 

[BrianB]

One easy way would be to use =VLOOKUP() in a column to find duplicate values (unique values will show #N/A). Sort on that column to get the values together, and then delete the rows.

 

[bad]

Hello Brian,

Thanks for the response. Do I have to first merge the two file and then complete the =VLOOKUP()? I don't understand how the formula works in the spreadsheet. Could you pls explain more?

Again, thanks for your assistance!
Dana
USA

 

[BrianB]

There are several ways of doing this sort of thing.
The way I have suggested is to make a new column in each file with a =VLOOKUP() formula to look up the value in the other file. If it finds it, it is a duplicate. Please see Excel Help for =VLOOKUP()

If you can add the 2 files together this is easier because you can sort on the column and have a formula in another column like :
=IF(A1=A2,1,0)
This puts a 1 in the column if a duplicate is found.
Select the column. Edit/Copy then Edit/PasteSpecial values to convert the formulas to values, then sort on the column to get all the duplicates together.