macro for dupes
Option Explicit
Sub DupFinder()
Dim r As Range
Dim t As Range
Dim V As String
Set t = Selection
For Each r In t.Cells
V = r.Value
If Application.WorksheetFunction.CountIf(t, Left(V, 4) & "*") 1 Then
r.Interior.ColorIndex = 3
End If
Next r
End Sub
=countif() is nice that it supports wildcards:
=countif(a1:a10,"asdf*")
will count the number of times you have entries that start with asdf in A1:A10.
shaji wrote:
Hi
I was searching for a macro to find dupes in s column and found the
following which working good also. But i want check for duplicates in the
first 4 charechers only which is a unique ID. can any one edit the same to
suite my requirement. a sample of data is given below:
0005TATA MOTORS-
0006TTK-PRESTIGE
0003ZEE-ESBE TIT
0007BAWA MASALE-
0005PLAYWIN-10
0004MPSTD-BISCOP
0635ZEE-DHOL-JAG
0032VANISH-SHINE
0010TATA MOTORS-
0004BSNL-RAP-30
the code i found is below:
Sub DupFinder()
Dim r As Range, t As Range
Set t = Selection
For Each r In t
v = r.Value
If Application.WorksheetFunction.CountIf(t, v) 1 Then
r.Interior.ColorIndex = 3
End If
Next
End Sub
thanks
--
Dave Peterson
|