## Filter unique values and sort based on adjacent date

The formula in cell F3 extracts unique values from column C, the formula in cell F3 extracts the corresponding dates from column B.

**Formula in E3:**

copied down as far as necessary.

**Array formula in F2:**

copied down as far as necessary.

### Explaining formula in cell E3

#### Step 1 - Get frequency of each value

The COUNTIF function counts values based on a condition or criteria. We are looking for unique values that is why we compare the array with 1.

COUNTIF($C$3:$C$22,$C$3:$C$22)=1

becomes

COUNTIF({"BB"; "VV"; "WW"; "BB"; "DD"; "KK"; "YY"; "UU"; "LL"; "WW"; "PP"; "CC"; "VV"; "MM"; "EE"; "WW"; "KK"; "TT"; "II"; "PP"}, {"BB"; "VV"; "WW"; "BB"; "DD"; "KK"; "YY"; "UU"; "LL"; "WW"; "PP"; "CC"; "VV"; "MM"; "EE"; "WW"; "KK"; "TT"; "II"; "PP"})=1

becomes

{2;2;3;2;1;2;1;1;1;3;2;1;2;1;1;3;2;1;1;2}=1

and returns

{FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE}

#### Step 2 - Check previous values to prevent duplicate values in output list

COUNTIF($F$2:F2,$C$3:$C$22)=0

becomes

COUNTIF("Date", {"BB"; "VV"; "WW"; "BB"; "DD"; "KK"; "YY"; "UU"; "LL"; "WW"; "PP"; "CC"; "VV"; "MM"; "EE"; "WW"; "KK"; "TT"; "II"; "PP"})=0

becomes

{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}=0

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

#### Step 3 - Multiply arrays

We multiply the arrays because both conditions must be met.

((COUNTIF($C$3:$C$22,$C$3:$C$22)=1)*(COUNTIF($F$2:F2,$C$3:$C$22)=0))

becomes

{FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE}*{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

and returns

{0;0;0;0;1;0;1;1;1;0;0;1;0;1;1;0;0;1;1;0}.

#### Step 4 - Divide 1 with array

This step makes sure that FALSE will become #DIV/0!. The LOOKUP function ignores errors and that will be very useful for us.

1/((COUNTIF($C$3:$C$22,$C$3:$C$22)=1)*(COUNTIF($F$2:F2,$C$3:$C$22)=0))

becomes

1/{0;0;0;0;1;0;1;1;1;0;0;1;0;1;1;0;0;1;1;0}

and returns

{#DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; 1; #DIV/0!; 1; 1; 1; #DIV/0!; #DIV/0!; 1; #DIV/0!; 1; 1; #DIV/0!; #DIV/0!; 1; 1; #DIV/0!}

#### Step 5 - Return value

LOOKUP(2,1/((COUNTIF($C$3:$C$22,$C$3:$C$22)=1)*(COUNTIF($F$2:F2,$C$3:$C$22)=0)),$B$3:$B$22)

becomes

LOOKUP(2,{#DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; 1; #DIV/0!; 1; 1; 1; #DIV/0!; #DIV/0!; 1; #DIV/0!; 1; 1; #DIV/0!; #DIV/0!; 1; 1; #DIV/0!},$B$3:$B$22)

and returns

8/23/2008 in cell E3.

### Get Excel *.xlsx file

First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

I read an article Merging Lists To A List Of Distinct Values at CPearson. The article describes code that you […]

### 11 Responses to “Filter unique values and sort based on adjacent date”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

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.

Dear Sir,

I handle bankruptcy cases for many companies. The details of each company are maintained in different workbooks. The reporting compliances and the processes have very stringent timelines. I need to have all the details of reporting requirements and processes due yesterday to three days from now on a single master sheet distinct from these workbooks.

The details of the reporting required and processes are on the columns G, I K, M O, R of worksheet "REPORTING" and column h of "PROCESS".

PLEASE help me with a formula or a vba code by which I can get the complete rows where the relevant dates appear on the master sheet mentioned above.

I have tried working with the formulas given by you, as I find your solutions to be effective as you explain them nicely and the logic once clearly understood, can be applied anywhere.

I have been able to get the answer for a column but not the complete row, if I am using the same worksheet or even the same workbook but not on a separate workbook. Besides, I get a #num wherever the dates are out of range. Is it possible to get a blank instead of this error message.

I have been struggling with this for long. Kindly help. I have attched the link to my google drive in the column for website. Just for information I use macbook pro and ms excel for mac.

A big thank you for bringing me upto this point ans bigger thank you in advance for leading me to the destination.