Link to home
Start Free TrialLog in
Avatar of Ayansane
AyansaneFlag for Guinea

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
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

first of all, how do you define "duplicates" ? which columns are used for comparison?
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

Open in new window

Elections-looking-for-dups-10192015.xlsm
Avatar of Ayansane

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
To highlight the duplicate entries, you probably can use Conditional Formatting with following formula:
=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

Open in new window


>>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