Remove Duplicate Lines; Retain Most Recent

[jski21]

Good day Mr. Excel Team,

A rather sticky one here for me. As usual, canned system download reports are invariably ugly and unusable. Here's an excerpted sample:

Contracts_Certified_-_8006 (1).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Grant	Major Program	Major Program Name	Vendor Legal Name	Doc Hdr Doc Description	Doc Hdr Record Date	Doc Comm Service From Date	Doc Comm Service To Date	Doc ID	Object	Doc Hdr Actual Amt	Doc Hdr Open Amt	Doc Hdr Closed Amt	Doc Version No
2	12345678	12345	ABCD	STD Contractors Inc.	J. Walk	5/12/2021	6/29/2020	12/31/2021	LA2020000000008	6380	83,523.00	49,328.00	34,195.00	1
3	12345678	12345	ABCD	STD Contractors Inc.	J. Walk	6/24/2021	6/29/2020	12/31/2021	LA2020000000008	6380	83,523.00	49,328.00	34,195.00	2
4	12345678	12345	ABCD	STD Contractors Inc.	J. Walk	8/25/2022	6/29/2020	12/31/2021	LA2020000000008	6380	88,681.00	35,239.00	53,442.00	3
Grant Balances

The raw data represents a list of contracts and data related to that contract. For whatever reason, any change to a contract (amendment, etc.) results in the system creating another line for that contract and assigning it a sequential number. Contract number is in Column I (Doc ID). Sequential number is in Column N (Doc Version No). The duplication makes simple analysis unbearable as there is only one contract.

I'm attempting to develop a macro that would review Column I for dupes, glance over at Column N to see the sequence numbers, and have the entry with the highest sequence number survive. The other row entries would be deleted. End product would be one line for a contract, that one being the 'most recent.'

Thanks in advance for the consideration and review.

jski

 

[kevin9999]

Please try the following on a copy of your workbook.

Option Explicit
Sub jski21()
    Dim ws As Worksheet
    Set ws = Worksheets("Grant Balances")   '<-- *** Check the sheet name ***
    With ws.Range("A1").CurrentRegion
        .Sort Key1:=ws.Range("I1"), order1:=xlAscending, _
        Key2:=ws.Range("N1"), order2:=xlDescending, Header:=xlYes
        .RemoveDuplicates Columns:=9, Header:=xlYes
    End With
End Sub

 

[jski21]

Awesome kevin9999. Walked through this using F8 and I see how this works now. Very helpful and instructive. Eliminated 700+ dupes. Huge time saver.

Thanks for your time and help!

jski

 

[kevin9999]

Happy to help, and thanks for the feedback