duplicate removal.

[mrxwanttobe]

Hello all. Please note first column is a customer number and last column is a date. I want to remove all the duplicates but only keep the row with the most recent date so in my example only the row with the 01/11/20 date. Is this possible? Than you all in advance.

1962​	40​	40​	7/8/16 22:29​
1962​	60​	100​	8/26/16 21:48​
1962​	100​	100​	10/13/17 22:32​
1962​	100​	100​	1/11/20 22:32​
1962​	40​	40​	11/2/18 22:51​
1962​	40​	40​	4/13/19 22:56​

 

[YasserKhalil]

Try this code

Sub Test()
    Dim rng As Range
    Set rng = Range("A1:D" & Cells(Rows.Count, 1).End(xlUp).Row)
    rng.Sort Key1:=rng.Cells(1, 1), Order1:=xlAscending, Key2:=rng.Cells(1, 4), Order2:=xlDescending, Header:=xlYes
    rng.RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

 

[YasserKhalil]

Another approach to use helper column in column E for example and put the formula starting at E2

=IF(MAX(IF(A2=$A$2:$A$7, $D$2:$D$7))=D2, "Latest", "")

then drag the formula down and you will have "Latest" as result for the latest records then you can filter by column E

 

[mrxwanttobe]

Having an issue installing 2010 SP1 so I can't install the power Query. Here is the results of the Helper column. any suggestions? Not sure why it only worked twice and then #VALUE for the next 17000 rows

membershipId	amountPaid	normalPrice	date
1​	0​	150​	3/29/16​
1​	150​	150​	3/29/16​
1​	0​	150​	4/5/16​
2​	0​	50​	3/29/16​
2​	0​	50​	5/26/17​	Latest
3​	0​	50​	5/26/17​	Latest
4​	0​	50​	6/9/16​	#VALUE!
4​	0​	50​	6/23/17​	#VALUE!
6​	0​	100​	4/1/16​	#VALUE!
7​	0​	100​	4/1/16​	#VALUE!
7​	0​	100​	4/15/17​	#VALUE!
7​	0​	100​	11/24/18​	#VALUE!
7​	0​	100​	1/4/20​	#VALUE!
8​	0​	150​	4/1/16​	#VALUE!
8​	100​	100​	7/15/16​	#VALUE!
8​	100​	100​	4/13/18​	#VALUE!
8​	100​	100​	4/19/19​	#VALUE!
9​	0​	50​	4/1/16​	#VALUE!
10​	0​	100​	4/1/16​	#VALUE!
12​	0​	50​	4/1/16​	#VALUE!

 

[mrxwanttobe]

I got the VBA to work perfectly. thanks so so so much.

 

[YasserKhalil]

You're welcome.
As for the formula you have to change the reference of ranges and at the end press Ctrl + Shift + Enter.

 

[sandy666]

Having an issue installing 2010 SP1 so I can't install the power Query

I suggest to use XL2016 or higher with Power Query built-in
also use XL2BB to post your examples

 

[mole999]

Found my Office 10 and will install and give the power query a try. My sheet has over 20000 rows and 100's of customer id's in column "A". Will this query not work? Yasser Khalil is correct. I want to delete all the duplicates with the oldest dates so the only row left has the latest date.

You should be able to add "Power Query Desktop" freeware from Microsoft, the advantage of that is on a 64bit system, with 32bit excel, you can install the 64bit and access bigger than 3Gb of available memory

 

[sandy666]

You should be able to add "Power Query Desktop" freeware from Microsoft, the advantage of that is on a 64bit system, with 32bit excel, you can install the 64bit and access bigger than 3Gb of available memory

You mean Power BI Desktop?