# Remove Duplicate Rows Based on Specific Columns and Oldest Date

#### Stroobs

##### New Member
Hi,

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

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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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!

Replies
1
Views
270
Replies
41
Views
686
Replies
5
Views
417
Replies
1
Views
307
Replies
6
Views
468

1,219,769
Messages
6,150,163
Members
450,937
Latest member
kattyg261

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

### Which adblocker are you using?

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

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