## 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

### Category: Unique values

5 easy ways to extract unique distinct values

Update: 30 Aug, 2017h You have quite a few options to choose from if you are looking for a way […]Comments(149) Filed in category: Advanced filter, Excel, Unique distinct values, Unique values

Excel: List intervals between two values

jeyner asks: I need to count in a list the interval between the same value. Example list, 1-2-3-1-4-5-1-6-7-8-9-7-8-1 So the […]Comments(16) Filed in category: Count values, Excel, Range

Create unique list from two columns

I read an article Merging Lists To A List Of Distinct Values at CPearson. The article describes code that you […]Comments(10) Filed in category: Excel, Unique values

List all the unique events for a month in excel (array formula)

Question: I have a table with four columns, Date, Name, Level, and outcome. The range is from row 3 to […]Comments(5) Filed in category: Dates, Excel, Unique distinct values, Unique values

Comments(4) Filed in category: Excel, Unique values

Find min and max unique and duplicate numerical values

Question: How do I get the largest and smallest unique and duplicate value? The image below shows you a list […]Comments(4) Filed in category: Duplicate values, Excel, Unique values

Extract unique values from a range using array formula in excel

Question: How do I extract values only occuring once in a range? Answer: A range (tbl_text) containing text values Array […]Comments(2) Filed in category: Excel, Unique values

Filter unique values from a range using array formula in excel

Unique values in a range are values occurring only once. This is what I am going to do in this […]Comments(2) Filed in category: Excel, Unique values

Count unique records between two dates in excel

This blog article is one out of four articles on the same subject. Most common value between two dates in […]Comments(2) Filed in category: Count values, Dates, Excel, Unique distinct values, Unique values

### 10 Responses to “Filter unique rows and sort by date using array formula in excel”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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.

Dave,

see this post: https://www.get-digital-help.com/2010/05/05/list-all-the-unique-events-for-a-month-in-excel-array-formula/

Thanks! Works like a treat

I am trying to filter dates (with days) using month and year as follows:

=SUMIFS('DATA INPUT'!$C$3:$C$5000,TEXT('DATA INPUT'!$B$3:$B$5000, "yyyy-mmm"),TEXT(E$1,"yyyy-mmm"),'DATA INPUT'!$D$3:$D$5000,"Pilsner",'DATA INPUT'!$E$3:$E$5000,"Beer pack")

- Column "C" is what I would like to sum

- Column "B" is the dates I am TRYING to filter using E1 (inputted month)

- Column "D" and "E" are standard text to be filtered

I have tried playing with the concepts above but to no avail, any ideas? I would like to avoid VBA, etc.

Chris,

Array formula in F1:

Your formula:

becomes

Entered as an array formula.

I may be a bimbo, but I can't figure this out. I have five rows that need to change, sorted by date from the first row.

here are my row names: due date, author, title, changes, date completed

I want the due dates to all be in order, changing the corresponding columns.

HELP a homegirl out! PLEASE!

oh and i have a MAC.

amber,

Is this what you had in mind?

Sort from left to right

Hi

I've gotten this to partially work. The problem is my data is split over 4+ columns. So Column A would be Columns S,AA,AI,etc and column B is Columns O,W,AE,etc.

This works:

Date (S2:S21)

Values (O2:O21)

But when I try to add other columns to the named ranges it doesn't like it. This doesn't work:

Data (S3:S42,AA3:AA24)

Values (O3:O42,W3:W24)

Thanks

Laura,

you are right, it does not work. I don´t have a solution for you.