How to automatically insert a blank row after a group of data

Question

I have created a sample table below that is similar-enough to my table in excel that it should serve to illustrate the question. I want to simply add a row after each distinct datum in column1 (simplest way, using excel, thanks).

_

CURRENT TABLE:

column1   |   column2   |  column3
----------------------------------
  A       |     small   |  blue
  A       |     small   |  orange
  A       |     small   |  yellow
  B       |     med     |  yellow
  B       |     med     |  blue
  C       |     large   |  green
  D       |     large   |  green
  D       |     small   |  pink

_

DESIRED TABLE

Note: the blank row after each distinct column1

column1   |   column2   |  column3
----------------------------------
  A       |     small   |  blue
  A       |     small   |  orange
  A       |     small   |  yellow

  B       |     med     |  yellow
  B       |     med     |  blue

  C       |     large   |  green

  D       |     large   |  green
  D       |     small   |  pink

Solution 1

This does exactly what you are asking, checks the rows, and inserts a blank empty row at each change in column A:

sub AddBlankRows()
'
dim iRow as integer, iCol as integer
dim oRng as range

set oRng=range("a1")

irow=oRng.row
icol=oRng.column

do 
'
if cells(irow+1, iCol)<>cells(irow,iCol) then
    cells(irow+1,iCol).entirerow.insert shift:=xldown
    irow=irow+2
else
    irow=irow+1
end if
'
loop while not cells (irow,iCol).text=""
'
end sub

I hope that gets you started, let us know!

Philip

Solution 2

This won't work if the data is not sequential (1 2 3 4 but 5 7 3 1 5) as in that case you can't sort it.

Here is how I solve that issue for me:

Column A initial data that needs to contain 5 rows between each number - 5 4 6 8 9

Column B - 1 2 3 4 5 (final number represents the number of empty rows that you need to be between numbers in column A) copy-paste 1-5 in column B as long as you have numbers in column A.

Jump to D column, in D1 type 1. In D2 type this formula - =IF(B2=1,1+D1,D1) Drag it to the same length as column B.

Back to Column C - at C1 cell type this formula - =IF(B1=1,INDIRECT("a"&(D1)),""). Drag it down and we done. Now in column C we have same sequence of numbers as in column A distributed separately by 4 rows.

Solution 3

Select your array, including column labels, DATA > Outline -Subtotal, At each change in: column1, Use function: Count, Add subtotal to: column3, check Replace current subtotals and Summary below data, OK.

Filter and select for Column1, Text Filters, Contains..., Count, OK. Select all visible apart from the labels and delete contents. Remove filter and, if desired, ungroup rows.

Comments

[scott]

why? if you ever need to work with that table again it will be a pain. If it is simply for reading ability why not use conditional formatting?

[Ben]

It's a fair question. I want to be able to navigate between the groups while I edit them using CTRL+up/down arrow. And it wouldn't be any trouble at all to get the table back into original format w/out any rows.

[Our Man in Bananas]

Ben, I gave you a VBA answer to do it in one step

[EEM]

Have you tried AutoFilter and\or PivotTable?

[Our Man in Bananas]

I can also give you one without a loop if you like :)

[Joshua Stafford]

copy/paste should almost never be the selected answer. select Philip's.

[user3844877]

This is a fantastic solution , but when I change a1 to b1 or d1 it fails for some reason !

[Our Man in Bananas]

@user3844877: that's because cells(irow+1, 1) = column 1, so for b1, you need to use cells(irow+1, 2) or alternatively something like cells(irow+1, iCol) and set iCol with iCol=activecell.column

[Our Man in Bananas]

@user3844877 I have edit the code, try it now, just change the cell for oRng

[user3844877]

Thanks so much , 'cells(irow+1, 2)' for b1 woked fine !

[Our Man in Bananas]

@user3844877 you'd be better off looking at the new version of the code, as it's a better way of doing it without using SELECT or Activate

[Alex Li]

How can I do this exact thing in Google Sheets?

[Our Man in Bananas]

@AlexLi - you'd need to check macros in Sheets: developers.google.com/apps-script/guides/sheets/macros