Inserting a a totals row every nth row.

[Jake87]

I have a list that looks like this:


Site-----Month--Use
Site A----Jan---10
Site A----Feb---5
Site B ----Jan---12
Site B ----Feb---6
Site C----Jan---5
Site C----Feb---3

I need something that will insert a row underneath each individual site grouping and that will sum the use column to look like this:

Site-------Month-Use
Site A------Jan---10
Site A------Feb---5
Total-------------15
Site B ------Jan---12
Site B ------Feb---6
Total-------------18
Site C------Jan---5
Site C------Feb---3
Total--------------8

I understand and can use a pivot table to do this, however i am looking for an alternative, as a pivot table will not work for what i need this to do. Can this be done somehow by pasteing a totals row every nth row or through a macro?

Thanks for any help in advance!

 

[Domenic]

Have you already looked at the Subtotal feature...

Data > Outline > Subtotal

or

Data > Subtotals

 

[VoG]

Try this macro

Sub sbttls()
Dim LastRow As Long, i As Long, aArea As Range
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = LastRow To 3 Step -1
    If Range("A" & i).Value <> Range("A" & i - 1).Value Then
        Rows(i).Insert
    End If
Next i
For Each aArea In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants).Areas
    Cells(aArea.Row + aArea.Rows.Count, 1).Value = "Total"
    Cells(aArea.Row + aArea.Rows.Count, 3).Value = WorksheetFunction.Sum(Range(Cells(aArea.Row, 3), Cells(aArea.Row + aArea.Rows.Count - 1, 3)))
Next aArea
End Sub

 

[theta]

Guys wasn't there a formula based version of this. Can't recall off hand, believe it uses the current ROW() + an incremental, then takes the MOD as the true / false vs the incremental to insert a formula or not. Can work every nth row....

 

[Jake87]

I never knew about the subtotals option. It's very similar to the pivot table, but it might do the trick. If not the macro posted is close to working but it does a couple things wrong, but might do the trick if I modify it a little bit. Thanks so much for the help from both of you.

-Jake

@Theta

A formula version of that is exactly what i am looking for any help regarding finding that would be perfect.

Thanks in advance.

-Jake

 

[Smitty]

Welcome to the Board!

Excel's native Subtotal does use formulas. It just does it for you.

 

[Jake87]

Thanks for the welcome!

It's looking pretty good, but there is one problem.

Pretend that in my example I have two use columns. One usage from 2009 and one from 2010. I need the totals row underneath to total only the first 9 months or so from 2009 so that they can be compared to the first 9 months total of 2010,(As the current year is not completed yet.)

Any suggestions for this?

-Jake

 

[zakasnak]

Vog,

I tried your macro & it seemed to work as designed. Can you help me make some modifications to it?

I would like for a row to be inserted between Books & Games, labeled Boutique in column C. Columns A & B should reflect the same store number & DM as above & below. YES?

Thanks for any help you can offer.



My sheet is set up as so:

Removed HTML Image - It was blowing up the page - Try a smaller sample - Moderator

 

[zakasnak]

Is this better?

we 20101002

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Trebuchet MS,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 35px"><COL style="WIDTH: 81px"><COL style="WIDTH: 47px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 37px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">Store</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">DL</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">Dept</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 8pt"> </TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: left">Chain</TD><TD style="FONT-SIZE: 8pt">Books</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 8pt"> </TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: left">Chain</TD><TD style="FONT-SIZE: 8pt">Games</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 8pt"> </TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: left">Chain</TD><TD style="FONT-SIZE: 8pt">Music</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 8pt"> </TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: left">Chain</TD><TD style="FONT-SIZE: 8pt">Movies</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #ccffff"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: left">Chain</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #ccffff">Total</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">9602</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: left">Little</TD><TD style="FONT-SIZE: 8pt">Books</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">9602</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: left">Little</TD><TD style="FONT-SIZE: 8pt">Games</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">9602</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: left">Little</TD><TD style="FONT-SIZE: 8pt">Music</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">9602</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: left">Little</TD><TD style="FONT-SIZE: 8pt">Movies</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: center">9602</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: left">Little</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #ccffcc">Total</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">9603</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: left">Miller</TD><TD style="FONT-SIZE: 8pt">Books</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">9603</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: left">Miller</TD><TD style="FONT-SIZE: 8pt">Games</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">9603</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: left">Miller</TD><TD style="FONT-SIZE: 8pt">Music</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">9603</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: left">Miller</TD><TD style="FONT-SIZE: 8pt">Movies</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: center">9603</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: left">Miller</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #ccffcc">Total</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">9604</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: left">Sandifer</TD><TD style="FONT-SIZE: 8pt">Books</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">9604</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: left">Sandifer</TD><TD style="FONT-SIZE: 8pt">Games</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">9604</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: left">Sandifer</TD><TD style="FONT-SIZE: 8pt">Music</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">9604</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: left">Sandifer</TD><TD style="FONT-SIZE: 8pt">Movies</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: center">9604</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: left">Sandifer</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #ccffcc">Total</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[VoG]

I don't understand what you are trying to total.