Inserting a blank row after a group of data, and performing a summation on that data

Question

I found this solution, and it is almost what I need. However, I also need some calculations performed on the data after it is formatted.

I would like to insert 2 blank rows after each group of data, and then sum certain columns after inserting the blank rows.

CURRENT TABLE:

column1  |   column2   |  column3  |  column4
 A       |     1       |  blue     |   .2
 A       |     2       |  orange   |   .1
 A       |     1       |  yellow   |   .5
 B       |     1       |  yellow   |   .4
 B       |     1       |  blue     |   .2
 C       |     2       |  green    |   .1
 D       |     1       |  green    |   .1
 D       |     3       |  pink     |   .15

_

DESIRED TABLE

Note: 2 blank rows are inserted after each distinct column1 value group, and the sum of the columns with nummerical values within each distinct group

column1   |   column2   |  column3  |  column4
  A       |     1       |  blue     |   .2
  A       |     2       |  orange   |   .1
  A       |     1       |  yellow   |   .5
          |     4       |           |   .8 
          |             |           |   
  B       |     1       |  yellow   |   .4
  B       |     1       |  blue     |   .2
          |     2       |           |   .6
          |             |           |   
  C       |     2       |  green    |   .1
          |     2       |           |   .1  
          |             |           |   
  D       |     1       |  green    |   .1
  D       |     3       |  pink     |   .15
          |     4       |           |   .25

score 0 · Answer 1 · answered Oct 20 '15 at 05:56

0

Five clicks for an out of the box solution, or dozens of lines of code. Your call. Here is the no-code approach.

Instead of re-inventing the wheel you could let Excel work FOR you and use a pivot table.

It depends on how dearly you want the columns in a particular order and how much effort you are prepared to throw at it, as opposed to about five clicks to arrive at this:

answered Oct 20 '15 at 05:56

teylyn

34,374
4
53
73

@pnuts, Let's tackle the finer points of pivot table displays after establishing that code is not required. The sort order is inconsistent in OP's sample, at least I cannot make out any logic. Pivot sort can be defined with another click and a half – teylyn Oct 20 '15 at 09:06

score 0 · Answer 2 · answered Oct 20 '15 at 05:59

Apply Subtotal twice (the second time without Replace current subtotal checked). Fill Row2 Column5 down to suit with:

 =R[-1]C[-4]=RC[-4]

Filter to select Contains total in Column1 and TRUE in Column5 and blank out the content of those rows. Unfilter and delete Column5.

Inserting a blank row after a group of data, and performing a summation on that data

2 Answers2