Filter unique rows and sort by date using array formula in excel
Question: How do I filter unique rows and sort by date?
Answer: Column A and B are the original list. Column D and E are the filtered unique list sorted by date. See picture below.
Array formula in D2:
copied down as far as necessary.
Array formula in E2:
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-unique-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 blog posts
- Filter duplicate rows and sort by date using array formula in excel
- Sort dates within a date range using excel array formula
- Filter a column and create a new unique list sorted from A to Z using array formula in excel
- Filter unique values from a range using array formula in excel
- Sort a range by occurence using array formula in excel
Welcome! I am Oscar and here to help you! Feel free to comment and ask excel questions.Don´t forget to add my
May 4th, 2010 at 4:44 am
Hi,
Just found the site and wow! I've already fixed a few sloppy problems in some my work spreadsheets.
Sorry in advance if this is the wrong way to ask a question.
But this page is the closet I've found to what I am trying to do. I have a table with four columns, Date, Name, Level, and outcome. The range is from row 3 to row 1000.
What I need to be able to do is look at today's date. Determine the month and year and then look up all the values in the date column that match the month and year. I've been trying to get it to work with sumproduct but I can't wrap my head around it.
Then on a separate tab list all the unique events for that month.
So one the seperate tab it would show something like this:
May 2/2010 Bob Smith 3 Requires Attention
May 5/2010 Jim Smith 1 Out of Service
Hope you are able to help. Thanks in advance.
May 5th, 2010 at 9:37 pm
Dave,
see this post: http://www.get-digital-help.com/2010/05/05/list-all-the-unique-events-for-a-month-in-excel-array-formula/
May 12th, 2010 at 8:08 pm
Thanks! Works like a treat