How to delete duplicate rows in Excel?

Factual Questions
1

I have an Excel spreadsheet which I am going to be importing into a database, but I want to delete all the rows with duplicate entries. In other words, if I have the data sorted on column D, I would like to delete all rows where the contents of D are equal to the contents of D in the row above.

I know I could do this manually, but it would be time consuming and error-prone. Does anyone know of a way to do this with a function or macro?

Thanks for your help.

2

This might not help at all, but deduplication is much easier in Access than Excel (and indeed, I suspect, in any other database also) - I only mention this because you mentioned this was for import.

3

stick this formula in the next column: If(d1=d2,0,1)

Then copy that column and paste special as values

Sort by the new column descending and delete everything that has a 1

4

since it is a formula put a “=” before that

5

Thanks! That will do exactly what I want. I knew I could count on the Dopers for help.

FBG

6

shit no delete the 0’s

7

Advanced Filter > Unique Records Only

Delete Duplicate Rows

8

Personally I think it is better to learn how to use formulas for this kind of stuff - you can recycle them and vary them endlessly.

A few basic formulas, a knowledge of pivot tables and some vlookup skills allow you to do really complex stuff without knowing how Excel would like you to do it.

9

The formula approach is straightforward if you are only checking duplicates in one column (don’t ask has shown us how to do that).

But, let’s say you were checking for duplicate rows (i.e. check the combination of all columns for uniqueness), that formula could get unwieldy, but Inuendo Hunter’s method will still work regardless if the row has one column or 255.