Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

migrated from stackoverflow.com Mar 8 '13 at 9:04

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

2 Answers

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
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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