Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dupes in a range
How can I make this formula respond ONLY to two-digit values in a range? (Array Entered)
=IF(MAX(COUNTIF(Data1,Data1))1,"Y","N") Where the values may also be values-stored-as-text. This returns "Y" because of the two 15's 1589 15 1789 189 4 189 2 15 6 This returns "N" ignoring the two 189's 1589 16 1789 189 4 189 2 15 6 Thanks, Howard |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dupes in a range
Hi Howard,
Am Sat, 28 May 2016 14:15:12 -0700 (PDT) schrieb L. Howard: How can I make this formula respond ONLY to two-digit values in a range? (Array Entered) =IF(MAX(COUNTIF(Data1,Data1))1,"Y","N") Where the values may also be values-stored-as-text. This returns "Y" because of the two 15's 1589 15 1789 189 4 189 2 15 6 This returns "N" ignoring the two 189's 1589 16 1789 189 4 189 2 15 6 try it with an UDF: Function TwoDigitDupes(myRng As Range) As String Dim rngC As Range For Each rngC In myRng If rngC 9 And rngC < 100 And Application.CountIf(myRng, rngC) 1 Then TwoDigitDupes = "Y" Exit Function Else TwoDigitDupes = "N" End If Next End Function Call that function in the sheet with =TwoDigitDupes(Data1) Regards Claus B. -- Windows10 Office 2016 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dupes in a range
Very clever! Works perfect.
Thanks, Claus. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dupes in a range
Hi Howard,
Am Sat, 28 May 2016 16:17:16 -0700 (PDT) schrieb L. Howard: Very clever! Works perfect. not clever enough. The line TwoDigitDupes = "N" should be out of the loop. Try: Function TwoDigitDupes(myRng As Range) As String Dim rngC As Range For Each rngC In myRng If rngC 9 And rngC < 100 And Application.CountIf(myRng, rngC) 1 Then TwoDigitDupes = "Y" Exit Function End If Next TwoDigitDupes = "N" End Function Regards Claus B. -- Windows10 Office 2016 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dupes in a range
not clever enough. The line TwoDigitDupes = "N" should be out of the loop. Try: Function TwoDigitDupes(myRng As Range) As String Dim rngC As Range For Each rngC In myRng If rngC 9 And rngC < 100 And Application.CountIf(myRng, rngC) 1 Then TwoDigitDupes = "Y" Exit Function End If Next TwoDigitDupes = "N" End Function Regards Claus B. Hi Claus, Okay, I found no fault with the first function, but I assume I did not test it thoroughly enough. I'll use the revised version. Thanks again. Howard |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dupes in a range
not clever enough. The line
TwoDigitDupes = "N" should be out of the loop. Try: Function TwoDigitDupes(myRng As Range) As String Dim rngC As Range For Each rngC In myRng If rngC 9 And rngC < 100 And Application.CountIf(myRng, rngC) 1 Then TwoDigitDupes = "Y" Exit Function End If Next TwoDigitDupes = "N" End Function Regards Claus B. Hi Claus, Okay, I found no fault with the first function, but I assume I did not test it thoroughly enough. I'll use the revised version. Thanks again. Howard Claus' 2nd version is more efficient for your intent because it exits immediately on a find instead of looping the entire range if no dupes found. This version will run faster on large ranges! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dupes in a range
Claus' 2nd version is more efficient for your intent because it exits immediately on a find instead of looping the entire range if no dupes found. This version will run faster on large ranges! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus Hi Garry, Okay, good to know. My ranges are nine cells, but the reason is well noted. Thanks. Howard |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dupes in a range
Claus' 2nd version is more efficient for your intent because it exits
immediately on a find instead of looping the entire range if no dupes found. This version will run faster on large ranges! What I meant is that the 'Else' code doesn't have to execute every iteration where no dupe is found. It just looks for dupes and exits IF found. Thus better coding, IMO! To minimize code further I'd probably write this function this way... Function TwoDigitDupes$(myRng As Range, Min&, Max&) Dim rng As Range TwoDigitDupes = "N" '//assume no dupes For Each rng In myRng If rng Min And rng < Max _ And Application.CountIf(myRng, rng) 1 _ Then TwoDigitDupes = "Y": Exit Function Next 'rng End Function ...just because it requires less typing as well as less reading to be understood by someone doing future maintenance. Also allows changing the criteria in the formula so the values are more flexible to the user. I'm not a fan of reading/writing cells directly (slower) if the range is large, so I'd likely load the data into an array and process everything in memory. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
No dupes | Excel Programming | |||
macro for dupes | Excel Discussion (Misc queries) | |||
No dupes | Excel Programming | |||
Checking for Dupes | Excel Discussion (Misc queries) | |||
Marking Dupes | Excel Programming |