Previous Topic Next Topic

Keeping one entry from duplicates

Closed
mconfused - Aug 13, 2010 at 12:04 PM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Aug 13, 2010 at 10:21 PM
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

Answer 1 / 1
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Aug 13, 2010 at 10:21 PM
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.
0