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

**Answer:**

### Match year and month

**Array formula in A14:**

Copy cell and paste it to the right to D14. Copy A14:D14 and paste it down as far as needed.

### List all the unique distinct events for that month

**Array formula in A23:**

Copy cell and paste it to the right to D23. Copy A23:D23 and paste it down as far as needed.

### How to create an array formula

- Select cell A23
- Copy/Paste array formula to formula bar
- Press and hold Ctrl + Shift
- Press Enter

### Explaining array formula in cell A23

**Step 1 - Find matching months and years**

DATE(YEAR($C$2), MONTH($C$2), 1)=DATE(YEAR($A$5:$A$9), MONTH($A$5:$A$9), 1))

becomes

DATE(YEAR(40303), MONTH(40303), 1)=DATE(YEAR({40290;40285;40300;40303;40300}), MONTH({40290;40285;40300;40303;40300}), 1))

becomes

DATE(2010, 5, 1)=DATE({2010;2010;2010;2010;2010}, {4;4;5;5;5}), 1))

becomes

40299={40269;40269;40299;40299;40299}

and returns

{FALSE;FALSE;TRUE;TRUE;TRUE}

**Step 2 - Find unique distinct records**

This COUNTIFS formula avoids duplicate records. It uses absolute and relative cell references.

COUNTIFS($A$22:$A22, $A$5:$A$9, $B$22:$B22, $B$5:$B$9, $C$22:$C22, $C$5:$C$9, $D$22:$D22, $D$5:$D$9)

returns

{0;0;0;0;0}

**Step 3 - Add arrays**

NOT(DATE(YEAR($C$2), MONTH($C$2), 1)=DATE(YEAR($A$5:$A$9), MONTH($A$5:$A$9), 1))+COUNTIFS($A$22:$A22, $A$5:$A$9, $B$22:$B22, $B$5:$B$9, $C$22:$C22, $C$5:$C$9, $D$22:$D22, $D$5:$D$9)

becomes

NOT({FALSE;FALSE;TRUE;TRUE;TRUE})+{0;0;0;0;0}

becomes

{1;1;0;0;0}+{0;0;0;0;0}

and returns

{1;1;0;0;0}

**Step 4 - Find first unique distinct row in range**

MATCH(0, NOT(DATE(YEAR($C$2), MONTH($C$2), 1)=DATE(YEAR($A$5:$A$9), MONTH($A$5:$A$9), 1))+COUNTIFS($A$22:$A22, $A$5:$A$9, $B$22:$B22, $B$5:$B$9, $C$22:$C22, $C$5:$C$9, $D$22:$D22, $D$5:$D$9), 0)

becomes

MATCH(0, {1;1;0;0;0}, 0)

and returns 3.

**Step 5 - Return a value of the cell at the intersection of a particular row and column**

INDEX($A$5:$D$9, MATCH(0, NOT(DATE(YEAR($C$2), MONTH($C$2), 1)=DATE(YEAR($A$5:$A$9), MONTH($A$5:$A$9), 1))+COUNTIFS($A$22:$A22, $A$5:$A$9, $B$22:$B22, $B$5:$B$9, $C$22:$C22, $C$5:$C$9, $D$22:$D22, $D$5:$D$9), 0), COLUMN(A1))

becomes

INDEX($A$5:$D$9, 3, COLUMN(A1))

becomes

INDEX({40290, " Jim Smith", 1, "Out of Service"; 40285, "John Doe", 3, "Requires Attention"; 40300, " Bob Smith", 3, "Requires Attention"; 40303, " Jim Smith", 1, "Out of Service"; 40300, " Bob Smith", 3, "Requires Attention"}, 3, 1)

and returns 2-MAy-2010

IFERROR converts errors to blank cells.

### Download excel *.xlsx file.

list-all-the-unique-events-for-a-specific-month.xlsx

**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.

**MATCH(**lookup_value;lookup_array; [match_type]

Returns the relative position of an item in an array that matches a specified value

**MIN(**number1,[number2]**)**

Returns the smallest number in a set of values. Ignores logical values and text

**ROW(**reference**)** returns the rownumber of a reference

**COLUMN(**reference**)
**returns the column number of a reference

**TEXT(**value, format_text**)**

Converts a value to text in a specific number format

### Category: Dates

Create a date range using excel formula

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/09-1/10/09 Cell B1 […]Comments(142) Filed in category: Dates, Excel

Lookup a value and find max date

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]Comments(82) Filed in category: Dates, Excel

Formula for matching a date within a date range in excel

Table of contents Match a date when a date range is entered in a single cell Match a date when […]Comments(48) Filed in category: Dates, Excel

### Category: Unique distinct values

How to extract a unique distinct list from a column in excel

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

Count unique distinct values that meet multiple criteria in excel

This post demonstrates how to build an array formula that counts unique distinct values using a criterion or criteria. Tip! […]Comments(93) Filed in category: Count values, Excel, Unique distinct values

Extract a unique distinct list from two columns

Question: I have two ranges or lists (List1 and List2) from where I would like to extract an unique distinct […]Comments(78) Filed in category: Excel, Unique distinct values

### Category: Unique values

How to create a list of random unique numbers in excel

Table of Contents Generate unique random numbers Generate unique random values from a cell range Generate unique random numbers Question: […]Comments(102) Filed in category: Excel, Random, Unique values

Count unique distinct values in a column in excel

Question: I have a list of values (A1:A6), how do I count unique distinct values? Answer: Table of Contents Count […]Comments(26) Filed in category: Count values, 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

### 3 Responses to “List all the unique events for a month in excel (array formula)”

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

Thank you for this sample, it is really useful.

I am trying to use it for one control sheet that I have, however I have found a problem which I am not able to fix, I hope you can help me.

The problem happens if you have an event in the current month that had already occurred in a previous month. In that case that event will not appear in the list of unique events for the current month.

For instance, in the sample you have, assuming today’s date is 5-May-2010 and if you replace the name in the first event (cell B5) with Jim Smith (instead of John Doe), the list of unique events for May 2010 will show only the event of line 7 and not the one of line 8. The event of line 8 has occurred in April and not in May, but it is still filtered and not shown in the final result.

I hope you understand my explanation and also that you can find a solution.

Thank you in advance,

MV.

MV,

You are right!

I have changed this post.

Thanks for bringing this to my attention!

Hi Oscar,

Thanks a lot!

MV