Home » Forums » Open Source Learning Center » Question and Answer

Sum amount based on same reference id

Submitted by djfscouse on 15 February, 2013 - 22:24

Hi,

If I have a excel 2003 file with a few rows/columns as follows

1234B,AB900,1
1234B,AB900,1
1244A,AB901,1
1267B,AB906,2
1279B,AB915,1
1279B,AB915,3
1279B,AB915,1
1288A,AB922,2

Is it possible to create a new line below each group of id and enter the total of column C using VBA

example:

1234B,AB900,1
1234B,AB900,1
2
1244A,AB901,1
1
1267B,AB906,2
2
1279B,AB915,1
1279B,AB915,3
1279B,AB915,1
4
1288A,AB922,2
2

Thanks for any help,

David

‹ VBA CODE IF YOU CLICK VISUAL BASIC EDITOR Insert start and finish between data ›
»
Shane Devenshire's picture

Sum amount based on same reference ID

Submitted by Shane Devenshire on 17 February, 2013 - 20:28.

In your example you show 4 under the 3 1279B's ???

Try this, to run it you place your cursor in the first cell with account numbers. It enters a COUNTA fourmula which allows you to insert items later and still get correct results. Or you can just enter the numbers, which I did not do.

Sub test()
Dim topRow As Integer
topRow = 0
Do Until ActiveCell = ""
If ActiveCell <> ActiveCell.Offset(1, 0) Then
ActiveCell.Offset(1, 0).Activate
ActiveCell.Resize(1, 3).Insert Shift:=xlDown
ActiveCell = "=COUNTA(R" & topRow + 1 & "C:R[-1]C)"
topRow = ActiveCell.Row
End If
ActiveCell.Offset(1, 0).Activate
Loop
End Sub

Shane

»