Remove Duplicates: (Keeping last row)

[natetc]

Hi,

Just using the remove duplicate function and wanted to keep the last row of data rather than keep the first row of data (keep 4 rather than 1). Reason being the first row is old data and the last row is most recent.

Here is the data i've been working with:

<TABLE style="WIDTH: 109pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=145><COLGROUP><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 61pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl79 height=17 width=81 align=right>11:42:31 AM</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=64 align=right>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl79 height=17 align=right>11:42:31 AM</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl79 height=17 align=right>11:42:31 AM</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl79 height=17 align=right>11:42:31 AM</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right>4</TD></TR></TBODY></TABLE>

Any help would be great! thanks!

 

[AlphaFrog]

If your Time values are in column A, put this in any empty column. For this example, I'll use column C

C1
=COUNTIF(A1:$A$65536,A1)>1

Copy this down column C for each entry in column A. This will return TRUE if there are duplicates below and FALSE if not. The last entry of a set of duplicates will be False.

Then select Column C
Select from the menu Data\ Filter\ Autofilter
This will put a dropdown list in C1
Select from the C1 dropdown list "TRUE"
This will hide all the "False" rows or the rows with the last duplicate or Unique entries.

You could then select all the visible rows and delete them.
To turn off Autofilter, select from the menu Data\ Filter\ Autofilter again.

You could make a macro do do this as well.

 

[jbeaucaire]

Great approach! My only caveat would be sure to start that formula in C2, C1 will need a title like "key" for the autofilter to work properly.

 

[Domenic]

Assuming that Column A and Column B contain the data, maybe...

Sub test()

    With Columns("A:B")
        .Sort key1:=Range("A1"), Order1:=xlAscending, key2:=Range("B1"), Order2:=xlDescending, Header:=xlGuess
        .RemoveDuplicates Columns:=1, Header:=xlGuess
    End With

End Sub