Search code examples
excelduplicatesexcel-2007

Delete duplicate items in Excel (including original value)

How to delete duplicate items in excelsheet(column), where it has to delete the items which has more than one occurrence:

1  
2  
3  
3  
4  
4

If we use remove duplicate option, it will give distinct values, but what should be done to get only values 

1  
2

Since 1 and 2 values are not duplicated and these two have only one occurance in a Excel(column)

Solution

  • Follow Below Steps.

    1. Consider you have data in column A

    2. Write formula as =IF(COUNTIF(A:A,A1)=1,0,1) in column B.

    3. Apply Step 3 for all rows that are there.

    4. Wherever you have duplicate data, you will see 1 in column B else you will see value as 0. :)

    5. Go To menu Data and apply filter for 1. Those are the rows that are duplicate. Want to delete it?? Delete it :)

    Here is the demo