Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

sum amounts based on column

0

Hi,

I need help to sum amounts in column F for Duplicates ID in column A .

the result should show ID  in column J and amounts in column K

I tried to write simple macro but doesn't give me what I want!

Sub sumup()
Dim i As Long
Dim lr As Long
Dim sh As Worksheet
Set sh = Sheets("SSS")
lr = sh.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lr
If Cells(i, 1) > 1 Then
Range("J1:K1") = Array("ID", "AMOUNT")
Cells(i + 1, 10) = Cells(i, 1)
Cells(i + 1, 11) = Cells(i, 6)
End If
Next
End Sub
Answer
Discuss

Answers

0
Selected Answer

Hi again Speed.

I think I know what you mean...

In the attached revised file, I've added a new variable SubT to collect the subtotal value for a given Customer # (ID) and changed the code (see bits in bold, including comments which you can delete):

Sub sumup()
    Dim i As Long
    Dim lr As Long
    Dim sh As Worksheet
    Dim SubT As Double

    Set sh = Sheets("SSS")
    lr = sh.Range("A" & Rows.Count).End(xlUp).Row
    ' clear any values
    Range("J:K") = ""

    For i = 2 To lr
    ' compare column A values
    If Cells(i, 1).Value = Cells(i + 1, 1).Value Then
            ' add to subtotal
            SubT = SubT + Cells(i, 6)

            Else
            ' output subtotal for Customer# and reset
            Range("J1:K1") = Array("Customer #", "Total")
            Cells(i, 10) = Cells(i, 1)
            Cells(i, 11) = SubT + Cells(i, 6)
            SubT = 0
        End If
    Next i
    ' resize columns
    Range("J:K").Columns.AutoFit

End Sub

Now the subtotals will appear on the last row for a given Customer #. (Other users should note that the column order is  right to left, not the left to right normally used in the Europe and the USA for example).

Hope this fixes your problem- if so, please remeber to mark this Answer as Selected.

Discuss

Discussion

perfect .
many thanks , John.
speed (rep: 42) Jun 27, '24 at 8:02 am
Glad that helped. Thanks for selecting my Answer, Speed.
John_Ru (rep: 6537) Jun 27, '24 at 9:51 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login