Author: Oscar Cronquist Article last updated on December 21, 2018

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

Question found here

Answer:

Match year and month

Array formula in A14:

=IFERROR(INDEX($A$5:$D$9, SMALL(IF(DATE(YEAR($C$2), MONTH($C$2), 1)=DATE(YEAR($A$5:$A$9), MONTH($A$5:$A$9), 1), MATCH(ROW($A$5:$A$9), ROW($A$5:$A$9)), ""), ROW(A1)), COLUMN(A1)), "")

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:

=IFERROR(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)), "")

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

  1. Select cell A23
  2. Copy/Paste array formula to formula bar
  3. Press and hold Ctrl + Shift
  4. Press Enter

Explaining array formula in cell A23

Step 1 - Find matching months and years

The DATE function creates an Excel date based on year, month and day value. The YEAR function returns the year from an Excel date. The MONTH function returns the year from an Excel date.

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 function prevents duplicate records. It uses absolute and relative cell references in order to create expanding cell references, they grow as the cell is copied to cells below.

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

The NOT function returns the boolean opposite, TRUE becomes FALSE and FALSE becomes TRUE.

By adding the arrays we get OR logic meaning at least one value must be TRUE for the result to be TRUE, remember that it is evaluated row-wise.

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

The MATCH function returns the relative position of a specific value in an array.

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

The INDEX function returns a value from a given cell range based on a row and column number.

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.

Get excel *.xlsx file.

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

Weekly Blog EMAIL

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

5 easy ways to extract Unique Distinct Values

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

Extract unique distinct values based on a date range

Question: I have a large list of dates and other adjacent values. I want to create a distinct list from […]

Extract unique distinct year and months from dates

Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]

Remove duplicates within same month or year

The array formula in cell B15 extracts dates from B4:B12 if it is not a duplicate item in the same […]

Highlight duplicates with same date, week or month

The image above demonstrates a conditional formatting formula that highlights duplicate items based on date. The first instance is not highlighted, […]