Unique records' Sums

[iknowu99]

Column C is product name and Column D to Column G the products associated sales figures

the problem is these products repeat throughout C - i plan to have a unique Column C with summed values for each product

help is appreciated

 

[Smitty]

Would SUMIF() work?

Or a Pivot table?

Smitty

 

[iknowu99]

not sure, i was thinking VBA

how would sumif work?

 

[Smitty]

Something like this:

Book4
	A	B	C	D
1	Product	Revenue	Product	Total
2	Apples	$1	Berries	$26
3	Berries	$2
4	Orange	$3
5	Apples	$4
6	Berries	$5
7	Orange	$6
8	Apples	$7
9	Berries	$8
10	Orange	$9
11	Apples	$10
12	Berries	$11
13	Orange	$12
Sheet1

Smitty

 

[jindon]

Hi
it will display the result in col J:N

Sub test()
Dim dic As Object, a, i As Long, w(), n As Integer, y
Set dic = CreateObject("Scripting.dictionary")
dic.CompareMode = vbTextcompare
a = Range("c1",Range("c" & Rows.Count).End(xlUp)).Resize(,5).Value
For i = 1 To UBound(a,1)
   If Not IsEmpty(a(i,1)) Then
      If Not dic.exists(a(i,1)) Then
         ReDim w(1 To 5)
         w(1) = a(i,1)
         For ii = 2 To 5
            w(ii) = a(i,ii)
         Next
         dic.add a(i,1), w
      Else
         w = dic(a(i,1))
         For ii = 2 To 5
            w(ii) = Application.Sum(w(ii),a(i,ii))
         Next
         dic(a(i,1)) = w
      End If
   End If
Next
y = dic.items : Set dic = Nothing : Erase a
With Range("j1")
   For i = 0 To UBound(y)
      .Offset(i).Resize(,UBound(y(i)+1).Value = y(i)
   Next
End With
end Sub

 

[iknowu99]

sweeet