• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 527
  • Last Modified:

Excel 2010 - Remove Duplicate Rows - Conditional

I am trying to remove duplicate rows from a spreadsheet. The duplicate values are contained in Column A. When I remove the duplicates, I want to remove the older ones and keep the newest (column B contains the date). I can sort this and highlight duplicate rows and then manually delete the older ones but I'd rather do this with a formula of some kind. Is there a relatively easy way to accomplish this? See Sample Spreadsheet and Resulting Spreadheet below for example.

Sample Spreadsheet

--A--    ------B------
1234   10/22/2013
1234   09/24/2013
1234   06/21/2013
2876   09/22/2013
3456   01/02/2012
3456   03/05/3013

Resulting Spreadsheet After Removing Duplicates

--A--    ------B------
1234   10/22/2013
2876   09/22/2013
3456   03/05/3013
1 Solution
Assuming that your data started in cell A1, I put the following formula in an empty column row 1:
=IF(COUNTIFS(A:A,A1,B:B,">" & B1),"Delete",IF(COUNTIFS(A$1:A1,A1,B$1:B1,B1)>1,"Delete",""))

The above formula will return an empty string (looks like a blank) for rows to be kept, and "Delete" for rows to be deleted. Sort your data by that column, then delete the desired rows.
cbridgmanAuthor Commented:
Worked like a charm. Thanks for the help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now