List all unique distinct rows in a given month

This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the image above.

The data set is in cell range A5:D9, the array formula is in cell A23 copied/pasted to adjacent cells to the right and below as far as needed.

Cell range A14:D16 shows filtered rows based on the given year and month specified in cell C2, however, there is a duplicate row, see rows 14 and 16.

1. List all unique distinct rows in a given month

Question:

Question found here

Answer:

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.

Back to top

1.1 How to create an array formula

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

1.2 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 a number representing the position of a given month in a year, based on 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.

Back to top

2. List all unique distinct rows in a given month (Excel 365)

Dynamic array formula in cell A23:

=UNIQUE(FILTER(A5:D9, (MONTH(B2)=MONTH(A5:A9))*(YEAR(B2)=YEAR(A5:A9))))

Back to top

2.1 How to enter a dynamic array formula

The dynamic array formula is a new feature available in Excel 365, you enter it as a regular formula.

Back to top

2.2 Explaining dynamic array formula

Step 1 - Calculate number representing the position of the month

The MONTH function returns a number representing the position of a given month in a year, based on an Excel date.

MONTH(serial)

MONTH(B2)

becomes

MONTH(40303)

and returns 5. The fifth month is May.

Step 2 - Compare month numbers to given month

The equal sign lets you compare month numbers to identify dates matching the month, the result is a boolean value.

MONTH(B2)=MONTH(A5:A9)

becomes

5={4; 4; 5; 5; 5}

and returns {FALSE; FALSE; TRUE; TRUE; TRUE}.

Step 3 - Extract year from date

The YEAR function returns the year from an Excel date.

YEAR(serial)

YEAR(B2)

becomes

YEAR(40303)

and returns 2010.

Step 4 - Compare year numbers to the condition

YEAR(B2)=YEAR(A5:A9)

becomes

2010={2010; 2010; 2010; 2010; 2010}

and returns {TRUE; TRUE; TRUE; TRUE; TRUE}.

Step 5 - AND logic

Both tests must be true and to do that we need to multiply the array, in other words, apply AND logic.

Use the asterisk * to multiply values or arrays.

The AND logic behind this is that

TRUE * TRUE = TRUE (1)
TRUE * FALSE = FALSE (0)
FALSE * FALSE = FALSE (0)

(MONTH(B2)=MONTH(A5:A9))*(YEAR(B2)=YEAR(A5:A9))

becomes

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

and returns {0; 0; 1; 1; 1}.

Step 6 - Filter values based on array

The FILTER function lets you extract values/rows based on a condition or criteria.

FILTER(array, include, [if_empty])

FILTER(A5:D9, (MONTH(B2)=MONTH(A5:A9))*(YEAR(B2)=YEAR(A5:A9)))

becomes

FILTER(A5:D9, {0; 0; 1; 1; 1})

and returns

{40300, " Bob Smith", 3, "Requires Attention"; 40303, " Jim Smith", 1, "Out of Service"; 40300, " Bob Smith", 3, "Requires Attention"}.

Step 7 - Extract unique distinct rows/records

The UNIQUE function lets you extract both unique and unique distinct values and also compare columns to columns or rows to rows.

UNIQUE(FILTER(A5:D9, (MONTH(B2)=MONTH(A5:A9))*(YEAR(B2)=YEAR(A5:A9))))

becomes

UNIQUE({40300, " Bob Smith", 3, "Requires Attention"; 40303, " Jim Smith", 1, "Out of Service"; 40300, " Bob Smith", 3, "Requires Attention"})

and returns

{40300, " Bob Smith", 3, "Requires Attention"; 40303, " Jim Smith", 1, "Out of Service"}.

Back to top

3. List all rows in a given month and year

The array formula in cell A14 returns all rows that match the month and year from the specified date in cell C2.

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.

Back to top

4. List all rows in a given month and year (Excel 365)

The dynamic array formula in cell A14 returns all rows that match the month and year from the specified date in cell C2.

Dynamic array formula in cell A14:

Back to top

Get Excel *.xlsx file

Get the Excel file

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

Back to top