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

Check for duplicate values?
 

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" wrote in message
...
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

Check for duplicate values?
 

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" wrote in message
...
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" wrote in message
...
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

Check for duplicate values?
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

Check for duplicate values?
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

Check for duplicate values?
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

Check for duplicate values?
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

Check for duplicate values?
 

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

Ed

"Tom Ogilvy" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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