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.)
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
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 SubNow 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.