Check for duplicate values?

I've got almost 30 ranges in my worksheet. No value should be duplicated in
any of these ranges. Is there a convenient way, either with a worksheet
function or a VBA method, to monitor this for each range? Or do I need to
loop through each cell of each range on a regular basis to see if I've got
duplicates?

Ed

You are light on details. But basically, you would need to check after or
as part of a change, that the new value is not a duplicate. Chip Pearson's
provides several generic techniques which might offer some ideas you can
use:
http://www.cpearson.com/excel/duplicat.htm

another page of interest might be on utilizing events
http://www.cpearson.com/excel/events.htm

--
Regards,
Tom Ogilvy



"Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
news:%[email protected]...
> I've got almost 30 ranges in my worksheet. No value should be duplicated
in
> any of these ranges. Is there a convenient way, either with a worksheet
> function or a VBA method, to monitor this for each range? Or do I need to
> loop through each cell of each range on a regular basis to see if I've
got
> duplicates?
>
> Ed
>
>

Tom:

Thank you for the link. Chip's first formula:
=IF(MAX(COUNTIF(Range1,Range1))>1,"Duplicates","No Duplicates")
seems to do exactly what I want.

However, I'm spotty both on using arrays and using worksheet formulas in
VBA. As this is an array formula, how should I implement this? Or what
details have I left out that would allow that kind of advice?

Ed

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> You are light on details. But basically, you would need to check after or
> as part of a change, that the new value is not a duplicate. Chip
Pearson's
> provides several generic techniques which might offer some ideas you can
> use:
> http://www.cpearson.com/excel/duplicat.htm
>
> another page of interest might be on utilizing events
> http://www.cpearson.com/excel/events.htm
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> news:%[email protected]...
> > I've got almost 30 ranges in my worksheet. No value should be
duplicated
> in
> > any of these ranges. Is there a convenient way, either with a worksheet
> > function or a VBA method, to monitor this for each range? Or do I need
to
> > loop through each cell of each range on a regular basis to see if I've
> got
> > duplicates?
> >
> > Ed
> >
> >
>
>

Replace Range1 with your actual ranges, and enter it with Ctrl-Shift-Enter
together, not just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
news:[email protected]...
> Tom:
>
> Thank you for the link. Chip's first formula:
> =IF(MAX(COUNTIF(Range1,Range1))>1,"Duplicates","No Duplicates")
> seems to do exactly what I want.
>
> However, I'm spotty both on using arrays and using worksheet formulas in
> VBA. As this is an array formula, how should I implement this? Or what
> details have I left out that would allow that kind of advice?
>
> Ed
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > You are light on details. But basically, you would need to check after
or
> > as part of a change, that the new value is not a duplicate. Chip
> Pearson's
> > provides several generic techniques which might offer some ideas you can
> > use:
> > http://www.cpearson.com/excel/duplicat.htm
> >
> > another page of interest might be on utilizing events
> > http://www.cpearson.com/excel/events.htm
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> > news:%[email protected]...
> > > I've got almost 30 ranges in my worksheet. No value should be
> duplicated
> > in
> > > any of these ranges. Is there a convenient way, either with a
worksheet
> > > function or a VBA method, to monitor this for each range? Or do I
need
> to
> > > loop through each cell of each range on a regular basis to see if
I've
> > got
> > > duplicates?
> > >
> > > Ed
> > >
> > >
> >
> >
>
>

I appreciate the info, Bob. I did get that. But I'm really wanting, if
possible, to implement this through a VBA macro. I've used code like
"rngCell.Formula = " before. Is there a separate method for entering an
array formula?

Ed

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Replace Range1 with your actual ranges, and enter it with Ctrl-Shift-Enter
> together, not just Enter.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> news:[email protected]...
> > Tom:
> >
> > Thank you for the link. Chip's first formula:
> > =IF(MAX(COUNTIF(Range1,Range1))>1,"Duplicates","No Duplicates")
> > seems to do exactly what I want.
> >
> > However, I'm spotty both on using arrays and using worksheet formulas in
> > VBA. As this is an array formula, how should I implement this? Or what
> > details have I left out that would allow that kind of advice?
> >
> > Ed
> >
> > "Tom Ogilvy" <[email protected]> wrote in message
> > news:[email protected]...
> > > You are light on details. But basically, you would need to check
after
> or
> > > as part of a change, that the new value is not a duplicate. Chip
> > Pearson's
> > > provides several generic techniques which might offer some ideas you
can
> > > use:
> > > http://www.cpearson.com/excel/duplicat.htm
> > >
> > > another page of interest might be on utilizing events
> > > http://www.cpearson.com/excel/events.htm
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > >
> > > "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> > > news:%[email protected]...
> > > > I've got almost 30 ranges in my worksheet. No value should be
> > duplicated
> > > in
> > > > any of these ranges. Is there a convenient way, either with a
> worksheet
> > > > function or a VBA method, to monitor this for each range? Or do I
> need
> > to
> > > > loop through each cell of each range on a regular basis to see if
> I've
> > > got
> > > > duplicates?
> > > >
> > > > Ed
> > > >
> > > >
> > >
> > >
> >
> >
>
>

array functions are only handled by Excel. You can use the evaluate
function to have excel do this for you.

Dim res as Variant
res = Application.Evaluate("Max(Countif(A1:A20,A1:A20))")
if not iserror(res) then
if res > 1 then
msgbox "duplicates"
else
msgbox "No duplicates"
End if
else
msgbox "errors in data or forumla"
end if


If you need the range to be a variable you can use something like

s = Range("A1:A20").Address(0,0)
res = Application.Evaluate("Max(Countif(" & s & "," & s & "))")



--
Regards,
Tom Ogilvy



"Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
news:[email protected]...
> Tom:
>
> Thank you for the link. Chip's first formula:
> =IF(MAX(COUNTIF(Range1,Range1))>1,"Duplicates","No Duplicates")
> seems to do exactly what I want.
>
> However, I'm spotty both on using arrays and using worksheet formulas in
> VBA. As this is an array formula, how should I implement this? Or what
> details have I left out that would allow that kind of advice?
>
> Ed
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > You are light on details. But basically, you would need to check after
or
> > as part of a change, that the new value is not a duplicate. Chip
> Pearson's
> > provides several generic techniques which might offer some ideas you can
> > use:
> > http://www.cpearson.com/excel/duplicat.htm
> >
> > another page of interest might be on utilizing events
> > http://www.cpearson.com/excel/events.htm
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> > news:%[email protected]...
> > > I've got almost 30 ranges in my worksheet. No value should be
> duplicated
> > in
> > > any of these ranges. Is there a convenient way, either with a
worksheet
> > > function or a VBA method, to monitor this for each range? Or do I
need
> to
> > > loop through each cell of each range on a regular basis to see if
I've
> > got
> > > duplicates?
> > >
> > > Ed
> > >
> > >
> >
> >
>
>

If you mean enter it as an array formula from VBA rather than use it
directly in VBA then:

Range("B9").formulaArray =
"=IF(MAX(COUNTIF(Range1,Range1))>1,""Duplicates"",""No Duplicates"")"


watch word wrap in the email.

--
Regards,
Tom Ogilvy



"Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
news:[email protected]...
> I appreciate the info, Bob. I did get that. But I'm really wanting, if
> possible, to implement this through a VBA macro. I've used code like
> "rngCell.Formula = " before. Is there a separate method for entering an
> array formula?
>
> Ed
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > Replace Range1 with your actual ranges, and enter it with
Ctrl-Shift-Enter
> > together, not just Enter.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> > news:[email protected]...
> > > Tom:
> > >
> > > Thank you for the link. Chip's first formula:
> > > =IF(MAX(COUNTIF(Range1,Range1))>1,"Duplicates","No Duplicates")
> > > seems to do exactly what I want.
> > >
> > > However, I'm spotty both on using arrays and using worksheet formulas
in
> > > VBA. As this is an array formula, how should I implement this? Or
what
> > > details have I left out that would allow that kind of advice?
> > >
> > > Ed
> > >
> > > "Tom Ogilvy" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > You are light on details. But basically, you would need to check
> after
> > or
> > > > as part of a change, that the new value is not a duplicate. Chip
> > > Pearson's
> > > > provides several generic techniques which might offer some ideas you
> can
> > > > use:
> > > > http://www.cpearson.com/excel/duplicat.htm
> > > >
> > > > another page of interest might be on utilizing events
> > > > http://www.cpearson.com/excel/events.htm
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > >
> > > > "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> > > > news:%[email protected]...
> > > > > I've got almost 30 ranges in my worksheet. No value should be
> > > duplicated
> > > > in
> > > > > any of these ranges. Is there a convenient way, either with a
> > worksheet
> > > > > function or a VBA method, to monitor this for each range? Or do I
> > need
> > > to
> > > > > loop through each cell of each range on a regular basis to see if
> > I've
> > > > got
> > > > > duplicates?
> > > > >
> > > > > Ed
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

"Evaluate" looks like exactly the way I want to go! Thank you, Tom!

Ed

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> array functions are only handled by Excel. You can use the evaluate
> function to have excel do this for you.
>
> Dim res as Variant
> res = Application.Evaluate("Max(Countif(A1:A20,A1:A20))")
> if not iserror(res) then
> if res > 1 then
> msgbox "duplicates"
> else
> msgbox "No duplicates"
> End if
> else
> msgbox "errors in data or forumla"
> end if
>
>
> If you need the range to be a variable you can use something like
>
> s = Range("A1:A20").Address(0,0)
> res = Application.Evaluate("Max(Countif(" & s & "," & s & "))")
>
>
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> news:[email protected]...
> > Tom:
> >
> > Thank you for the link. Chip's first formula:
> > =IF(MAX(COUNTIF(Range1,Range1))>1,"Duplicates","No Duplicates")
> > seems to do exactly what I want.
> >
> > However, I'm spotty both on using arrays and using worksheet formulas in
> > VBA. As this is an array formula, how should I implement this? Or what
> > details have I left out that would allow that kind of advice?
> >
> > Ed
> >
> > "Tom Ogilvy" <[email protected]> wrote in message
> > news:[email protected]...
> > > You are light on details. But basically, you would need to check
after
> or
> > > as part of a change, that the new value is not a duplicate. Chip
> > Pearson's
> > > provides several generic techniques which might offer some ideas you
can
> > > use:
> > > http://www.cpearson.com/excel/duplicat.htm
> > >
> > > another page of interest might be on utilizing events
> > > http://www.cpearson.com/excel/events.htm
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > >
> > > "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> > > news:%[email protected]...
> > > > I've got almost 30 ranges in my worksheet. No value should be
> > duplicated
> > > in
> > > > any of these ranges. Is there a convenient way, either with a
> worksheet
> > > > function or a VBA method, to monitor this for each range? Or do I
> need
> > to
> > > > loop through each cell of each range on a regular basis to see if
> I've
> > > got
> > > > duplicates?
> > > >
> > > > Ed
> > > >
> > > >
> > >
> > >
> >
> >
>
>