Message Box for Duplicates
 

Hi Everyone,

I have a Column where Numbers are Entered. There can be from 5 Numbers
to 200 or so Numbers. What I would like is to Check that there is NO
Duplicates in the Column Please.

Thanks in Advance.
All the Best.
Paul

Message Box for Duplicates
 

=IF(COUNTA(A1:A200)=SUMPRODUCT((A1:A200<"")/COUNTIF(A1:A200,A1:A200&"")),
"all the same","not all the same")


=SUMPRODUCT((A1:A200<"")/COUNTIF(A1:A200,A1:A200&""))
will give the count of unique items

=count(a1:a200) will count the number of numbers.

Paul Black wrote:

Hi Everyone,

I have a Column where Numbers are Entered. There can be from 5 Numbers
to 200 or so Numbers. What I would like is to Check that there is NO
Duplicates in the Column Please.

Thanks in Advance.
All the Best.
Paul

--

Dave Peterson

Message Box for Duplicates
 

Thanks Dave,

How could I do it Using a Macro as well Please.

Thanks in Advance.
All the Best.
Paul

Dave Peterson wrote:
=IF(COUNTA(A1:A200)=SUMPRODUCT((A1:A200<"")/COUNTIF(A1:A200,A1:A200&"")),
"all the same","not all the same")


=SUMPRODUCT((A1:A200<"")/COUNTIF(A1:A200,A1:A200&""))
will give the count of unique items

=count(a1:a200) will count the number of numbers.

Paul Black wrote:

Hi Everyone,

I have a Column where Numbers are Entered. There can be from 5 Numbers
to 200 or so Numbers. What I would like is to Check that there is NO
Duplicates in the Column Please.

Thanks in Advance.
All the Best.
Paul

--

Dave Peterson

Message Box for Duplicates
 

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Columns(1), Target) Is Nothing Then
If Application.CountIf(Columns(1), Target.Value) 1 Then
MsgBox Target.Value & " already exists"
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Paul Black" wrote in message
oups.com...
Thanks Dave,

How could I do it Using a Macro as well Please.

Thanks in Advance.
All the Best.
Paul

Dave Peterson wrote:

=IF(COUNTA(A1:A200)=SUMPRODUCT((A1:A200<"")/COUNTIF(A1:A200,A1:A200&"")),
"all the same","not all the same")


=SUMPRODUCT((A1:A200<"")/COUNTIF(A1:A200,A1:A200&""))
will give the count of unique items

=count(a1:a200) will count the number of numbers.

Paul Black wrote:

Hi Everyone,

I have a Column where Numbers are Entered. There can be from 5 Numbers
to 200 or so Numbers. What I would like is to Check that there is NO
Duplicates in the Column Please.

Thanks in Advance.
All the Best.
Paul

--

Dave Peterson

Message Box for Duplicates
 

Excellent Bob.
Thanks very much.
All the Best.
Paul