here is a macro that will select all duplicates. It differs from
other approaches, which mostly rely on the COUNTIF function, because it
1) ignores the first instance (which I think can't be called a duplicate)
2) works on multiple selections (may not be interesting for you, but I need
that functionality in some cases).

Cheers,

Joerg Mochikun



Sub SelectDuplicates()
'Macro selects all duplicates (=second and more instances of same text or
'value) either within selection or
'(if only 1 cell selected) in whole sheet.
'Hidden cells are ignored, selection of multiple ranges is supported
'Macro uses Regular Expressions and therefore requires a recent version of
'Windows Scripting Host to be installed
Dim rng, MyArea As Range
Dim TotalCellValues, SearchString, i As String
Dim RegExp As Object
Set RegExp = CreateObject("VBScript.RegExp")
i = 0
If Selection.Cells.Count = 1 Then
Set rng = ActiveSheet.UsedRange
Else
Set rng = Intersect(ActiveSheet.UsedRange, Selection)
End If
For Each cell In rng
If cell.EntireRow.Hidden Or cell.EntireColumn.Hidden Then GoTo
EndForLoop
SearchString = "@" & cell.Value & "@"
TotalCellValues = TotalCellValues & "@" & cell.Value & "@"
RegExp.Pattern = "(@" & cell.Value & "@.*){2,}"
If RegExp.test(TotalCellValues) Then
If MyArea Is Nothing Then Set MyArea = cell
Set MyArea = Union(MyArea, cell)
End If
EndForLoop:
Next cell
If MyArea Is Nothing Then
MsgBox "No duplicates found!"
Else
MyArea.Select
End If
End Sub

wrote in message
ups.com...
Dear Experts,
I have a column contain the name of patients (text format) some names
appears more than on time, how can I identify the repetition cases
and
than deleted?
Thanks.