Remove Duplicate Rows Based on Specific Columns and Oldest Date

Stroobs

New Member
Joined
Feb 22, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have a spreadsheet of data, sorted by columns A, D and E with E sorted newest to oldest.

1645546909984.png

I then want to check columns A to D for duplicates and for any duplicates found I want to delete the complete row that has the oldest date in column E. In the example above I want to keep all rows highlighted in green and delete the rows in yellow.

I've tried concatenating columns A to D and then deleting the oldest row based on the date in column E however it just deletes the oldest duplicate date in columns A to E and leaves everything in columns F to I so if I started with 100 rows I end up with 50 rows in columns A to E and 100 rows still left in the adjacent cells.

Hopefully someone is able to help.

Thanks.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

trunten

Active Member
Joined
Jul 26, 2011
Messages
497
If vba is ok this should do the trick

VBA Code:
Sub dedupe()
    Dim a, d, k, v, i&, r As Range
   
    Set r = Range("A1:I100") '<-- Change reference accordingly
   
    a = r.Value
    Set d = CreateObject("Scripting.Dictionary")
    d.CompareMode = vbTextCompare
    For i = UBound(a) To 1 Step -1
        k = a(i, 1) & a(i, 2) & a(i, 3) & a(i, 4)
        If k <> "" And Not IsEmpty(a(i, 5)) Then
            If Not d.exists(k) Then
                d.Add k, Array(a(i, 5), i, 1)
            Else
                v = d(k)
                v(2) = v(2) + 1
                If a(i, 5) < v(0) Then v(1) = i
                d(k) = v
            End If
        End If
    Next i
    For Each v In d.items
        If v(2) > 1 Then r.Rows(v(1)).EntireRow.Delete
    Next v
    Set d = Nothing
    Erase a
End Sub

make sure you run it on a copy of your data first though, to check you're happy!
 

Forum statistics

Threads
1,176,264
Messages
5,902,229
Members
434,952
Latest member
AlanN

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top