Subscribe!

There are many occasions when you may need to remove rows that contain duplicate information. There are two quick and easy methods to accomplish this.

Methods for Removing Duplicates:

  1. Autofilter
  2. The & operator 
     

To use method 1:
Select the list of data where you would like to delete duplicate rows (including headers). In the data menu select “advanced filter”, choose “filter the list,in place” and “unique records only”. Your data will now be filtered to only show unique rows. Next, go to edit and click on office clipboard. Copy the filtered list. Go back to data=>filter and choose show all. Delete the original list and paste the filtered list you previously copied.

To use method 2:
Assume the info you are testing for duplicates is in columns A and B, rows 1 through 10. In cell C1 enter the formula = A1&B1. The result will be a combined version of A and B in C1. Drag this formula down through all your rows. In D1 add the formula =IF(COUNTIF($C$1:C1,C1)>1,”Duplicate”,”Unique”) and drag down through all your rows. Copy=>paste special=>values column D and then sort and delete rows that contain the word “duplicate”.


Joseph Harris

Excel Help
Analyst Help

Related posts:

  1. Delete Duplicate Rows Using Advanced Filter – Excel There are many ways to remove duplicate rows in Excel,...
  2. Removing Macros from a Workbook Q: How do I remove all the macros from my...
  3. Paste Special – Transpose Excel Transpose...
  4. “Value” Excel Workbooks to lock in Values There are times when it is useful to remove all...
  5. Removing #N/A error in Excel using ISNA() The #N/A error happens most often when a lookup function...

This entry was posted on Tuesday, December 2nd, 2008 at 1:48 pm and is filed under Excel Hints. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply

You must be logged in to post a comment.