Hansen
12-13-2016, 03:43 AM
Hello all,
I want to use a VBA macro in excel to find duplicates within a column. I want to write "Duplicate" into the row of the duplicate value, but only for the 2nd or 3rd time it appears. Here is an example:
Value
1
2
1
Duplicate
3
3
Duplicate
1
Duplicate
As you can see the first time a duplicate appears the 2nd column is empty. Only the 2nd and 3rd time the value appears, it says duplicate in the second column.
I tried doing this using the following code (I was actually trying to look for duplicates in column B):
Dim lastRow As Long
Dim matchFoundIndex As Long
Dim iCntr As Long
lastRow = Range("A65000").End(xlUp).Row
For iCntr = 1 To lastRow
If Cells(iCntr, 2) <> "" Then
matchFoundIndex = WorksheetFunction.Match(Cells(iCntr, 2), Range("B1:B" & lastRow), 0)
If iCntr <> matchFoundIndex Then
Cells(iCntr, 3) = "Duplicate"
End If
End If
Next
Dim lastRow As Integer
Dim rownumber As Integer
Dim myrange As Range
lastRow = Worksheets("Data").Cells(Worksheets("Data").Rows.Count, 1).End(xlUp).Row
rownumber = 2
Set myrange = Range("B:B")
Do While Worksheets("Data").Cells(rownumber, "A").Value <> ""
If Application.WorksheetFunction.CountIf(myrange, Worksheets("Data").Cells(rownumber, "B").Value) > 0 Then
Worksheets("Data").Cells(rownumber, "AG").Value = "Duplicate"
Else
End If
rownumber = rownumber + 1
Loop
Both the subs seem to run, but no result is showing.
As I am quite new to VBA any advice would be greatly appreciated!
Thanks!
I want to use a VBA macro in excel to find duplicates within a column. I want to write "Duplicate" into the row of the duplicate value, but only for the 2nd or 3rd time it appears. Here is an example:
Value
1
2
1
Duplicate
3
3
Duplicate
1
Duplicate
As you can see the first time a duplicate appears the 2nd column is empty. Only the 2nd and 3rd time the value appears, it says duplicate in the second column.
I tried doing this using the following code (I was actually trying to look for duplicates in column B):
Dim lastRow As Long
Dim matchFoundIndex As Long
Dim iCntr As Long
lastRow = Range("A65000").End(xlUp).Row
For iCntr = 1 To lastRow
If Cells(iCntr, 2) <> "" Then
matchFoundIndex = WorksheetFunction.Match(Cells(iCntr, 2), Range("B1:B" & lastRow), 0)
If iCntr <> matchFoundIndex Then
Cells(iCntr, 3) = "Duplicate"
End If
End If
Next
Dim lastRow As Integer
Dim rownumber As Integer
Dim myrange As Range
lastRow = Worksheets("Data").Cells(Worksheets("Data").Rows.Count, 1).End(xlUp).Row
rownumber = 2
Set myrange = Range("B:B")
Do While Worksheets("Data").Cells(rownumber, "A").Value <> ""
If Application.WorksheetFunction.CountIf(myrange, Worksheets("Data").Cells(rownumber, "B").Value) > 0 Then
Worksheets("Data").Cells(rownumber, "AG").Value = "Duplicate"
Else
End If
rownumber = rownumber + 1
Loop
Both the subs seem to run, but no result is showing.
As I am quite new to VBA any advice would be greatly appreciated!
Thanks!