Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate check | Excel Worksheet Functions | |||
duplicate check | Excel Worksheet Functions | |||
How can I check for duplicate $'s? | Excel Worksheet Functions | |||
Check to see if variable is duplicate. | Excel Programming | |||
Check for duplicate entries | Excel Programming |