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 question in this post: Extract dates using a drop down list
Update! 2017-08-23, a smaller easier regular formula:
You can also easily extract a unique distinct list of dates with a pivot table:
Discover Pivot Tables – Excel’s most powerful feature and also least known
A pivot table allows you to examine data more efficiently, it can summarize large amounts of data very quickly and is very easy to use.
Discover Pivot Tables – Excel’s most powerful feature and also least known
Explaining formula in cell
Step 1 - Convert dates into a given format
The TEXT function lets you convert a value based on a formatting code, in this case we want to convert the date into month and year.
The reason is we want to count previous values against this array to make sure no duplicates show up.
TEXT($B$3:$B$10,"MMM-yyyy")
becomes
TEXT({40093;40103;40121;40128;40181;40192;40207;40217},"MMM-yyyy")
and returns
{"Oct-2009";"Oct-2009";"Nov-2009";"Nov-2009";"Jan-2010";"Jan-2010";"Jan-2010";"Feb-2010"}
Step 2 - Count previous values
The COUNTIF function allows us to count values displayed above the current cell using an expanding cell reference.
COUNTIF($D$2:D2,TEXT($B$3:$B$10,"MMM-yyyy"))
becomes
COUNTIF($D$2:D2,{"Oct-2009";"Oct-2009";"Nov-2009";"Nov-2009";"Jan-2010";"Jan-2010";"Jan-2010";"Feb-2010"})
becomes
COUNTIF("Unique distinct list",{"Oct-2009";"Oct-2009";"Nov-2009";"Nov-2009";"Jan-2010";"Jan-2010";"Jan-2010";"Feb-2010"})
and returns
{0;0;0;0;0;0;0;0}
Step 3 - Values represented by a zero has not been displayed yet
COUNTIF($D$2:D2,TEXT($B$3:$B$10,"MMM-yyyy"))=0
becomes
{0;0;0;0;0;0;0;0}=0
and returns
{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}
Step 4 - Divide 1 with array
1/(COUNTIF($D$2:D2,TEXT($B$3:$B$10,"MMM-yyyy"))=0)
becomes
1/{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}
and returns
{1;1;1;1;1;1;1;1}
Step 5 - Return date value based on criteria
LOOKUP(2,1/(COUNTIF($D$2:D2,TEXT($B$3:$B$10,"MMM-yyyy"))=0),$B$3:$B$10)
becomes
LOOKUP(2,{1;1;1;1;1;1;1;1},$B$3:$B$10)
becomes
LOOKUP(2,{1;1;1;1;1;1;1;1},{40093;40103;40121;40128;40181;40192;40207;40217})
and returns 40217.
Step 6 - Convert to month and year
TEXT(LOOKUP(2,1/(COUNTIF($D$2:D2,TEXT($B$3:$B$10,"MMM-yyyy"))=0),$B$3:$B$10),"MMM-yyyy")
becomes
TEXT(40217,"MMM-yyyy")
and returns Feb-2010 in cell D3.
Download *.xlsx file
Extract unique distinct year and months from dates.xlsx
Recommended article:
The formula in cell D18 counts unique distinct months in cell range B3:B16. Formula in D18: =SUMPRODUCT((FREQUENCY(DATE(YEAR($B$3:$B$16), MONTH($B$3:$B$16), 1), DATE(YEAR($B$3:$B$16), […]
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 values based on a date range
List all unique events in a month
Question: I have a table with four columns, Date, Name, Level, and outcome. The range is from row 3 to […]
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 […]
Find latest date based on a condition
Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]
Formula for matching a date within a date range
Table of contents Match a date when a date range is entered in a single cell Match a date when […]
The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]
Identify overlapping date ranges
The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]
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 a unique distinct list from two columns
Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]
Vlookup – Return multiple unique distinct values
Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]
Create a unique distinct alphabetically sorted list
The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]
Extract a unique distinct list and sum amounts based on a condition
Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]
5 Responses to “Extract unique distinct year and months from dates”
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.
How to Get in Sorted Order (ASC or DESC)?
Thanks!
Qadeer Ahmed,
Great question. Formula in cell D2:
ASC
=TEXT(SMALL(IF(COUNTIF($D$1:D1, TEXT($A$2:$A$135, "mmm-YYYY")), "", $A$2:$A$135), ROWS($A$1:A1)), "mmm-YYYY")
DESC
=TEXT(LARGE(IF(COUNTIF($D$1:D1, TEXT($A$2:$A$135, "mmm-YYYY")), "", $A$2:$A$135), ROWS($A$1:A1)), "mmm-YYYY")
Thanks for helping Oscar, it works! :)
Not working for me.
The download file sorts from top to bottom in descending order.
The solution Oscar gave is giving errors.
Please help.
Brandon,
You need to adjust the bolded cell range to the cell above your selected cell.
=TEXT(SMALL(IF(COUNTIF($D$1:D1, TEXT($A$2:$A$135, "mmm-YYYY")), "", $A$2:$A$135), ROWS($A$1:A1)), "mmm-YYYY")
Did that help?