Biff,
I will digest your most recent formula (with a twist) later on and I am sure I'll learn something. Right now, I want to say this.
I am very glad that you have thought of blanks and changed the formula from SUMPRODUCT/COUNTIF to COUNTA/FREQUENCY. In the last couple of days, I have been playing with true blanks, zeros, null strings etc. and I have found subtle difference between COUNTBLANK and ISBLANK, difference between COUNTIF with "" as criterion and A1 (a blank cell never touched) as criterion etc. etc. It can be confusing.
I included your SUMPRODUCT/COUNTIF formula as part of my experiment and I am not sure if there is a bug with MS evaluate formula in terms of null strings. The details can be found under the following thread in a post with a date/time-stamp of 10/20/26 4:20 p.m.
http://tinyurl.com/yavg5y
I am including the link here in case anyone is interested. I know you are probably busy.
I find something interesting in the following formula.
=IF(COUNTA(A1:A10),MAX(FREQUENCY(A1:A10,A1:A10))1 ,"")
COUNTA will count null strings ("") but not true blanks ("=") whereas FREQUENCY ignores both null strings and true blanks. There shouldn't be any conflict in the above formula, but I should keep in mind the difference for future reference.
Just want to share with you what I have learned. Thank you for reading.
Hope the original poster don't mind me dropping by.
Epinn
"Biff" wrote in message ...
Dang!
I'll get it right one of these times!!!!!
=IF(COUNTA(A1:A10),MAX(FREQUENCY(A1:A10,A1:A10))1 ,"")
TRUE means there are dupes
FALSE means there are no dupes
Biff
"Biff" wrote in message
...
This is more robust:
=IF(COUNTA(A1:A10),MAX(FREQUENCY(A1:A10,A1:A10))=1 ,"")
With the first formula I was assuming ALL the cells in the range would
ALWAYS have something in them.
Biff
"Biff" wrote in message
...
Try this:
=SUMPRODUCT(COUNTIF(A1:A10,A1:A10))10
FALSE = no dupes
TRUE = dupes
Biff
"Tetsuya Oguma" wrote in message
...
Hi all,
I have a range, say, A1:A10, and want to check if any duplicate entry
exists
within this range.
What is the formula that returns True/False to do that?
Simple enough?
Tetsuya Oguma