Remove Duplicate Row IF Older

[BrutalDawg]

Good morning,

I have a situation where I am trying to remove only the older of duplicate rows. Matching Duplicates would be based on three criteria, Column A which is text, column D which is text, and column G which is formatted as mm/dd/yyyy. Column B would decide which is older formatted as mm/dd/yyyy. Such As



Ohio
1/29/2017
1001
81241
Firm
120
2/7/2017
Delete


Ohio
2/1/2017
1001
81241
Firm
135
2/7/2017
Leave


GA
1/29/2017
1002
81241
Firm
50
2/7/2017
Delete


GA
1/29/2017
1002
81340
Firm
50
2/7/2017
Leave


GA
2/1/2017
1002
81241
Firm
55
2/7/2017
Leave


GA
2/1/2017
1002
81340
Firm
50
2/14/2017
Leave


GA
1/29/2017
1002
81241
Firm
100
2/14/2017
Leave


Ohio
2/1/2017
1001
81241
Firm
100
2/14/2017
Leave




Any ideas?

[JBeaucaire]

Sort the data using those 3 columns so that rows that are wanted to remain appear above the others. Then use the built in DATA > REMOVE DUPLICATES feature flagging all 3 columns are relavant.

[Paul_Hossler]

Maybe something like this




Option Explicit

Sub Macro1()
Dim r As Range, r1 As Range

With ActiveSheet
Set r = .Cells(1, 1).CurrentRegion
Set r1 = .Cells(2, 1).Resize(r.Rows.Count - 1, r.Columns.Count)

With .Sort
.SortFields.Clear
.SortFields.Add Key:=r1.Columns(2), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
.SetRange r
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

r.RemoveDuplicates Columns:=Array(1, 4, 7), Header:=xlYes
End With

End Sub

[jrb]

Cross Post (http://www.excelforum.com/excel-programming-vba-macros/1173719-remove-duplicate-row-if-older.html#post4581694)