Sorting multiple years by month

[paulsolar]

Hi All

I know this is a big ask but I've been trying to find an easy way to do this over the weekend and havent got anywhere near what I'm trying to achieve.

I have a sheet "Installations" with 50k+ rows (and growing) and I want to sort the sheet with the dates descending (oldest date first) the dates are in column G which is the last used column.

The dates go back to 2017. Where I'm struggling is that I want to insert a row in between each month and add the Month and Year in this row column A.

So I would have the headers in row 1 columns A:G
Row 2 Column A would say Nov 2017
Row 3 to whatever was the last row in Nov 2017
Then last row for Nov 2017 + 1 would be Dec 2019 and so on up to the newest date at the bottom of the list.

Is this that is something that is achievable?

I'm really having difficulty with this, hence the post

Kind regards

Paul

 

[JamesCanale]

I always keep my data entry (or base data) apart from my desired outputs. And I like to use tables - it makes the formulas more readable and flexible.

MrExcelPlayground22.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Date	Item	Thing	Other Thing	Value 1	Value 2	Value 3			Date	Item	Thing	Other Thing	Value 1	Value 2	Value 3
2	11/1/2017	A	Beta	Gamma	38	45	86			Nov 2017
3	11/2/2017	B	Beta	Gamma	76	55	85			11/1/2017	A	Beta	Gamma	38	45	86
4	11/3/2017	C	Beta	Gamma	45	72	35			11/2/2017	B	Beta	Gamma	76	55	85
5	12/1/2017	D	Beta	Gamma	98	6	23			11/3/2017	C	Beta	Gamma	45	72	35
6	12/2/2017	E	Beta	Gamma	11	90	45			Dec 2017
7	12/3/2017	F	Beta	Gamma	10	15	9			12/1/2017	D	Beta	Gamma	98	6	23
8	12/4/2017	G	Beta	Gamma	50	90	92			12/2/2017	E	Beta	Gamma	11	90	45
9	1/1/2018	H	Beta	Gamma	26	34	57			12/3/2017	F	Beta	Gamma	10	15	9
10	1/2/2018	I	Beta	Gamma	37	76	58			12/4/2017	G	Beta	Gamma	50	90	92
11	1/3/2018	J	Beta	Gamma	39	34	54			Jan 2018
12	1/5/2018	K	Beta	Gamma	91	38	17			1/1/2018	H	Beta	Gamma	26	34	57
13										1/2/2018	I	Beta	Gamma	37	76	58
14										1/3/2018	J	Beta	Gamma	39	34	54
15										1/5/2018	K	Beta	Gamma	91	38	17
Sheet8

Cell Formulas
Range	Formula
J2:P15	J2	=LET(a,Table2,b,CHOOSECOLS(a,1),c,UNIQUE(EOMONTH(b,-1)+1),d,EXPAND(c,ROWS(c),7,""),e,VSTACK(d,a),SORT(e,1))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J2:J19	Expression	=K2=""	text	NO

The conditional formatting just changes the date format to Month-Year if the cell next to it is blank.

 

[paulsolar]

Hi James

Thats a good way of doing it, I never thought it would be possible to do it with a formula.

Ideally I wanted to do it in vba as I want it to run as part of an already existing macro. I'm wondering I could adapt this to work in vba ??

Cheers

Paul

 

[JamesCanale]

I'm not too savvy with VBA - I only use it when I absolutely need to. But I don't think this approach would be needed for VBA. I'd just have excel sort the data by date and I'd have VBA go through each row in the selection, and when the month changed, insert a row and put the new month in that inserted row.