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.

Answer:

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

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

• How to extract a unique distinct list from a column in excel
  
• Extract a unique distinct list from two columns using excel 2007 array formula
  
• Extract a unique distinct list from three columns in excel
• Extract distinct unique sorted year and month list from a date series in excel
  
• Create a unique distinct list from a date range in excel
  
• Unique values from multiple columns using array formulas
  
• Extract a unique distinct list sorted from A-Z from range in excel
  
• Sort a range by occurence using array formula in excel
  
• Filter unique distinct values from two ranges combined in excel 2007
• Create a unique list and sort by occurrances from large to small
  
• Unique list to be created from a column where an adjacent column has text cell values
  
• Create unique list from column where an adjacent column meets criteria
  
• How to create a unique distinct list where other columns meet two criteria