Show subtotals in multiple columns

Question

What I'm trying to do is to SUM IF the value is 1 in every subtotal created . I've tried using Subtotal option but I can't make it work (maybe because I'm not good at using Excel) and sometime I get 0 and 1 in groups with the sum. So I decide to make a VBA.

This is almost the group of data

- |    A    |    B    |  C  |
-----------------------------
1 | Names   | Points  |  N  |
-----------------------------
2 | Grimer  |   1     |  88 |
3 | Grimer  |   1     |  88 |
4 | Grimer  |   0     |  88 |
5 | Psyduck |   1     |  54 |
6 | Psyduck |   0     |  54 |
7 | Psyduck |   0     |  54 |
8 | Pikachu |   1     |  25 | 
9 | Pikachu |   1     |  25 |
10| Pikachu |   1     |  25 |

And this is what I'm trying to get:

     |    A    |    B    |  C  |
-------------------------------
   1 | Names   | Points  |  N  |
-------------------------------
|  2 | Grimer  |   1     |  88 |
|  3 | Grimer  |   1     |  88 |
|  4 | Grimer  |   0     |  88 |
-  5 | Grimer  |   2     |  88 |
|  6 | Psyduck |   1     |  54 |
|  7 | Psyduck |   0     |  54 |
|  8 | Psyduck |   0     |  54 |
-  9 | Psyduck |   1     |  54 |
|  10| Pikachu |   1     |  25 | 
|  11| Pikachu |   1     |  25 |
|  12| Pikachu |   1     |  25 |
-  13| Pikachu  |   3     |  25 |

Those dashed at the beginning of every row (starting from 2) are the groups that I want to create (and the B cell of every row is the result.. it should be bold). The following code is to get the previous result (the table). It works but I would like to know if there is a way if there is a better solution doing it on Excel without programming. Or... Is there any error because when I run it, it takes a few seconds (for more than 4000 rows).

Sub GetPointsByPokemon()

    With ThisWorkbook.ActiveSheet

        Dim Fila As Integer, InitRow As Integer, Suma As Integer

        PkNumber = .Range("C2").Value
        InitRow = 2
        ActualRow = 2
        Suma = 0

        Do Until .Cells(ActualRow, 1).Value = ""
            If .Cells(ActualRow, 2).Value = 1 Then
                Suma = Suma + 1
            End If

            If PkNumber <> .Range("C" & (ActualRow + 1)).Value Then
                .Cells(ActualRow, 1).Offset(1).EntireRow.Insert
                PkNumber = .Range("C" & (ActualRow + 2)).Value

                .Range(ActualRow & ":" & ActualRow).Offset(1).Value = .Range(ActualRow & ":" & ActualRow, 1).Value

                .Range("B" & (ActualRow + 1)).Value = Suma
                .Rows(InitRow & ":" & ActualRow).Group

                ActualRow = ActualRow + 1
                InitRow = ActualRow + 1
                Suma = 0
            End If

            ActualRow = ActualRow + 1
        Loop

    End With
End Sub

All the data is an example and I had to change some values on the code. I have done it using MySQL but I really want to know how to do it on excel (and vba) Thanks in advance!

Answer 1

One way without VBA:

1. Choose Data | Subtotal ... At each change in: Names Use function: Sum, Check: Points and N.

2. Select column C and choose to replace (Ctrl+H) the 9, in the subtotal formula with another summary function such as: 1, (average), 4, (max), or 5, (min).

Answer 2

Personally, I like Bulbasaur.

I also don't like this approach, because you are hardcoding subtotals onto your worksheet and weaving them into your source data.

But here is the code if you still want to do this. It is ugly but it works.

Sub AddSubtotals()

  Dim i As Long
  Dim numberOfRows As Long

  ' number of pokemon
  numberOfRows = Cells(Rows.Count, "A").End(xlUp).Row

  ' do bottom row first
  Cells(numberOfRows + 1, 1).value = Cells(numberOfRows, 1).value
  Cells(numberOfRows + 1, 2).FormulaR1C1 = "=SUMIF(R[-" & numberOfRows - 1 & "]C[-1]:R[-" & numberOfRows - (numberOfRows - 1) & "]C[-1],""" & Cells(numberOfRows, 1).value & """,R[-" & numberOfRows - 1 & "]C[0]:R[-" & numberOfRows - (numberOfRows - 1) & "]C[0])"
  ' convert to value
  Cells(numberOfRows + 1, 2).value = Cells(numberOfRows + 1, 2).value
  Cells(numberOfRows + 1, 3).value = Cells(numberOfRows, 3).value

  Range(Cells(numberOfRows + 1, 1), Cells(numberOfRows + 1, 3)).Font.Bold = True

  ' insert blank row in between each group of pokemon
  ' loop backwards because we are inserting rows
  For i = numberOfRows To 3 Step -1
    If Cells(i, 1).value <> Cells(i - 1, 1).value Then
      Cells(i, 1).EntireRow.Insert xlShiftDown

      ' copy pokemon name down
      Cells(i, 1).value = Cells(i - 1, 1).value

      ' put formula into Points field
      Cells(i, 2).FormulaR1C1 = "=SUMIF(R[-" & i - 1 & "]C[-1]:R[-" & i - (i - 1) & "]C[-1],""" & Cells(i, 1).value & """,R[-" & i - 1 & "]C[0]:R[-" & i - (i - 1) & "]C[0])"
      ' convert to value
      Cells(i, 2).value = Cells(i, 2).value

      ' copy N value down
      Cells(i, 3).value = Cells(i - 1, 3).value

      Range(Cells(i, 1), Cells(i, 3)).Font.Bold = True

    End If
  Next i

End Sub

Sample data before code:

Sample data after code: