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]

How about

+Fluff 1.xlsm
	A	B	C
1	DATE
2	01/01/2020		Jan-20
3	03/02/2020		Feb-20
4	09/02/2020		Mar-20
5	20/03/2020		Apr-20
6	23/03/2020		Jan-21
7	04/04/2020		Feb-21
8	09/04/2020		Sep-21
9	05/01/2021		Feb-22
10	09/02/2021
11	11/02/2021
12	17/09/2021
13	03/02/2022
14
Main

Cell Formulas
Range	Formula
C2:C13	C2	=IFERROR(AGGREGATE(15,6,$A$2:$A$100/($A$2:$A$100<>"")/(ISNA(MATCH(TEXT($A$2:$A$100,"mmyyyy"),TEXT(C$1:C1,"mmyyyy"),0))),1),"")

 

[Peter_SSs]

Like this?

22 02 22.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
Month List

Cell Formulas
Range	Formula
C3:C13	C3	=IFERROR(AGGREGATE(15,6,($A$2:$A$13-DAY(A$2:A$13)+1)/ISNA(MATCH($A$2:$A$13-DAY(A$2:A$13)+1,C$2:C2,0)),1),"")

 

[redspanna]

@Peter_SSs and @Fluff
Thanks both - works just great!

Is it also possible to change the formula so that I can change the data held into a Table, so that the formula will automatically pick up any dates added at a later stage?
FYI the Table will simply be called Data Table

Thanks again guys

 

[Fluff]

Yes you can do that like

+Fluff 1.xlsm
	A	B	C
1	DATE
2	01/01/2020
3	03/02/2020		01/01/2020
4	09/02/2020		01/02/2020
5	20/03/2020		01/03/2020
6	23/03/2020		01/04/2020
7	04/04/2020		01/01/2021
8	09/04/2020		01/02/2021
9	05/01/2021		01/09/2021
10	09/02/2021		01/02/2022
11	11/02/2021
12	17/09/2021
13	03/02/2022
14
Master

Cell Formulas
Range	Formula
C3:C13	C3	=IFERROR(AGGREGATE(15,6,(Table2[DATE]-DAY(Table2[DATE])+1)/ISNA(MATCH(Table2[DATE]-DAY(Table2[DATE])+1,C$2:C2,0)),1),"")

 

[redspanna]

Yes you can do that like

+Fluff 1.xlsm
	A	B	C
1	DATE
2	01/01/2020
3	03/02/2020		01/01/2020
4	09/02/2020		01/02/2020
5	20/03/2020		01/03/2020
6	23/03/2020		01/04/2020
7	04/04/2020		01/01/2021
8	09/04/2020		01/02/2021
9	05/01/2021		01/09/2021
10	09/02/2021		01/02/2022
11	11/02/2021
12	17/09/2021
13	03/02/2022
14
Master

Cell Formulas
Range	Formula
C3:C13	C3	=IFERROR(AGGREGATE(15,6,(Table2[DATE]-DAY(Table2[DATE])+1)/ISNA(MATCH(Table2[DATE]-DAY(Table2[DATE])+1,C$2:C2,0)),1),"")

OK, I tried the changed formula however the first date comes out as January 1900 and and then the second row shows (correctly) January 2020

 

[Fluff]

Do you have any blank cells in the table?

 

[redspanna]

Do you have any blank cells in the table?

I've extended the data rows and what I'm working with is shown as example below..(Column A holds many rows of dates which includes dates up to today - but not copied in this example as far too many rows)

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-Jan-20	Jan	2019-20	2020	Q1		Fri		January 2020
5	04-Jan-20	Jan	2019-20	2020	Q1	70	Sat		February 2020
6	05-Jan-20	Jan	2019-20	2020	Q1	70	Sun		September 2022
7	06-Jan-20	Jan	2019-20	2020	Q1	960	Mon
8	07-Jan-20	Jan	2019-20	2020	Q1	2000	Tue
9	08-Jan-20	Jan	2019-20	2020	Q1		Wed
10	09-Jan-20	Jan	2019-20	2020	Q1	70	Thu
11	10-Sep-22	Sep	2022-23	2022	Q3	475	Sat
12	11-Jan-20	Jan	2019-20	2020	Q1	70	Sat
13	12-Jan-20	Jan	2019-20	2020	Q1	140	Sun
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	I3	=IFERROR(AGGREGATE(15,6,(Data_Table3-DAY(Data_Table3)+1)/ISNA(MATCH(Data_Table3-DAY(Data_Table3)+1,I$2:I2,0)),1),"")
I4:I13	I4	=IFERROR(AGGREGATE(15,6,($A$2:$A$13-DAY(A$2:A$13)+1)/ISNA(MATCH($A$2:$A$13-DAY(A$2:A$13)+1,I$2:I3,0)),1),"")

There could be blanks in column F (Sales) , where no sales made that day - all other columns should have a value for each day

 

[Fluff]

What exactly is Data_Table3 ?

 

[redspanna]

=IFERROR(AGGREGATE(15,6,(Table2[DATE]-DAY(Table2[DATE])+1)/ISNA(MATCH(Table2[DATE]-DAY(Table2[DATE])+1,C$2:C2,0)),1),"")

I changed your formula with Table2 to match the table name for my data - which is Data_Table3