#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 852
Default Dupes in a range

Very clever! Works perfect.

Thanks, Claus.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,182
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
No dupes Eric Excel Programming 3 February 20th 09 09:23 PM
macro for dupes shaji Excel Discussion (Misc queries) 2 June 17th 08 04:51 PM
No dupes Eric Excel Programming 5 April 4th 08 07:09 PM
Checking for Dupes TKnTexas Excel Discussion (Misc queries) 4 November 3rd 06 03:43 AM
Marking Dupes GregR Excel Programming 7 January 10th 06 03:17 AM


All times are GMT +1. The time now is 02:37 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"