excel: check for duplicate rows based on 3 columns and keep one row

Question

This might be a bit much to ask but I'm wondering if the following is possible. this is all based on my initial question on How do I copy a range of formula values and paste them to a specific range in another sheet?

I have a sheet that contains approximately 12 columns where the same type of data is copied to which means that two or more rows could contains the exact same data, however if I could check for duplicates it would be based on 3 specific columns.

I don't want to use AutoFilter or Conditional Formatting for this task because the sheet can contain hundreds to thousands of rows and I want to be able to locate duplicates, delete them and keep just one original. I have also read other posts on here about Removing Duplicates based on column conditions but so far none has worked for me.

The Range of data is from A thru P and down and the 3 columns that would identify a duplicate are A (which is numeric, an ID), B (which is a date), and P (which is a Text string). Where this data comes from, it will always have the same format and will always be placed in the same columns which is why if A,B,P are the same, then it is considered a duplicate.

Because the ones I found, searched for values (greater than, less than, 0, etc.) some others increased the file size of my report by like 500% after running the macro. I tried quite a few but didn't not see the one you just linked. — BlueSun3k1

Dmitry Pavliv Dmitry Pavliv · Accepted Answer · 2014-02-09T15:08:16

This code works for me (lastrow based on this asnwer by @SiddharthRout):

Sub test()

    Dim lastrow As Long

    With ThisWorkbook.Worksheets("Sheet1")
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lastrow = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row
        Else
            lastrow = 1
        End If

        'Array(1, 2, 16) means 1 - for A, 2 for B and 16 for P columns
        .Range("A1:P" & lastrow).RemoveDuplicates Columns:=Array(1, 2, 16), _
            Header:=xlYes
    End With
End Sub

excel: check for duplicate rows based on 3 columns and keep one row

1 Answers