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

Neustria

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? – Cheiron

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. – Neustria

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

Have you tried AutoFilter and\or PivotTable? – Consternation

Answer 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

Alopecia

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

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

@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 – Alopecia

@Gammer I have edit the code, try it now, just change the cell for oRng – Alopecia

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

@Gammer 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 – Alopecia

How can I do this exact thing in Google Sheets? – Susceptible

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

Answer 2

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.

Garv

Answer 3

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.

Embryonic

Answer 4

Figured it out.

Step 1

Put a new column to the left of column1 and copy+paste the following formula

=B2=B3

=B3=B4

=B4=B5

... all the way to the bottom (assume column B here is column1 in the original question).

This formula evaluates whether or not the next row is a new value in column1. Deopending on the result, you will have TRUE or FALSE. Copy and Paste these results as values and then swap "FALSE" for nil and "TRUE" for 0.5

Step 2

Then add that column full of only 0.5's to the column1 which will yield the following table:

  newcolumn0  |   column1 ("B") |   column2   |  column3
-----------------------------------------------------
              |     1           |     small   |  blue
              |     1           |     small   |  orange
      1.5     |     1           |     small   |  yellow
              |     2           |     med     |  yellow
      2.5     |     2           |     med     |  blue
      3.5     |     3           |     large   |  green
              |     4           |     large   |  green
      4.5     |     4           |     small   |  pink

Step 3

Lastly, copy and paste the values from newcolumn0 right below the values in column1 and then sort the table by column1 and you should have a blank row in between each distinct whole number in column1, with the table something like this:

    newcolumn0   |  column1 ("B")  |   column2       |  column3
---------------------------------------------------------------
                 |     1           |     small   |  blue
                 |     1           |     small   |  orange
        1.5      |     1.5         |             |
                 |     1           |     small   |  yellow
                 |     2           |     med     |  yellow
                 |     2           |     med     |  blue
        2.5      |     2.5         |             |
                 |     3           |     large   |  green
        3.5      |     3.5         |             |
                 |     4           |     large   |  green
                 |     4           |     small   |  pink
        4.5      |     4.5         |             |

Alternative Solutions (still no VBA)

1. Put a value of 1 Column 1, Row 2 (assume this is A2)
2. Put this formula in A3 =IF(B3=B2,A2,A2+1) and copy+paste this formula for the rest of column 2
3. Then copy and paste all the values from column 1 into a new temp excel sheet, remove duplicates, then add 0.5 to all numbers, then paste these values below the values in original spreadsheet below the data in column 1, paste all data in column as values and then sort by that column, delete the temp excel sheet

Neustria

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

Answer 5

Just an idea, if you know the categories, as small, medium, and large mentioned above...

At the bottom of the sheet, make 3 rows that only say small, medium, and large, change the font to white, and then sort so that it alphabetizes, placing a blank row between each section.

Hunchbacked

Answer 6

1. Insert a column at the left of the table 'Control'
2. Number the data as 1 to 1000 (assuming there are 1000 rows)
3. Copy the key field to another sheet and remove duplicates
4. Copy the unique row items to the main sheet, after 1000th record
5. In the 'Control' column, add number 1001 to all unique records
6. Sort the data (including the added records), first on key field and then on 'Control'
7. A blank line (with data in key field and 'Control') is added

Doublecross

Answer 7

I have a large file in excel dealing with purchase and sale of mutual fund units. Number of rows in a worksheet exceeds 4000. I have no experience with VBA and would like to work with basic excel. Taking the cue from the solutions suggested above, I tried to solve the problem ( to insert blank rows automatically) in the following manner:

1. I sorted my file according to control fields
2. I added a column to the file
3. I used the "IF" function to determine when there is a change in the control data .
4. If there is a change the result will indicate "yes", otherwise "no"
5. Then I filtered the data to group all "yes" items
6. I copied mutual fund names, folio number etc (no financial data)
7. Then I removed the filter and sorted the file again. The result is a row added at the desired place. (It is not entirely a blank row, because if it is fully blank, sorting will not place the row at the desired place.)
8. After sorting, you can easily delete all values to get a completely blank row.

This method also may be tried by the readers.

Russon