Question: How do I filter duplicate rows and sort by date?

Answer: Column A and B are the original list. Column D and E are the filtered duplicate list sorted by date. See picture below.

Array formula in D2:

=INDEX(Date, MATCH(SMALL(IF(COUNTIF(Values, Values)>1, COUNTIF(Date, "<"&Date), ""), ROW()-1), COUNTIF(Date, "<"&Date), 0)) + CTRL + SHIFT + ENTER copied down as far as necessary.

Array formula in E2:

=INDEX(Values, MATCH(SMALL(IF(COUNTIF(Values, Values)>1, COUNTIF(Date, "<"&Date), ""), ROW()-1), COUNTIF(Date, "<"&Date), 0)) + CTRL + SHIFT + ENTER copied down as far as necessary.

Named ranges
Date (A2:A21)
Values (B2:C21)
What is named ranges?

How to implement array formula to your workbook
Change named ranges. If your list starts at, for example, F2. Change D1:$D$1  in the above formulas to F1:$F$1 and E1:$E$1 to G1:$G$1

Download excel example file.
filter-duplicate-values-and-sort-by-date.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

SMALL(array,k) Returns the k-th smallest row number in this data set.

ROWS(array) returns the number of rows in a reference or an array

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

Related posts:

1. Filter unique rows and sort by date using array formula in excel
2. Sort dates within a date range using excel array formula
3. Filter duplicates from two columns combined and sort from A to Z using array formula in excel
4. Sort a range by occurence using array formula in excel
5. Filter duplicate values in a range using “contain” condition in excel
6. Filter duplicate text values in a range using “begins with” criterion in excel
7. Min and max unique and duplicate values using array formula in excel
8. Filter values existing in range 1 but not in range 2 using array formula in excel
9. Filter a column and create a new unique list sorted from A to Z using array formula in excel
10. Sort text values by length using array formula in excel