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

Bookender

New Member
Joined
Sep 18, 2013
Messages
47
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
BlueSample 1Bat
YellowSample 2Cat
Sample 3Rat
RedSample 3Hat

<TBODY>
</TBODY>



Desired output
Blue
Sample 1Bat
Yellow
Sample 2Cat
Sample 3Rat
Red
Sample 3Hat

<TBODY>
</TBODY>


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

Board Regular
Joined
Jul 2, 2013
Messages
177
Code:
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

New Member
Joined
Sep 18, 2013
Messages
47
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.
 

Bookender

New Member
Joined
Sep 18, 2013
Messages
47
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

New Member
Joined
Sep 18, 2013
Messages
47
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

New Member
Joined
Sep 18, 2013
Messages
47
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:
A
B
C
1
2
Red
Shoe
Bat
3
Blue
Boot
Cat
4
Fish
Rat
5
Yellow
Tree
Hat
6
Fork
Mat

<TBODY>
</TBODY>


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.

A
B
C
D
E
F
G
H
1
1
2
Red
Shoe
Bat
1
Red
3
Blue
Boot
Cat
1
1
Shoe
Bat
4
Fish
Rat
2
1
Blue
5
Yellow
Tree
Hat
2
2
Boot
Cat
6
Fork
Mat
3
3
Fish
Rat
7
4
3
Yellow
8
5
4
Tree
Hat
9
6
5
Fork
Mat

<TBODY>
</TBODY>


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

Forum statistics

Threads
1,082,045
Messages
5,362,862
Members
400,696
Latest member
Kclynn

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top