Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checking for Dupes | Excel Discussion (Misc queries) | |||
How can I check for dupes in more than one column in excel? | Excel Worksheet Functions | |||
Summary Sheet For Identified Dupes | New Users to Excel | |||
Dynamic subtotals and eliminate dupes | Excel Worksheet Functions | |||
How to check for dupes on 2 files, and ? about csv file ext | Excel Discussion (Misc queries) |