Adding Blank Row via formula/array

[Labrat24]

I would like to automatically add a blank row in a series of Dates so that when I graph the data there is a gap whenever there is a new month. This makes it alot easier to read when there is a bunch of data. The reason that I would like this done automatically is because I have several long lists of data that I can apply this to and it is very time consuming to manually add the blank rows. Can this be accomplished without a macro? I have an example below.

[TABLE="width: 294"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 68, bgcolor: transparent"]A
[/TD]
[TD="width: 64, bgcolor: transparent"]B
[/TD]
[TD="width: 64, bgcolor: transparent"]C
[/TD]
[TD="width: 68, bgcolor: transparent"]D
[/TD]
[TD="width: 64, bgcolor: transparent"]E
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]Date
[/TD]
[TD="bgcolor: transparent"]Data
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Date
[/TD]
[TD="bgcolor: transparent"]Data
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent, align: right"]1/5/2019
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1/5/2019
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3
[/TD]
[TD="bgcolor: transparent, align: right"]1/10/2019
[/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1/10/2019
[/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]4
[/TD]
[TD="bgcolor: transparent, align: right"]1/15/2019
[/TD]
[TD="bgcolor: transparent, align: right"]6
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1/15/2019
[/TD]
[TD="bgcolor: transparent, align: right"]6
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5
[/TD]
[TD="bgcolor: transparent, align: right"]1/30/2019
[/TD]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1/30/2019
[/TD]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]6
[/TD]
[TD="bgcolor: transparent, align: right"]2/1/2019
[/TD]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7
[/TD]
[TD="bgcolor: transparent, align: right"]2/8/2019
[/TD]
[TD="bgcolor: transparent, align: right"]12
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]2/1/2019
[/TD]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]8
[/TD]
[TD="bgcolor: transparent, align: right"]2/17/2019
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]2/8/2019
[/TD]
[TD="bgcolor: transparent, align: right"]12
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]9
[/TD]
[TD="bgcolor: transparent, align: right"]2/25/2019
[/TD]
[TD="bgcolor: transparent, align: right"]14
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]2/17/2019
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10
[/TD]
[TD="bgcolor: transparent, align: right"]3/1/2019
[/TD]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]2/25/2019
[/TD]
[TD="bgcolor: transparent, align: right"]14
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]11
[/TD]
[TD="bgcolor: transparent, align: right"]3/15/2019
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]12
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3/1/2019
[/TD]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]13
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3/15/2019
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Michael M]

I think a macro is the best way to go....
Press ALT + F11 to open the VBE
In the LH window double click on "This Workbook"
In the RH window where the cursor is flashing, paste the code below

Sub MM1()
Dim r As Long, lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 3 Step -1
        If Month(Range("A" & r).Value) <> Month(Range("A" & r - 1).Value) Then
            Rows(r).EntireRow.Insert
        End If
    Next r
End Sub

Close the VBE and then Press ALT + F8 and select the MM1 macro from the list and select run.

Job Done !!

 

[Labrat24]

Michael

Entering an entire row wont work. This is just a few columns of a much larger spreadsheet. That is one of the reasons I was asking for help. The list of dates that I have is several hundred long so it takes a little bit of time to add the space, not to mention I do this for several data sets.

 

[Michael M]

So, the first comment in your question was.....

I would like to automatically add a blank row

Now you say you don't want that....can you provde more information on what is required ??

 

[My Aswer Is This]

In Post 1 you said:

I would like to automatically add a blank row

You said Row. Not a few cells.
And a formula cannot enter rows. You would need a Vba script.

More specific details are needed.
​

 

[Labrat24]

Sorry for not being more specific. The reason that I dont think a macro will work is that my data is automatically dumped into excel using PI. I use the dates that are generated to pull additional information for that database. The way I have it set up now, I use a formula to organize the dates and remove duplicates. I have already tried "inserting" cells/rows into my dates and that ends up messing up other formulas that use the data. If there was a way to use a array/formula that would be very helpful, if not then I will continue doing it the way I have been because I dont think a macro will work. I really do appreciate the help but I understand if this is not possible.

 

[Labrat24]

Also "" or #N/A are acceptable in place of a blank cell

 

[Michael M]

You simply can't insert blank rows by formula !
I can't see why a macro wouldn't work if we have the correct information.....and inserting rows shouldn't affect the formulae if done correctly.
If you would like to upload the data to DropBox and then post a link back here, we may be able to have a play with it !!

 

[DRSteele]

You simply can't insert blank rows by formula !

Sure you can. That is to say, you can create an algorithm of formulas that report an array containing blanks according to conditions. The extracted data here is in ColL.

The formula in ColJ uses the new dynamic arrays from Excel365, but if you're still using old software the alternative formula in ColN will suffice (if invoked with Ctrl+Shift+Enter). I'm sure some industrious person can create a single-cell formula instead of the ones in ColF through CoL; I left the algorithm across several helper columns so it can be more easily audited.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
5	DataPoint	YEAR (ascending order)		desired output of YEAR		change?	number of changes above	implied blank row		row of desired output	row of desired output, blanks excluded	YEAR extracted		row of desired output (old Excel)
6	1	2001		2001		0	0	0		1	1	2001		1
7	2	2001		2001		0	0	0		2	2	2001		2
8	3	2001		2001		0	0	0		3	3	2001		3
9	4	2002				1	0	4		4				4
10	5	2002		2002		0	1	0		5	4	2002		5
11	6	2002		2002		0	1	0		6	5	2002		6
12	7	2002		2002		0	1	0		7	6	2002		7
13	8	2002		2002		0	1	0		8	7	2002		8
14	9	2003		2002		1	1	10		9	8	2002		9
15	10	2003				0	2	0		10				10
16	11	2003		2003		0	2	0		11	9	2003		11
17	12	2004		2003		1	2	14		12	10	2003		12
18	13	2005		2003		1	3	16		13	11	2003		13
19	14	2005				0	4	0		14				14
20	15	2006		2004		1	4	19		15	12	2004		15
21										16				16
22				2005						17	13	2005		17
23				2005						18	14	2005		18
24										19				19
25				2006						20	15	2006		20
Sheet27

Cell Formulas
Range	Formula
A6	=SEQUENCE(15)
F6	=IF(ROWS($B$6:B6)=1,0,--(B6<>B5))
G6	=IF(ROWS($B$6:B6)=1,0,COUNTIFS($F5:F$6,1))
H6	=F6*(ROWS($B$6:B6)+G6)
J6	=SEQUENCE(COUNT(F6:F20)+COUNTIFS(F6:F20,1))
K6	=IF(ROWS($B$6:B6)=1,1,IF(SUM(--(J6=$H$6:$H$20)),"",J6-COUNTIFS($K5:K$6,"")))
L6	=IFERROR(INDEX($B$6:$B$20,K6),"")
N6	=ROW(INDIRECT("1:"&ROWS($B$6:B20)+SUM($F$6:$F$20)))

[/FONT]