Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

migrated from Mar 8 '13 at 9:04

This question came from our site for professional and enthusiast programmers.

up vote 5 down vote accepted

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.

share|improve this answer
Remove Duplicates may not be entirely reliable see. – pnuts Mar 30 '13 at 0:38

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".

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .