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.
Table of Contents
1. List all unique distinct rows in a given month
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.
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.
1.1 How to create an array formula
- Select cell A23
- Copy/Paste array formula to the formula bar
- Press and hold Ctrl + Shift
- 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.
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))))
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.
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"}.
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.
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:
Get Excel *.xlsx file
Dates category
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
Unique distinct values category
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]
This article shows how to extract unique distinct values based on a condition applied to an adjacent column using formulas. […]
Unique values category
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 […]
The formula in cell F3 extracts unique values from column C, the formula in cell F3 extracts the corresponding dates […]
Excel categories
9 Responses to “List all unique distinct rows in a given month”
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.
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
Oscar,
I have a similar issue and would ask if you can help with the final step of extracting a list of names from a 'helper' column that fall within a date range?
I have a worksheet with 2 tabs, one for DATA (over 4,000 rows) and the other is used to do lookups and reflect calculations on the data (REPORT). I attach an image of a spreadsheet I created just to give you an idea of what the columns and ranges are: https://postimg.org/image/c203uwp5h/
On the DATA tab, there are 2 columns of data 'Dates' and 'Agent' (these are the range names as well) - and I've created a 3rd column using INDEX/MATCH that contains a unique list of agent names found in the 'AGENT' range (named range of AGENTLIST).
Now I need to use this 'helper' column to derive a 2nd list (on the REPORT Tab) of just those agent names that have records that fall within two dates (date fields also on the REPORT Tab). As I change the dates, I would expect the 2nd list of names to be updated as the sheet recalculates.
If I had my 'druthers', it would be GREAT if that list is sorted.
I do not have the excel experience to do this, and I derived the first 'unique' list only after much studying of all the similar posts on this site (great site BTW). I hope you can help me.
Thanks,
Rich
Rich Darlington
Use a pivot table!
https://www.get-digital-help.com/2016/07/04/excel-pivot-tables/
Hi,
Great site!
I need help comparing two ranges and extracting unique names on one side, and repeated names on the other.
For instance, in one range I have a list of all the employees currently working in my office. They are in a separate sheet named "operadores".
They don't have to come to work every day, so in a different sheet on my workbook, I have a grid that lists the days of the semester in column C (there's a blank row between each day), and somewhere in those two rows there's the name of the employees that are assigned to work that day, next to the assignment they are working on.
I would need a formula that looks what the names of my employees are, and checks whether they are assigned that day.
After that, I would split it in two:
- On one side I would need to know who is free to be assigned to a different project.
- On the other side, I would need to know who is assigned so I can call them and let them know they have to come to work.
Is that possible? I'm working on Excel 2013.
Thanks!
Alejandra
Alejandra
Can you describe in greater detail how data is arranged on your worksheets? Perhaps an example or a picture?
Thanks for posting this example. It has been so helpful.
Question: How would I do the same thing if I only want to copy the unique events from two non-adjacent columns, for example, column A and D, and not copy columns B and C?
Thank you
Shaun
I have a table with four columns Month, Brand Name, Executive Name, and Shop Name.
I am trying to find out, how many shop can sale executive wise month wise brand wise. I am trying count shop name but not get success.
Hope you are able to help. Thanks in advance.
Month Shop Name Executive Name Brand Name
Jan.20 Shiv Shakti & Sons Hari Mohan Red Pen
Jan.20 Body Shop Rajesh Singh Blue Pen
Jan.20 Stop & Shop Govind Ram Green Pen
Feb.20 Shiv Shakti & Sons Hari Mohan Blue Pen
Feb.20 Body Shop Rajesh Singh Green Pen
March.20 Shiv Shakti & Sons Hari Mohan Red Pen
March.20 Body Shop Rajesh Singh Blue Pen
March.20 Stop & Shop Govind Ram Green Pen
March.20 Shop Clue Jai Prakesh Red Pen