macro for dupes
 

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

macro for dupes
 

You can do it with a formula. Note the dollar signs in front of the row.
A1:A10 is the range of data. Copy the frmula down the new column.

=SUMPRODUCT(--(LEFT(A$1:A$10,4)=LEFT(A1,4)))

"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

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