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

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

Very clever! Works perfect.

Thanks, Claus.

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

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

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