Macro Needed: Deleting duplicate line items that are not the latest version

[matht1234]

Hello,

Long time on-looker, first time poster here!

I’m looking to write a Macro to delete multiple line items that pertaining to a common reference number, given that are not reflective of the latest update.

For this problem, my data is downloaded periodically from a separate system and brought into excel to work with. Within the separate system, updates are made to certain reference numbers and dumped into excel. This creates multiple rows of data for the same reference number, and the date of the update. To me, none of the old data is relevant and only the row reflecting the reference number accompanied by the latest update is useful.

The data is in the range from row 2 through 1000.
The assignment numbers are listed in column B.
The dates for these assignment numbers are listed in column D.

In summary, the macro should output no duplicate rows (categorized by reference number) and be accompanied by the date with the latest update (most recent date).

Any help is appreciated.</SPAN></SPAN>

Thanks in advance! You guys are great!

 

[texasalynn]

could you post a small sample of your data

 

[matht1234]

[TABLE="class: grid, width: 689"]
<TBODY>[TR]
[TD]Data Field A</SPAN>[/TD]
[TD]Assignment Number</SPAN>[/TD]
[TD]Data Field B</SPAN>[/TD]
[TD]ISSUED-DATE</SPAN>[/TD]
[TD]Data Field</SPAN>[/TD]
[TD]Data Field</SPAN>[/TD]
[TD]Data Field</SPAN>[/TD]
[TD]Data Field</SPAN>[/TD]
[/TR]
[TR]
[TD]12</SPAN>[/TD]
[TD]12345</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]3/2/13</SPAN>[/TD]
[TD]30</SPAN>[/TD]
[TD]xxx</SPAN>[/TD]
[TD]pcs</SPAN>[/TD]
[TD]O</SPAN>[/TD]
[/TR]
[TR]
[TD]12</SPAN>[/TD]
[TD]12345</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]3/12/13</SPAN>[/TD]
[TD]30</SPAN>[/TD]
[TD]xxx</SPAN>[/TD]
[TD]pcs</SPAN>[/TD]
[TD]O</SPAN>[/TD]
[/TR]
[TR]
[TD]15</SPAN>[/TD]
[TD]99999999</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]3/12/13</SPAN>[/TD]
[TD]100</SPAN>[/TD]
[TD]ghy</SPAN>[/TD]
[TD]pcs</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[/TR]
[TR]
[TD]43</SPAN>[/TD]
[TD]8888888</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]3/12/13</SPAN>[/TD]
[TD]120</SPAN>[/TD]
[TD]gpa</SPAN>[/TD]
[TD]pcs</SPAN>[/TD]
[TD]O</SPAN>[/TD]
[/TR]
[TR]
[TD]32</SPAN>[/TD]
[TD]77777777</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]3/12/13</SPAN>[/TD]
[TD]39</SPAN>[/TD]
[TD]hji</SPAN>[/TD]
[TD]pcs</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[/TR]
[TR]
[TD]5</SPAN>[/TD]
[TD]11199980</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]4/13/13</SPAN>[/TD]
[TD]50</SPAN>[/TD]
[TD]yyy</SPAN>[/TD]
[TD]pcs</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[/TR]
[TR]
[TD]5</SPAN>[/TD]
[TD]11199980</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]5/6/13</SPAN>[/TD]
[TD]50</SPAN>[/TD]
[TD]yyy</SPAN>[/TD]
[TD]pcs</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[/TR]
[TR]
[TD]5</SPAN>[/TD]
[TD]11199980</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]5/6/13</SPAN>[/TD]
[TD]50</SPAN>[/TD]
[TD]yyy</SPAN>[/TD]
[TD]pcs</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[/TR]
[TR]
[TD]5</SPAN>[/TD]
[TD]11199980</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]5/20/13</SPAN>[/TD]
[TD]50</SPAN>[/TD]
[TD]yyy</SPAN>[/TD]
[TD]pcs</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[/TR]
[TR]
[TD]5</SPAN>[/TD]
[TD]11199980</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]7/1/13</SPAN>[/TD]
[TD]50</SPAN>[/TD]
[TD]yyy</SPAN>[/TD]
[TD]pcs</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[/TR]
[TR]
[TD]5</SPAN>[/TD]
[TD]11199980</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]7/8/13</SPAN>[/TD]
[TD]50</SPAN>[/TD]
[TD]yyy</SPAN>[/TD]
[TD]pcs</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[/TR]
[TR]
[TD]96</SPAN>[/TD]
[TD]22299980</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]4/10/13</SPAN>[/TD]
[TD]85</SPAN>[/TD]
[TD]mmm</SPAN>[/TD]
[TD]pcs</SPAN>[/TD]
[TD]O</SPAN>[/TD]
[/TR]
[TR]
[TD]96</SPAN>[/TD]
[TD]22299980</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]5/12/13</SPAN>[/TD]
[TD]85</SPAN>[/TD]
[TD]mmm</SPAN>[/TD]
[TD]pcs</SPAN>[/TD]
[TD]O</SPAN>[/TD]
[/TR]
[TR]
[TD]96</SPAN>[/TD]
[TD]22299980</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]6/19/13</SPAN>[/TD]
[TD]85</SPAN>[/TD]
[TD]mmm</SPAN>[/TD]
[TD]pcs</SPAN>[/TD]
[TD]O</SPAN>[/TD]
[/TR]
[TR]
[TD]2</SPAN>[/TD]
[TD]33399980</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]3/12/13</SPAN>[/TD]
[TD]16</SPAN>[/TD]
[TD]qjw</SPAN>[/TD]
[TD]pcs</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL span=4></COLGROUP>[/TABLE]

 

[texasalynn]

try this

Sub remove_dups()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
x = Evaluate("MAX(IF(B:B=" & Cells(i, "B") & ",D:D))")
If Cells(i, "D") <> x Then
    Rows(i).Delete
End If
Next i
End Sub