how to delete duplicate entries in excel

January 22, 2012 at 01:30:06
Specs: Windows 7

In excel how to delete duplicated rows, I have long list and I want to remove duplicate entries easily because it is difficult to remove each and every one individually:

See More: how to delete duplicate entries in excel

Report •

January 22, 2012 at 07:20:37

Since you say that the rows are duplicates, I'll assume that the entries in Column A contain duplicates. We will use that to our advantage.

Assuming that the duplicate rows are on Sheet1, try this:

On Sheet2 (or any blank worksheet) use the Data...Advanced Filter feature to create a list of unique values from Sheet1 Column A. Place the filtered list in Sheet2!A1.

On Sheet3 (or any blank worksheet) use VLOOKUP to pull the data from Sheet1 by looking up the values from the filtered list on Sheet2. Your first VLOOKUP must be placed in Column A.

Once the VLOOKUP has pulled all the data into Sheet3, do a Copy...PasteSpecial...Values to remove the formulas but retain the data.

To make the VLOOKUP formula easy to autofill, use the COLUMN() function as the col_index_num argument. That will cause the col_index_num to increment by one as you drag it to the right.

e.g. In Sheet3 you might have something like this:


Drag this across as wide as your original table in Sheet1 and as far down as the Filtered List in Sheet2.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 22, 2012 at 07:48:05

If you are using Excel 2007 then there is a built in function for removing duplicates.

On the Ribbon/Task Bar
Select Data
Select Remove Duplicates

In the pop-up window ALL the columns in your sheet will be Checked,
First, Uncheck All,
then simply Check the specific column with your duplicates.


Report •

January 22, 2012 at 09:08:27

Well, that sure is a whole bunch easier than my old school method!

Of course, there's also a whole bunch less learned. ;-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

Ask Question