- Right-click on a cell in your month and/or year column, choose "Field Settings"
- Layout and Print tab
- Uncheck "Show items with no data"
P.S. This will hide months with no data within your desired period as well.
Also, since you control the source data, I find that adding permanent columns for years and months is great. For the months, I'd recommend having them as "2011-01-01, 2011-02-01, 2011-03-01" in your data and format the field as desired ("mm" or "mmm", etc.)
This can let you have huge amounts of data - just use Year and Month as row labels and/or filters to get the periods you want without the < and > totals that groups make.. Since they're raw data, the PT will always be simple and work predictably. It also lets you use features not-available on groups, such as regrouping them further in seasons or however you wish.
If you can add some intermediate formulas to your sheet, here's a possible solution.
Based on the ranges in your example:
Put in cell N123 This returns the part to the left of , or the whole cell if no ,
=IFERROR(LEFT($M123,FIND(",",$M123)-1),M123)
Put in cell O123 This returns the part to the right of , or - if no ,
=IFERROR(TRIM(MID($M123,FIND(",",$M123)+1,999)),"-")
Put in cell P123 This returns 1 if cell N123 is unique in the list, plus 1 if cell O123 is unique
=AND(ROW()+1=ROW($N$123:$N$127)+MATCH($N123,$N$123:$N$127,0),$N123<>"-")
+AND(ROW()=IFERROR(MATCH($O123,$N$123:$N$127,0),""),$O123<>"-")
+AND(ISNA(MATCH($N123,$N$123:$N$127,0)),ROW()+1=IFERROR(ROW($N$123:$N$127)
+MATCH($N123,$O$123:$O$127,0),FALSE),$N123<>"-")
+AND(ISNA(MATCH($O123,$N$123:$N$127,0)),ROW()+1=ROW($N$123:$N$127)
+MATCH($O123,$O$123:$O$127,0),O123<>"-")
Copy cells N123..P123 down to N123..P127
The number of unique items is =SUM(P123:Q127)
This assumes there are at most 2 items in each data cell. If this is not the case you will need to add more intermediate columns, and extend the formula in O123 accordingly. If there are many more than 2, this will quickly get out of hand.
FWIW, you say a VBA solution is not posible, but is a VBA user defined function ok?
It would look something like =CountUnique(M123:M127)
Best Answer
Select your entire sheet. Use the Sort function and choose the date column you want. Then order it from newest to oldest, press OK. Keep the entire sheet selected, now to to Remove Duplicates, unselect all columns and then choose the column you need unique values from. This method will put all newest dates at the top when it removes the duplicates, you're left with unique values that match to the top most (recent dates).