Remove Duplicate Rows / Values

08 Apr

Getting Unique Items from a List of Cells

There are 3 simple ways to do this:

  • Using Advanced Data Filter
  • Using countif() and auto filter
  • Using formulas as described here

Assuming you have data as shown in the picture aside (and wishing you will have customers like those):

First add a column to the left of the list. Here we will use formulas to fill numbers based on the uniqueness of the cell next to it. Essentially our formula should generate numbers in increasing order as long as the corresponding item is unique and not increase the number otherwise. So the formula for order column can be like this: =IF(COUNTIF(list-upto-that-point, current element)=1,previous-order+1, previous-order)

Now add another column to the right of the list, here we will fetch unique items. We will use vlookup() to fetch each of the 12 unique items. The formula goes like this:

=VLOOKUP(running number,$B$4:$C$22,2,FALSE)

You can wrap the vlookup() with if() formula to avoid seeing #value errors. That is all. Using this method you can extract unique items from a list.

Eliminating Doubles from a List

There are 2 ways in which you can find and remove duplicates(doubles) in excel lists with ease:

  • Using countif() and then auto-filter
  • Using formulas

The process for finding duplicates using formulas is same as that of finding unique items.

Instead of writing COUNTIF(list-upto-that-point, current element)=1, we now write COUNTIF(list-upto-that-point, current element)=2. Also the first element’s count should be changed to zero.

Once done the list should look like what you see on the side.


Leave a comment

Posted by on April 8, 2012 in MS Excel, My Home


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: