Ayansane
asked on
Excel Duplicates in File
Tech Analysts,
Looking for a SubRoutine that can help:
1 Identify duplicates in the attached file
Count duplicates
2 Combine Lines that are duplicates.... in a a different worksheet
Elections-looking-for-dups-10192015.xlsx
Looking for a SubRoutine that can help:
1 Identify duplicates in the attached file
Count duplicates
2 Combine Lines that are duplicates.... in a a different worksheet
Elections-looking-for-dups-10192015.xlsx
first of all, how do you define "duplicates" ? which columns are used for comparison?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
to copy the duplicate rows to another sheet, you probably need this:
Sub copyDupRows()
Dim lastRow As Long
Dim r As Range
Dim ws As Worksheet
Application.ScreenUpdating = False
Application.EnableEvents = False
ActiveSheet.Copy After:=Sheets(Sheets.Count)
Set ws = Sheets(Sheets.Count)
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
ws.Range("$F$2:$F$" & lastRow).Cells.FormulaR1C1 = _
"=COUNTIFS(C1,""=""&RC[-5],C2,""=""&RC[-4],C3,""=""&RC[-3],C4,""=""&RC[-2],C5,""=""&RC[-1])"
ws.Range("$A$1:$F$" & lastRow).AutoFilter Field:=6, Criteria1:="=1", _
Operator:=xlOr, Criteria2:="="
Set r = ws.Range("A2:A" & lastRow).SpecialCells(xlCellTypeVisible)
r.EntireRow.Delete
ws.Columns("F").EntireColumn.Delete
ws.AutoFilterMode = False
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Elections-looking-for-dups-10192015.xlsm
ASKER
Hey Ryan,
It seems that we are comparing two sets of rows each time...
Can we get it to highlight the duplicate..?
Also, your final subset has only "four" lines.. on the new sheet . What are those..? Thx
Best,
Hans
It seems that we are comparing two sets of rows each time...
Can we get it to highlight the duplicate..?
Also, your final subset has only "four" lines.. on the new sheet . What are those..? Thx
Best,
Hans
To highlight the duplicate entries, you probably can use Conditional Formatting with following formula:
>>Also, your final subset has only "four" lines.. on the new sheet . What are those..?
what do you mean by that?
Elections-looking-for-dups-10192015.xlsm
=COUNTIFS($A:$A,"="&INDIRECT("A"&ROW()),$B:$B,"="&INDIRECT("B"&ROW()),$C:$C,"="&INDIRECT("C"&ROW()),$D:$D,"="&INDIRECT("D"&ROW()),$E:$E,"="&INDIRECT("E"&ROW()))>1
>>Also, your final subset has only "four" lines.. on the new sheet . What are those..?
what do you mean by that?
Elections-looking-for-dups-10192015.xlsm