| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return True/False to check duplicate items in a range with one for
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return True/False to check duplicate items in a range with one for
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return True/False to check duplicate items in a range with one for
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return True/False to check duplicate items in a range with one for
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return True/False to check duplicate items in a range with one
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 "Biff" wrote: 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return True/False to check duplicate items in a range with one
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 "Tetsuya Oguma" wrote in message ... 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 "Biff" wrote: 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return True/False to check duplicate items in a range with one for
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return True/False to check duplicate items in a range with one for
Clarification:
There shouldn't be any conflict in the above formula ..... << 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 "Epinn" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return True/False to check duplicate items in a range with one
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 "Biff" wrote in message ... 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 "Tetsuya Oguma" wrote in message ... 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 "Biff" wrote: 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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return True/False to check duplicate items in a range with one for
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. 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 "Epinn" wrote in message ... 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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return True/False to check duplicate items in a range with one for
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" wrote in message ... 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. 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 "Epinn" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
| Sumif to return a blank if sum range is blank | Excel Worksheet Functions | |||
| Duplicate Range Names by worksheet | Excel Discussion (Misc queries) | |||
| Match function...random search? | Excel Worksheet Functions | |||
| if the value of a cell in a range is not blank, then return the v. | Excel Worksheet Functions | |||
Linear Mode
