Return True/False to check duplicate items in a range with one for

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
Thanks for your reply.

I must add one more twist to this.

A1:A10 has the items to check duplicate and B1:B10 has "include" flags. Then
I want to consider ONLY items in Column A whose corresponding inlcude flag in
Column B is "Y"

Under the following scenario a desired formula should return FALSE, as there
is one instance of 1, 3, 6 and 7.

A B
1 1 Y
2 1
3 3 Y
4 6 Y
5 7 Y

But the formula should give TRUE under the following:

A B
1 1 Y
2 1 Y
3 3 Y
4 6 Y
5 7 Y

Can you come up with a formula?

Thanks again.

Tetsuya
 
I must add one more twist to this

No more twists! <g>

This seems to work.

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=MAX(FREQUENCY(IF((A1:A10)*(B1:B10="Y"),(A1:A10)*(B1:B10="Y")),(A1:A10)*(B1:B10="Y")))>1

TRUE means there are dupes
FALSE means there are no dupes

Biff
 
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

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
 
Clarification:

What I meant to say was it shouldn't be a problem under the circumstances even though the functions checked for blank/null strings differently.

Biff, I invite you to check the thread/link that I have previously included http://tinyurl.com/yavg5y if you are not too busy. Sandy joined in my experiment on blank, zero, null strings and have found something interesting.

"" = Blank but Blank <> ""

This is on top of Roger's and my findings that blank is treated as 0 by COUNTIF. I always appreciate your input, but of course, no obligation.

Hope you enjoy your weekend.

Epinn

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

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
 
I discovered a potential bug.

If column A contains dupe number 0's and column B=Y that formula fails. So,
use this one (array entered):

=MAX(FREQUENCY(IF((A1:A10<>"")*(B1:B10="Y"),MATCH(A1:A10,A1:A10,0)),MATCH(A1:A10,A1:A10,0)))>1

This also works on TEXT entries where the first one only worked on numbers.

Biff
 
I have been playing with true blanks, zeros, null strings etc.
and I have found subtle difference between COUNTBLANK
and ISBLANK......It can be confusing.
Click to expand...

Eh, you'll get the hang of it! Let me add to your confusion!

In A1 enter =""
In B1 enter: =ISBLANK(A1)

How's that for confusion? MS should have named that function ISEMPTY.

Biff

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

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
 
Oh, I did that and much more before I posted. This is why I say "confusing" but this is no comparison to my and Sandy's latest discovery; details under the link I posted. I am not surprised if not many people know about the discovery.

Epinn

Biff said:
I have been playing with true blanks, zeros, null strings etc.
and I have found subtle difference between COUNTBLANK
and ISBLANK......It can be confusing.
Click to expand...

Eh, you'll get the hang of it! Let me add to your confusion!

In A1 enter =""
In B1 enter: =ISBLANK(A1)

How's that for confusion? MS should have named that function ISEMPTY.

Biff

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

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
 
Back
Top