Inserting rows after each group and sum a particular contents of a column based on the group

Question

I have data in consective rows, however they are sorted in name column, i.e. (which is 'A' as for now but could change)

>    A      B   C
>     Name1 123 rdt
>     Name1 256 efh
>     Name1 456 ikj
>     Name2 123 rfa
>     Name3 654 xxd
>     Name3 846 olp
>     Name6 841 yuf

I want to insert a row (or two, any number of rows) after each group of names i.e. and sum colum 'B'

  A     B   C
    Name1   123 rdt
    Name1   256 efh
    Name1   456 ikj
        =sum(above number of Name1s)
    Name2   123 rfa
        =sum(above number of Name2s)
    Name3   654 xxd
    Name3   846 olp
        =sum(above number of Name3s)
    Name6   841 yuf

I have similar problem to the question here AWK: Insert a row after each group of data AWK: Insert a row after each group of data but with Excel.

Also, is it possible to do that in Python with openpyxl, if so how? Please!

Thank you

please take the time to format it correctly for better readability — nj2237, Mar 18 '18 at 16:48
It was beautifully formatted before uploading, but when i posted it got messed up. How can I upload a picture? — Jawad Mansoor, Mar 18 '18 at 16:52
Kindly refer to my profile picture so that it becomes easier to see what I am asking. Please — Jawad Mansoor, Mar 18 '18 at 16:55
i think new users cannot link inline images. in future, use code format for tables (4 spaces before the line) — nj2237, Mar 18 '18 at 16:56
Just use either the `Data --> Subtotal` wizard; or a Pivot Table. — Ron Rosenfeld, Mar 18 '18 at 19:30

score 0 · Answer 1 · answered Mar 18 '18 at 17:15

quick and dirty

Option Explicit

Sub main()
    Dim i As Long
    Dim sum As Double

    With Range("A1").CurrentRegion
        i = 1
        Do
            If .Cells(i + 1, 1) = .Cells(i, 1) Then
                sum = sum + .Cells(i, 2)
                i = i + 1
            Else
                sum = sum + .Cells(i, 2)
                i = i + 1
                .Rows(i).Insert xlDown
                .Cells(i, 2).Value = sum
                i = i + 1
                sum = 0
            End If
        Loop While i <= .Rows.Count
    End With
End Sub

Thanks I will try and give feed back, (accept your answer and rate it), after I start windows. (on Linux right now dual boot). But this means there is no Python code for this? — Jawad Mansoor, Mar 18 '18 at 17:18

Inserting rows after each group and sum a particular contents of a column based on the group

1 Answers1