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

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

Dupes in a range
 

Very clever! Works perfect.

Thanks, Claus.

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

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

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

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

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