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 b@gmail.com

2 c d@gmail.com

3 f e@gmail.com

spreadsheet 2:

1 b a@gmail.com

2 f d@gmail.com

3 c e@gmail.com

then spreadsheet 2 should remain:

1 b a@gmail.com

  • But where are the Duplicate Mail IDs ? All six are unique !! – Rajesh S Feb 20 '18 at 12:15

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

  • maybe you misunderstood me...i have two columns of which i want to remove the duplicates and the duplicates come from two completely different lists, so there are duplicates which does not lay in one and the same line – Don Moe Feb 20 '18 at 11:48

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.

  • to drag it down is very difficult if you have 11142 lines – Don Moe Feb 20 '18 at 14:44
  • That I know,, if you can handle the VBA then I can suggest you the faster method !! – Rajesh S Feb 20 '18 at 16:01
  • Don, this is a good suggestion, I usually go the VLOOKUP route, but the COUNTIF is clever. To make copying down 11142 times easier, try hovering the mouse over the corner of the cell until the cursor changes to a plus sign. This is called the fill handle. Double click on the fill handle and excel will do the work for you. pryor.com/blog/copy-excel-formulas-down-to-fill-a-column – Ian McGowan Feb 21 '18 at 2:47

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.