Friday, February 4, 2011

Feature on Excel 2007/2010 to Remove Duplicates

Maybe you faced this situation: you have a lot of rows in Excel with many columns and you that you are only looking for codes which are in the first column to build a useful report.

As you are very intelligent, you decide to copy the first column on another sheet to begin the construction of your desired report. However, you realize at that moment that you have indeed duplicated codes, one code you saw it on row 13 and the same on row 65 and so on.

Now, you have to look for a way to do it without wasting your time, therefore I will give a idea that have just came to mi mind:

1) Copy the column in a new sheet and apply an ascending order.

2) Use the equal operator to determine if the number on the left is equal to its predecessor. At this point you will have a TRUE or FALSE in the right cell of each number.

3) Filter the TRUE cells and remove these rows, you will keep only FALSE cells and, as you can see, there will be no duplicate codes.

But, as you are using Excel 2007/2010... why don't you use the remove duplicates button? haha. For this just select the cells you want to be with no duplicate values, go to the Data ribbon and click the "Remove duplicates" and wait until Excel do its job.

No comments:

Post a Comment