Message Box for Duplicates

[Paul Black]

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]

=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

[Paul Black]

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

[Bob Phillips]

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" <paul_black27@hotmail.com> wrote in message
news:1139747863.241083.258850@g44g2000cwa.googlegroups.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
>

[Paul Black]

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