remove duplicate values from two spreadsheets in excel

Question

I have got two different spreadsheets with some duplicate emails in it but maybe with different information in other cells of the same line. Both of the spreadsheets contain more than 10k lines. I succeeded in filtering out the duplicates by extracting the relevant data in a separate spreadsheet but i do not know how to delete the whole corresponding line of one of my original spreadsheets

spreadsheet. as an example:

spreadsheet 1:

1 a [e-mail]

2 c [e-mail]

3 f [e-mail]

spreadsheet 2:

1 b [e-mail]

2 f [e-mail]

3 c [e-mail]

then spreadsheet 2 should remain:

1 b [e-mail]

Answer 1

when you remove duplicate values, only the values in the range of cells or table are affected. Any other values outside the range of cells or table are not altered or moved.Because you are permanently deleting data, it's a good idea to copy the original range of cells or table to another worksheet or workbook before removing duplicate values.

So this is what you do; Select the range of cells, or make sure that the active cell is in a table. On the Data tab, in the Data Tools group, click Remove Duplicates.

Data Tools

Then choose one or more of the following: - Under Columns, select one or more columns. - To quickly select all columns, click Select All. - To quickly clear all columns, click Unselect All. If the range of cells or table contains many columns and you want to only select a few columns, you may find it easier to click Unselect All, and then under Columns, select those columns.

Then Click OK. A message is displayed indicating how many duplicate values were removed and how many unique values remain, or if no duplicate values were removed.

Then Click OK.

Hey tell me how it goes for you.

All the best

Answer 2

In Sheet 2 Column B write this Formula,

=COUNTIF(Sheet1!$A:$A, A1).

Then Drag it down till is necessary. You find 0 and 1, where 0 is for unique and 1 is for Duplicates. Then Filter the 1 and Delete.

Other you can try in Sheet 2 is,

=if (ISNA(Vlookup(Sheet1!A2,Sheet2!$A$2:$B$100,1,FALSE)), "Not Found", "Found").

Drag it down also. Here Found are Duplicate and Not Found are unique. Finally Filter Found and Delete.

NB: Both the Solutions are match only one Column to find Duplicate. If you feel I can suggest you VBA Code to delete Duplicates while comparing two Data Range for faster process.

I do hope this help you.