-
Jul 20th, 2016, 06:45 AM
#1
Thread Starter
Addicted Member
[RESOLVED] [Excel] VBA for summing up one column based on the unique value in another column
Dear all,
I have the following data in a worksheet
Col A Col B
Country Total
UK 100
USA 200
HK 300
UK 400
USA 50
Ireland 0
What I want to have is, i need a VBA procedure to read the range of Col A & B and then sum up Col B based on the unique country in Col A and output the result to Col C & D respectively.
One more criteria is added in the output. I would like to exclude those records that have total = 0 in the output. Thus, the expected output is as below:
Col C Col D
Country Total
UK 500
USA 250
HK 300
Actually, I have tried to seek help via google search. However, most of the people suggest to use formula instead of VBA code.
Could anyone help? Thanks in advance
Last edited by lok1234; Jul 20th, 2016 at 06:51 AM.
I can still live in my current job because I am here
-
Jul 20th, 2016, 07:44 AM
#2
Re: [Excel] VBA for summing up one column based on the unique value in another column
it is pretty easy to loop through all the cells in column A to get a running total, but easier to use excel built in formulas (sumif and countif) even in code to get the results
here is an example of just one of many methods
Code:
Set rng = Range("a:a") ' change range to suit
rw = 2
For Each cel In rng
If IsEmpty(cel) Then Exit For
If WorksheetFunction.CountIf(cel.Offset(-cel.Row + 1).Resize(cel.Row), cel) = 1 Then
tot = WorksheetFunction.SumIf(rng, cel, rng.Offset(, 1))
If tot > 0 Then
Cells(rw, 5) = cel
Cells(rw, 6) = tot
rw = rw + 1
End If
End If
Next
Last edited by westconn1; Jul 20th, 2016 at 08:01 AM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jul 20th, 2016, 08:01 AM
#3
Re: [Excel] VBA for summing up one column based on the unique value in another column
This question comes up repeatedly in Excel forums.
To do the job in VBA typically you sort the rows on the country column
such that your rows are in consecutive country groups.
Then you use a loop to make one pass through the rows.
You hold the current country id and the running subtotal in variables.
If the country remains the same you add the row value to the total
If the country changes you stamp the subtotal in column C and reset the subtotal variable to zero. You have to handle the last row after the loop depending on whether it is a duplicate or not.
P.S. Since you are summing the entries the zero entries have no effect on the totals so there is no point in trying to exclude them unless your trying to get an average value where the count makes a difference.
Last edited by Gruff; Jul 21st, 2016 at 12:24 PM.
Burn the land and boil the sea
You can't take the sky from me
~T
-
Jul 20th, 2016, 08:47 AM
#4
Re: [Excel] VBA for summing up one column based on the unique value in another column
Along the lines of Gruff's suggestion:
Code:
Sub sumCountries()
Dim ws As Worksheet
Dim lr As Long
Dim j As Long
Dim rngKey As Range
Dim rngSort As Range
Dim country As String
Dim total As Long
Dim writeRow As Long
Set ws = ActiveSheet
With ws
.Range("c1").Value = "Country"
.Range("d1").Value = "Total"
lr = .Range("a" & Rows.Count).End(xlUp).Row
Set rngKey = .Range("a2:a" & lr)
Set rngSort = .Range("a1:b" & lr)
With .Sort
.SortFields.Clear
.SortFields.Add rngKey
.SetRange rngSort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
country = .Range("a2").Value
total = .Range("b2").Value
For j = 3 To lr + 1
If .Range("a" & j).Value = country Then
total = total + .Range("b" & j).Value
ElseIf total > 0 Then
writeRow = .Range("c" & Rows.Count).End(xlUp).Row + 1
.Range("c" & writeRow).Value = country
.Range("d" & writeRow).Value = total
country = .Range("a" & j).Value
total = .Range("b" & j).Value
Else
country = .Range("a" & j).Value
total = .Range("b" & j).Value
End If
Next j
End With
End Sub
-
Jul 20th, 2016, 11:16 AM
#5
Thread Starter
Addicted Member
Re: [Excel] VBA for summing up one column based on the unique value in another column
Originally Posted by westconn1
it is pretty easy to loop through all the cells in column A to get a running total, but easier to use excel built in formulas (sumif and countif) even in code to get the results
here is an example of just one of many methods
Code:
Set rng = Range("a:a") ' change range to suit
rw = 2
For Each cel In rng
If IsEmpty(cel) Then Exit For
If WorksheetFunction.CountIf(cel.Offset(-cel.Row + 1).Resize(cel.Row), cel) = 1 Then
tot = WorksheetFunction.SumIf(rng, cel, rng.Offset(, 1))
If tot > 0 Then
Cells(rw, 5) = cel
Cells(rw, 6) = tot
rw = rw + 1
End If
End If
Next
Many thanks for your help. May I know what the meaning of this code behind? CountIf(cel.Offset(-cel.Row + 1).Resize(cel.Row), cel)
I can still live in my current job because I am here
-
Jul 20th, 2016, 04:42 PM
#6
Re: [Excel] VBA for summing up one column based on the unique value in another column
May I know what the meaning of this code behind?
counts if the current cell value is in the cells above the current cell to see if that value has already been summed, to avoid duplicating the values into the new column
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jul 21st, 2016, 08:36 AM
#7
Thread Starter
Addicted Member
Re: [Excel] VBA for summing up one column based on the unique value in another column
Originally Posted by westconn1
counts if the current cell value is in the cells above the current cell to see if that value has already been summed, to avoid duplicating the values into the new column
Many thanks, all guys! I got the solution
I can still live in my current job because I am here
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|