Keeping one entry from duplicates

[Closed]
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,

I have an Excel spreadsheet that has one column with duplicate entries. The next column contains dates. I would like to keep only the newest of the duplicated entries as per the date on the second column. How is this done?

1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
suppose the data is like this

name date
a 8/5/2010
a 8/1/2010
d 8/7/2010
d 8/3/2010
s 8/6/2010
s 8/4/2010
s 8/2/2010

1. find out the unique names in column A. to find if data is large use advanced filter
highlight data in column A ONLY from A1 down(including column heading). click data-filter-advancefilter
if alert comes whether the first row is column heading click ok(this may or may not ocme)
in the advancefilter window
under "action" choose "copy to another location"
in list range it is already entered $A$1:$A$8.
leave criteria range blank
in "copy to" choose a cell a few caells below the data , in this case for examlpe A15.
click unique records onlly and click ok
a15 down will be

name
a
d
s

2. sort main data A1:B8 first column ascending and second column DESCENDING

3. NOW IN b16 COPY THIS FORMULA
=VLOOKUP(A16,$A$1:$B$8,2,0)
copy B16 down.

you will get from A15 down


name
a 08/05/10
d 08/07/10
s 08/06/10
This is what you want.