group cells.......

  • Thread starter Thread starter RADO
  • Start date Start date
Hi

I've following information wanted to group by first
column and show the sum of quantities:


a b c
---- ----- ------
123 10.8
123 20.2
123 44
124 2
124 11
124 45


The desired result:

a b c
---- ----- ------
123 10.8 75 -> sum(10.8+20.2+44)
123 20.2
123 44
124 2 58 -> sum(2+11+45+0)
124 11
124 45
124 0


note: I don't want to write sum(a1:a3) , ... because
it depends on the number of rows has the same value.

any help would greatly appricieted.
 
The following VBA code will do what you want

Sub STots()
Dim Rw 'Row being evaluated
Dim Var 'The value in column A
Dim Srw 'the 1st row containing VAR
Dim Stot 'sub-total of values in column B
Rw = 1: Srw = 1
Var = Cells(Rw, 1)
Do
Do
Stot = Stot + Cells(Rw, 2)
Rw = Rw + 1
Loop While Cells(Rw, 1) = Var
Cells(Srw, 3) = Stot
Stot = 0: Srw = Rw: Var = Cells(Rw, 1)
Loop Until Cells(Rw, 1) = ""
End Sub

The above assumes that your data list starts in cell A1.
If not, adjust as necessary.

regards,
Don
 
If your data starts in row 1, then the formula for C1 would be:
=SUMIF(A:A,"="&A1,B:B)

Then in subsequent rows, use:
=IF(A2=A1,"",SUMIF(A:A,"="&A2,B:B))
 
I should have said:

In row 2 enter
=IF(A2=A1,"",SUMIF(A:A,"="&A2,B:B))

and copy it down.
 
Back
Top