compare 3 columns and delete dups

[sparky101747]

i would like to know if there is a way to compare 3 columns and delete the entire row where duplicates are found. My columns that i compare are C, I, and U. All 3 columns have to match, if all three columns match on row 7 & 17 then 1 row needs to be deleted. I have uploaded a small sample file and cleared the data that is not relevant.

http://www.box.net/shared/5p8t18yh3r

Thanks again

 

[Blade Hunter]

What version of Excel?

 

[sparky101747]

Excel 2007

 

[Blade Hunter]

Here you go:

Sub RemoveDupesOn3Columns()
Dim X As Long
For X = Range("C" & Rows.Count).End(xlUp).Row To 3 Step -1
    If Application.WorksheetFunction.CountIfs(Range("$C$2:C" & X - 1), Range("C" & X), Range("$I$2:I" & X - 1), Range("I" & X), Range("$U$2:U" & X - 1), Range("U" & X)) > 0 Then Rows(X).Delete
Next
End Sub

Edit: Just changed = 1 to > 0 make sure you pick up this change.

 

[brettdj]

My Duplicate Master addin will also handle this. It may prove useful if you want to run more complex matches, ie these three columns over mutiple sheets, and/or have whitespaces or other string manipulations

You can download it from http://www.experts-exchange.com/A_2123.html


Search Option .... Row Search
Output Choices ... Delete Duplicates
Application Scope .... Range ... =$C:$C,$I:$I,$U:$U
Under 'Delete Duplicate Options' you can select to
1) delete the original row as well as any dupes
2) complete row removal (which you want for this question)

Cheers

Dave

 

[Nick_G]

Here you go:

Sub RemoveDupesOn3Columns()
Dim X As Long
For X = Range("C" & Rows.Count).End(xlUp).Row To 3 Step -1
    If Application.WorksheetFunction.CountIfs(Range("$C$2:C" & X - 1), Range("C" & X), Range("$I$2:I" & X - 1), Range("I" & X), Range("$U$2:U" & X - 1), Range("U" & X)) > 0 Then Rows(X).Delete
Next
End Sub

Edit: Just changed = 1 to > 0 make sure you pick up this change.

Can this be modified easily to copy and paste the duplicates into Sheet 2 before deleting them from Sheet 1?