Excel Delete Duplicate Rows

Excel - simple methods how to remove duplicates

Excel - How to delete rows with duplicate values

The basic problem:

How do I find duplicate records in one column and how to automatically remove them along with the entire line, but while one record was retained?

Possible solutions:

There are several solutions, depending on how method is used and what we want to do with duplicates. In principle we have three options:

1) Use the formula
2) Use easy macro
3) Use the built-in functionality of Excel

Sample: http://eggheadcafe.com/FileUpload/667729429_HowTo_Remove_Duplicates_Sample.zip


1) Formula: Visual Indication – Conditional Formatting

Sometimes just visually distinguish values that are duplicates, is enough. To highlight first duplicated value with one color, the rest ones with other color. Here is the best to use COUNTIF formula.
(Can by used also in Open Office)


2) Tags for auto filter – Use a macro

Macro used for this example has a single task. To mark duplicated values by the tag, such as X to an adjacent column.
Next, they can be used by auto filter, to filter out the rows marked and by keyboard shortcuts
CTRL + - (minus key) duplicates can be removed at once.

Option Explicit
'crea by pc-prog.eu

Sub SelectRowOfDupli_A()
Dim x As Variant, xRng As Range, xR As Range
Dim xMltRow As String, i As Integer, xObl As String
Set xRng = Selection
x = "xxxxxx"

On Error GoTo xErr
xObl = "B"
xObl = InputBox("Enter COLUMN where by 'X' sign will be marked duplicate entries " & _
"of selected cells:", "RANGE", xObl)

If xObl = "" Then
MsgBox "Column name must be entered!", vbCritical, "CHYBA"
Else
For Each xR In xRng
If Trim(CStr(xR.Value)) = x Then
Range(xObl & CStr(xR.Row)).Value = "X"
i = i + 1
Else
x = Trim(CStr(xR.Value)) 'xR.Value
End If
Next xR
If xMltRow <> "" Then
Range(xMltRow).Select
End If
MsgBox "Done. " & CStr(i) & " duplicates."
End If
Exit Sub
xErr:
MsgBox Err.Description, vbCritical, "FINISHED WITH ERRORS:"
End Sub


3) The easiest finally – built Excel functionality

When using Excel version 2007, built functionality can be used to remove duplication.

The command is on tab Data, a group Data Tools;
icon Remove duplicates.



How simple :-)