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

User · Answer

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

User · Answer

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

User · Answer

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  lt  gt 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

User · Answer

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    Put a value of 1 Column 1  Row 2  assume this is A2  Put this formula in A3  IF B3 B2 A2 A2 1  and copy paste this formula for the rest of column 2 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

User · Answer

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  amp  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

User · Answer

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

User · Answer

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    I sorted my file according to control fields I added a column to the file I used the  IF  function to determine when there is a change in the control data   If there is a change the result will indicate  yes   otherwise  no  Then I filtered the data to group all  yes  items I copied mutual fund names  folio number etc  no financial data  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   After sorting  you can easily delete all values to get a completely blank row    This method also may be tried by the readers

[excel] How to automatically insert a blank row after a group of data

Examples related to excel

Examples related to vba

Examples related to rollup

Examples related to blank-line