Insert blank row, then continue filling in data until next condition is met (hard to explain in short)

[Bookender]

My problem is as such:
I have a sheet of data that is very frequently updated, but needs an easy overview for some other code to work. Basically, this data-sheet needs to be able to insert a blank row every time a specific condition is met. Actually, it's easier to show:

Input [TABLE="class: grid, width: 50"]
<TBODY>[TR]
[TD]Blue[/TD]
[TD]Sample 1[/TD]
[TD]Bat[/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD]Sample 2[/TD]
[TD]Cat[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sample 3[/TD]
[TD]Rat[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD]Sample 3[/TD]
[TD]Hat[/TD]
[/TR]
</TBODY>[/TABLE]



Desired output
[TABLE="class: grid, width: 50"]
<TBODY>[TR]
[TD]Blue[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sample 1[/TD]
[TD]Bat[/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sample 2[/TD]
[TD]Cat[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sample 3[/TD]
[TD]Rat[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sample 3[/TD]
[TD]Hat[/TD]
[/TR]
</TBODY>[/TABLE]


Basically, every time a cell in column A isn't blank, there should be a blank row - without losing the data from the other two columns.

I already have a simple array running that sorts everything by colour, as well as one that removes any duplicate colours. So, I just need to figure out a way that I can (non-manually) insert this blank row in my sheet. Please note that this data is not static. So I may one day have one Blue, the next day seven, and then four the day after...

 

[Juglaz]

Sub addrows()
Dim x As Integer
For x = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
    If Cells(x, 1) <> "" Then
        Cells(x, 1).EntireRow.Insert
        Cells(x + 1, 1).Cut
        Cells(x, 1).Select
        ActiveSheet.Paste
    End If
Next x
End Sub

 

[Bookender]

My sincerest apologies - I forgot to state that from the start.

I cannot use VBA (security issues; macros are disabled on the computers that this sheet is primarily run on). So, it has to be something that can be done solely with formulas.

 

[Juglaz]

This cannot be done with formulas alone.

 

[Bookender]

Well, that statement would be half-false.

I could use a bit of code that inserts an extra blank cell between each, off-set the Colour column by one to get the adjustment I want, and then run an array to remove the blank rows.

However, that code takes several minutes to compute due to the sheer size of the actual data (around 35 columns per row of data, and I have a few hundred rows) - hence I wondered if it was possible to do this in one move, rather than using the extremely time-consuming array that does little more than remove the blank rows.

 

[Bookender]

For the record, my insert-blank-space code (in column D) for the sample above would be:

IF(MOD(ROWS(D$1:D1),2),INDEX(A$1:A$4,INT((ROWS(D$1:D1)-1)/2)+1,0),"")

and then copied sideways and down as needed - adding an extra IF command to remove the 0s that will pop up in the event of a blank space. But it works like a charm, providing I want everything space out with one cell inbetween. Which, I do not.

And my l33t skillzors at base math isn't good enough for me to figure out how I can get the above code to work when it's not everything I want spaced. Perhaps someone brighter than me can come up with something to do with it.

 

[Bookender]

After much screaming, throwing of heavy things, and generally scaring off all of my colleagues, I have found a solution.

Using formulas. And 2 helper columns. And some IFERROR bits to make the code not throw a hysterical fit at the end.

Leaving the solution here in case someone else wants to do this with formulas, and meet the same "This cannot be done" mentality in others, as (evidenced above) I have.

Example table for the info we want to sort out:
[TABLE="class: grid, width: 50"]
<TBODY>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Red
[/TD]
[TD]Shoe
[/TD]
[TD]Bat
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Blue
[/TD]
[TD]Boot
[/TD]
[TD]Cat
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD]Fish
[/TD]
[TD]Rat
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Yellow
[/TD]
[TD]Tree
[/TD]
[TD]Hat
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD]Fork
[/TD]
[TD]Mat
[/TD]
[/TR]
</TBODY>[/TABLE]


First helper column figures out if there's any blanks in your Colour column (assume that D is where we put this helper column):
=IF(A2<>"",D1,D1+1)
NOTE! D2 has to be a 1, for the sake of making the whole code work. You can also just put a 1 in D1, and the code will work as well.

Now we can sort out the Colours in column F (leave E blank for now), by using our brand new helper-column and INDEX.
=IFERROR(IF(INDEX(A$2:A$6,D2,0)=F1,"",INDEX(A$2:A$6,D2,0)),"")
Thus, we get all Colours spaced apart so that they can have their own rows - IFERROR exists, because otherwise the code throws a fit once we get past Yellow.

Now to fill in column E, which is our second helper-column. You can probably make the code work without this second column, if you do some roundabout fiddling with the code, but I thought it was easier to use this one.
=IF(F2="",E1+1,E1)
What does this one count? It, more or less, counts if there's something in column F. It should start with a 0 (because F2 isn't blank) - that's on purpose.

Now we can fill in column G and H with our 4-Word and 3-Word respectively. Again, an IFERROR to ensure that we get everything sorted out neatly.
=IFERROR(IF($E2=$E1,"",INDEX(B$2:B$6,$E2),"")
This first checks our little second helper-column and ensures that we have blank spaces in the rows that is filled with Colour. And then it simply plugs in the 4-Word and 3-Words according to that.

End result? Columns F, G, and H contains the data set up the way I want it to.

[TABLE="class: grid, width: 50"]
<TBODY>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Red
[/TD]
[TD]Shoe
[/TD]
[TD]Bat
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]Red
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Blue
[/TD]
[TD]Boot
[/TD]
[TD]Cat
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]Shoe
[/TD]
[TD]Bat
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD]Fish
[/TD]
[TD]Rat
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]Blue
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Yellow
[/TD]
[TD]Tree
[/TD]
[TD]Hat
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD]Boot
[/TD]
[TD]Cat
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD]Fork
[/TD]
[TD]Mat
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD]Fish
[/TD]
[TD]Rat
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]3
[/TD]
[TD]Yellow
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD]Tree
[/TD]
[TD]Hat
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6
[/TD]
[TD]5
[/TD]
[TD][/TD]
[TD]Fork
[/TD]
[TD]Mat
[/TD]
[/TR]
</TBODY>[/TABLE]


Which was precisely what I wanted in the first place. And works entirely and fully through the use of formulas.