Question: How do I create an unique distinct list of dates formatted YEAR-MONTH, for example 2008-jan? See my dates series in column A, picture below.

extract-distinct-unique-year-and-month-from-a-date-series1

Answer:

Update: Read this post! Create unique distinct year and months from a long date listing in excel
extract-distinct-unique-year-and-month-from-a-date-series2

Here is the helper column formula (D2):

=DATE(YEAR(A2), MONTH(A2), 1) copied down to D15.

Here is the formula in E2:E15:

=IF(COUNT(IF(MATCH(List, List, 0)=(ROW(List)-ROW(List_start)+1), COUNTIF(List, "<"&List)+1, ""))>(ROW(List)-ROW(List_start)), YEAR(INDEX(List, MATCH(SMALL(IF(MATCH(List, List, 0)=(ROW(List)-ROW(List_start)+1), COUNTIF(List, "<"&List)+1, ""), (ROW(List)-ROW(List_start)+1)), IF(MATCH(List, List, 0)=(ROW(List)-ROW(List_start)+1), COUNTIF(List, "<"&List)+1, ""), 0)))&"-"&TEXT(INDEX(List, MATCH(SMALL(IF(MATCH(List, List, 0)=(ROW(List)-ROW(List_start)+1), COUNTIF(List, "<"&List)+1, ""), (ROW(List)-ROW(List_start)+1)), IF(MATCH(List, List, 0)=(ROW(List)-ROW(List_start)+1), COUNTIF(List, "<"&List)+1, ""), 0)), "mmm"), "") + CTRL + SHIFT + ENTER

Update: Read this post! Create unique distinct year and months from a long date listing in excel

Named ranges
List (D2:D15)
List_start (D2)

What is named ranges?

Download example workbook

Download excel sample file for this tutorial.
filter-cells-values-using-drop-down-list.xls
(Excel 97-2003 Workbook *.xls)


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.

ROW(reference) returns the rownumber of a reference

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

COUNT(value1;[value2])
Counts the number of cells in a range that contain numbers

OFFSET(reference,rows,cols, [height],[width])
Returns a reference to a range that is a given number of rows and columns from a given reference

This blog article is one out of thirteen articles on the same subject "unique".