How to Remove Duplicates from two Excel sheets

0
votes

The Problem - I have two excel sheets (in one workbook) with many hundreds of lines of data, let's say a company name (column a) and the company registration number (column b).

Some lines appear on both sheets and I wish to delete the duplications on one of the sheets only, say Sheet 2. So I am thinking to mark the duplicates with an 'X' in Column C so I can sort them and mass delete.

I'm trying to use a formula in column C that says if the 'company' number appearing in column b on sheet 2 appears in a cell range on Sheet 1, then mark the corresponding cell in column c on sheet 2 with an X.

I can then sort sheet 2 and easily delete the duplications. I think the formula in the cells in column C on Sheet2 should look like.... =IF(Sheet1!B1:B1000=B1,"X"," ") ....but it's not working.

Any suggestions please? I am not a programmer ;)

2
excelexcel-formula

2 Answers

2
votes

Use Vlookup

=if (isna(vlookup(sheet1!A2,Sheet2!$A$2:$B$100,1,FALSE)), "Not Found", "Found")

The you can filter "Not Found"

0
votes

You could also simply use the already existing feature in Excel: Data --> Data Tools --> Remove Duplicates