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.

Hi Omar,

For a variety of techniques for identifying duplicate
list values, see Chip Pearson at:

Duplicate And Unique Items In Lists
http://www.cpearson.com/excel/duplic...tingDuplicates


---
Regards,
Norman


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.

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?

Lets assume your names are in column A and start on row 1 - thatis,
the first name is in A1.

In cell B1 - insert the following formulae making sure to get the $s
in the right place;

=COUNTIF($A$1:$A1,A1)

Copy this formulae all down column B as far as the names in column A.

This formulae counts the number of occurences of the name in column A
up until the current row. That means if "A Smith" appears four times
then its 1st occurence will have "1" in column B, its 2nd occurence
will have "2" and so on.

Now all you need to do is use auto-filter to isolate all the names
with "1" in column B, copy and paste this set to a new sheet or range
and you have your list.

Keep in mind that this wont solve the problem of the same entry being
entered twice but differently i.e. "P Smith" vs "Smith, P"

Chrisso

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.