Here's a good one! Delete duplicates with macro keeping only the newest by date

[rkol297]

I have a spreedsheet with 17,000 rows in it. In column D I have ID numbers and in column B I have the dates. There are multiple entries for each ID in column D which gives multiples dates each entry was given in column B. I need a VBA Macro that will identify duplicate entries in Column D and keep only the most recent entry by date in column B. Any help would be greatly appreciated. I'm banging my head on the desk to get this to work thanks!

 

[Moe Kadhom]

Hi Domenic
Thanks for the quick response.

the code, only highlighted the last entries. Not the last two.

I will be using the values in column B and Dates in column C to write a formula so I want the last two entry for each value in column A to be next to each other.

sample of my data is:
A B C
IC54446 1258 24/01/2015
IC54446 1300 24/01/2015
IC54446 1258 24/03/2015
IC54446 1300 08/04/2015
AB2598 1800 15/06/2014
AB2598 1905 15/08/2014
AB2598 2001 15/08/2014
AB2598 2101 15/09/2014

thanks

 

[Domenic]

When I run the macro based on your sample data, I get the following results assuming that your data has column headers...

AB2598	2101	9/15/14
AB2598	1905	8/15/14
IC54446	1300	4/08/15
IC54446	1258	3/24/15

<tbody>
</tbody>

Is this the result you expect?

Also, are your dates true date values? If not, convert them into true date values.

 

[Moe Kadhom]

Hi Domenic
OK, the macro is working but not for all values.

when I run the macro on sample below I get this result.

Just a note, I am using Day, Month, Year format ( All our reports in Australia uses this format)



Result

ESR211	5474	7/01/2015
ESR211	5474	7/01/2015

<tbody>
</tbody><colgroup><col span="2"><col></colgroup>

Sample Data
ESR211 4910 12/12/2013
ESR211 4910 12/12/2013
ESR211 4910 24/03/2014
ESR211 4910 24/03/2014
ESR211 4910 24/03/2014
ESR211 5240 26/06/2014
ESR211 5240 4/07/2014
ESR211 5240 4/07/2014
ESR211 5240 29/08/2014
ESR211 5240 29/08/2014
ESR211 5240 29/08/2014
ESR211 5240 29/08/2014
ESR211 5474 7/01/2015
ESR211 5474 7/01/2015
ESR211 5474 7/01/2015


Can you see what the problem is?

thanks

 

[Domenic]

The macro keeps the last two entries based solely on Column A, hence the result. Do you actually want base the results on both Column A and Column B? So the last two entries for Column A and Column B? If so, I'll have to get back to you when I get a chance. I'm logging off for the day.

 

[Moe Kadhom]

Hi Domenic
yes, That's is exactly what I want.


thanks for your help

 

[Domenic]

Try...

[color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] KeepLastTwoEntries()

    [color=darkblue]Dim[/color] LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] LastCol [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    Application.ScreenUpdating = [color=darkblue]False[/color]
    
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    
    Range("A1", Cells(LastRow, LastCol)).Sort _
        key1:=Range("A1"), order1:=xlAscending, _
        key2:=Range("B1"), order2:=xlDescending, _
        key3:=Range("C1"), order3:=xlDescending, _
        Header:=xlYes, _
        OrderCustom:=1, _
        MatchCase:=False, _
        Orientation:=xlTop[color=darkblue]To[/color]Bottom
    
    Range(Cells(2, LastCol + 1), Cells(LastRow, LastCol + 1)).FormulaR1C1 = "=RC1&""#""&RC2"
    
    [color=darkblue]For[/color] i = LastRow To 2 [color=darkblue]Step[/color] -1
        [color=darkblue]If[/color] WorksheetFunction.CountIf(Range(Cells(2, LastCol + 1), Cells(i, LastCol + 1)), Cells(i, LastCol + 1)) > 2 [color=darkblue]Then[/color]
            Rows(i).Delete
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] i
    
    Columns(LastCol + 1).ClearContents
    
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
    MsgBox "Completed...", vbInformation
        
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

Hope this helps!

 

[Moe Kadhom]

Hi Domenic
What is the result of the macro after you run it on the sample data that I gave earlier? I ran it and it is giving me the wrong results. it is not giving me the last two entries, rather leaving 4-5 rows at random. Maybe it is something that I'm doing.



It should give me below result:

ESR211	5240	29/08/2014
ESR211	5474	7/01/2015

<tbody>
</tbody>

 

[Domenic]

I get the following result...

ESR211	5474	42011
ESR211	5474	42011
ESR211	5240	41880
ESR211	5240	41880
ESR211	4910	41722
ESR211	4910	41722

<colgroup><col style="width:48pt" span="3" width="64"> </colgroup><tbody>
</tbody>

 

[Moe Kadhom]

Can you modify the VBA code so it gives me the result that I want? thanks for your help

ESR211	5240	29/08/2014
ESR211	5474	7/01/2015

<tbody>
</tbody>

 

[Domenic]

Can you modify the VBA code so it gives me the result that I want? thanks for your help

ESR211	5240	29/08/2014
ESR211	5474	7/01/2015

<tbody>
</tbody>

Are you saying that these two rows above is what you expect?