Wednesday, September 5, 2012

How to remove duplicates in one click - Excel 2007

Excel tips : There are lot of options in excel , for removing duplicates. Here is the screen shot which helps you to understand how to remove duplicates in a single column as well as in multiple column in one click . There is no need to write any script or any code. There are built in options in the Excel 2007 onward.  
The following is the sample data, here there are 4 duplicates

In the top ribbon, you can see the 5th ribbon as Data , marked in red box, click on that . Now you can see "Remove Duplicates" marked in red . I hope now you can get what you are searching for . 

Select the column that you want to remove the duplicate and click on " Remove Duplicates"
 You can see the a pop up came with "Column B" and its checked , which means its going to remove duplicates from "Column B" . Click OK 
Its done, you can see 4 duplicates is removed

Now consider , you want to select from multiple column and then remove the duplicate, Same row with same data ( What I mean is that . merging or two column and then validating the data ) 
Now there is point that you need to be care full 
a) You have to select a single range. ( I mean , DONT SELECT , Column B  and Column D separately, you have to select Column B to Column D ) . If you Select separately Column B  and Column D, you will get the 
following message . 


Click OK and then select  from Column B to Column D
b) Now click on "Remove Duplicate" 
c) Now if you want to remove Column C , as there is no data, click on the CHECK BOX  on Column C , then Click OK
 Now you can see the duplicates are removed . 
Hope you got what you required.