Produce list of months/year from list

[redspanna]

Hey all

Is there any formula or VB code that can look down the list of dates in column A and produce a dynamic list in oldest to newest order?

NDA.v6.xlsm
	A	B	C
1	DATE
2	01-Jan-20
3	03-Feb-20		January 2020
4	09-Feb-20		February 2020
5	20-Mar-20		March 2020
6	23-Mar-20		April 2020
7	04-Apr-20		January 2021
8	09-Apr-20		February 2021
9	05-Jan-21		September 2021
10	09-Feb-21		February 2022
11	11-Feb-21
12	17-Sep-21
13	03-Feb-22
Data (2)

The required result shown in column c

 

[Fluff]

And did it work?

 

[redspanna]

Yes and No, It has correctly added the date as required into I4:I14 (getting the data from my Data_Table3) but as you can see it places January 1900 into cell I3 (being the fisr cell the formula is pasted)

There are blank cells though column F, but all other columns will normally be populated with some value

NDA.v6.xlsm
	A	B	C	D	E	F	G	H	I
1	DATE	MONTH	YEAR	YEAR2	Q	SALES	DAY
2	01-Jan-20	Jan	2019-20	2020	Q1		Wed
3	03-Feb-20	Feb	2019-20	2020	Q1	140	Mon		January 1900
4	03-Mar-21	Mar	2020-21	2021	Q1		Wed		January 2020
5	05-Sep-21	Sep	2021-22	2021	Q3	70	Sun		February 2020
6	04-Dec-20	Dec	2020-21	2020	Q4	70	Fri		May 2020
7	11-Jun-22	Jun	2022-23	2022	Q2	960	Sat		December 2020
8	13-Aug-21	Aug	2021-22	2021	Q3	2000	Fri		February 2021
9	01-Jan-20	Jan	2019-20	2020	Q1		Wed		March 2021
10	03-Jan-22	Jan	2021-22	2022	Q1	70	Mon		August 2021
11	02-Sep-22	Sep	2022-23	2022	Q3	475	Fri		September 2021
12	19-May-20	May	2020-21	2020	Q2	70	Tue		January 2022
13	25-Feb-21	Feb	2020-21	2021	Q1	140	Thu		June 2022
Data (2)

Cell Formulas
Range	Formula
B2:B13	B2	=IF(ISBLANK(A2),"",TEXT(A2,"MMM"))
C2:C13	C2	=IF(ISBLANK(A2),"",YEAR(EOMONTH(A2,9))-1&"-"&RIGHT(YEAR(EOMONTH(A2,9)),2))
D2:D13	D2	=IF(ISBLANK(A2),"",YEAR(A2))
E2:E13	E2	=IF(ISBLANK(A2),"",VLOOKUP(B2,Workings!$A$4:$C$15,3,FALSE))
G2:G13	G2	=IF(ISBLANK(A2),"",TEXT(A2,"DDD"))
I3:I13	I3	=IFERROR(AGGREGATE(15,6,(Data_Table3[DATE]-DAY(Data_Table3[DATE])+1)/ISNA(MATCH(Data_Table3[DATE]-DAY(Data_Table3[DATE])+1,I$2:I2,0)),1),"")

 

[Fluff]

The only way I can get a date of Jan 1900 is if there is a blank cell in the date column. Does the table extend below the last row with data?

 

[redspanna]

so I've copied the table that shows whats further down....

NDA.v6.xlsm
	A	B	C	D	E	F	G	H	I
12	19-May-20	May	2020-21	2020	Q2	70	Tue		January 2022
13	25-Feb-21	Feb	2020-21	2021	Q1	140	Thu		June 2022
14
15
16
17
18
Data (2)

Cell Formulas
Range	Formula
B12:B18	B12	=IF(ISBLANK(A12),"",TEXT(A12,"MMM"))
C12:C18	C12	=IF(ISBLANK(A12),"",YEAR(EOMONTH(A12,9))-1&"-"&RIGHT(YEAR(EOMONTH(A12,9)),2))
D12:D18	D12	=IF(ISBLANK(A12),"",YEAR(A12))
E12:E18	E12	=IF(ISBLANK(A12),"",VLOOKUP(B12,Workings!$A$4:$C$15,3,FALSE))
I12:I13	I12	=IFERROR(AGGREGATE(15,6,(Data_Table3[DATE]-DAY(Data_Table3[DATE])+1)/ISNA(MATCH(Data_Table3[DATE]-DAY(Data_Table3[DATE])+1,I$2:I11,0)),1),"")
G12:G18	G12	=IF(ISBLANK(A12),"",TEXT(A12,"DDD"))

As you can see Columns B:D and G all have formula populated whereas Column A is completly blank
So, yes, the DATE column (A) does have blank cells

Sorry for any confusion, and if this is too much of a pain, I can just live with the original formula

 

[Fluff]

The whole point of having a table is that when you add data formulae carry down & therefore you should not have blank rows in the table. If you delete the blank rows, then the formula will work.

 

[redspanna]

The whole point of having a table is that when you add data formulae carry down & therefore you should not have blank rows in the table. If you delete the blank rows, then the formula will work.

OK, got it

Realy appreciate your help with this = many thanks

 

[Peter_SSs]

Since it seems you have converted to a table and removed the blank rows, you could reconsider the formulas inside the table to use the table nomenclature as well as eliminate the check for blanks.
Plus I have suggested a couple of other minor changes.

redspanna.xlsm
	A	B	C	D	E	F	G
1	DATE	MONTH	YEAR	YEAR2	Q	SALES	DAY
2	01-Jan-20	Jan	2019-20	2020	Q1		Wed
3	03-Feb-20	Feb	2019-20	2020	Q1	140	Mon
4	03-Mar-21	Mar	2020-21	2021	Q1		Wed
5	05-Sep-21	Sep	2021-22	2021	Q3	70	Sun
6	04-Dec-20	Dec	2020-21	2020	Q4	70	Fri
7	11-Jun-22	Jun	2022-23	2022	Q2	960	Sat
8	13-Aug-21	Aug	2021-22	2021	Q3	2000	Fri
9	01-Jan-20	Jan	2019-20	2020	Q1		Wed
10	03-Jan-22	Jan	2021-22	2022	Q1	70	Mon
11	02-Sep-22	Sep	2022-23	2022	Q3	475	Fri
12	19-May-20	May	2020-21	2020	Q2	70	Tue
13	25-Feb-21	Feb	2020-21	2021	Q1	140	Thu
Sheet2

Cell Formulas
Range	Formula
B2:B13	B2	=TEXT([@DATE],"mmm")
C2:C13	C2	=YEAR(EOMONTH([@DATE],-3))&TEXT(EOMONTH([@DATE],9),"-yy")
D2:D13	D2	=YEAR([@DATE])
E2:E13	E2	="Q"&INT((MONTH([@DATE])-1)/3)+1
G2:G13	G2	=TEXT([@DATE],"ddd")

 

[redspanna]

Thanks for updated formula @Peter_SSs

 

[Rick Rothstein]

I did not follow this thread originally and I am not entirely sure where it stands now, but I wanted to note that since the OP is using XL365, the following formula will produce a spilled list like the OP said he wanted originally...

=TEXT(SORT(0+UNIQUE(TEXT(A2:A13,"mmmm yyyy"))),"mmmm yyyy")

 

[Peter_SSs]

.. but I wanted to note that since the OP is using XL365, the following formula will produce a spilled list ..

Good point Rick. A couple of comments though ..

In the OP's original mini-sheet the values in column C are actually dates, (1st of each relevant month) so the mmmm yyyy formatting must be from the cell formatting. Therefore it should only require this from the formula I believe

=SORT(0+UNIQUE(TEXT(A2:A13,"mmmyy")))

Further, since the request was for a "dynamic list" and a table structure is now being used, this should give an automatically expanding/contracting list as the table changes.
(As earlier, still assuming no blanks in the DATE column of the table)

=SORT(0+UNIQUE(TEXT(Data_Table3[DATE],"mmmyy")))