How to Identify and Remove Duplicate Rows in MS Excel?
Here we need to identify which are the duplicate rows in our Excel Sheet and return all unique rows.
Let see an example, i have the data as shown in fig. 1. Lets see :
I have some duplicate rows in the data. Here,
I mark the first entry as UNIQUE and repeated entry as DUPLICATE.
Step 1: First we apply Filter. Go to DATA=> Filter
Click on the Filter of First Name=>Sort A to Z or
Click Filter of Id=>Sort Smallest to Largest.
Click "Ok".
Step 2: Now you can see the sorted data as in the fig.
Step 3: Add a column Name it Unique/Duplicate.
Now we write a formula in the cell E2.
=IF((A2=A1)*(B2=B1)*(C2=C1),"DUPLICATE","UNIQUE")
Drag this formula to entire column E. You can see the values as UNIQUE or DUPLICATE.
Step 4: We now need to remove the formula applied. Just to see the values as UNIQUE or DUPLICATE. Else you will see the formula when you go to a cell in column E.
Copy the values in Column E, Right Click=>Paste Options=>Click on the option '123'
Step 5: Now apply filter to Column E. Select only 'DUPLICATE' in check box. Click Ok.
Step 6: Now you can see that column E contains only Duplicate Values. Select all these rows and then right and click 'Delete Row'.
Step 7: Click on the filter of Column E. Select UNIQUE. Now that's all the UNIQUE values you needed.
Thank You for reading the blog!!!
Other related blog
No comments:
Post a Comment