0

I am using Excel 2007.

I have more than 40000 records in Sheet1.

I want to remove the duplicates.

Constraint for duplicate would be if you have the exact same fields for the entire line. This means that every line must be different.

What would happen if Remove duplicates with respect to all columns?

guna seelan
  • 103

2 Answers2

5

Data ribbon > Remove Duplicates. Click the question mark in the dialog for more help. In short: tick the columns that you want to test for duplicates.

teylyn
  • 23,615
0

A method I consider more reliable is to:

  1. ensure relevant columns are headed with labels
  2. select all the relevant columns
  3. Data > Sort & Filter - Advanced, select Copy to another location
  4. confirm List range: is correct (or adjust)
  5. Copy to: "X", tick Unique records only and OK
    where "X" is a reference to the top left cell of an unoccupied array, in the same sheet, whose size can accommodate all the data in the 'relevant' columns.
  6. Delete 'relevant' columns (if desired).

It can be advantageous that with this approach the duplicate records are not automatically deleted and it works as I would expect whereas Remove Duplicates does not for, for example, the dataset in SU Question 572226 Excel “Remove Duplicates” feature does not remove all duplicates.

Note that Copy to: must reference the same sheet so this solution is limited to a maximum of 16,384/2 columns.

An answer to your exact question therefore is "depends upon what is in your columns".

Community
  • 1
pnuts
  • 6,242