Macro to sum values based on condition

[Dhamo]

Hi,


I have a table with 3 columns as in the below table.


When I run a macro I need to get the sum of units based on the date for the names. See output table for your reference.


Name | Date | Units

AAAA | 1/1/13| 9.0

BBBB | 1/1/13| 8.0

AAAA | 1/1/13| 5.0

AAAA | 2/1/13| 6.0

BBBB | 2/1/13| 4.0

CCCC | 2/1/13| 8.0

AAAA | 3/1/13| 7.0

BBBB | 3/1/13| 3.0

CCCC | 3/1/13| 3.0

CCCC | 3/1/13| 5.0

DDDD | 3/1/13| 2.0

CCCC | 3/1/13| 1.0


OUTPUT:


Name | Date | Units

AAAA | 1/1/13| 14.0

BBBB | 1/1/13| 8.0

AAAA | 2/1/13| 6.0

BBBB | 2/1/13| 4.0

CCCC | 2/1/13| 8.0

AAAA | 3/1/13| 7.0

BBBB | 3/1/13| 3.0

CCCC | 3/1/13| 9.0

DDDD | 3/1/13| 2.0


Please help.

 

[Faseeh]

Hi Dhamo,


Simply create a pivot table, put Dates and Name in Row Label and Units in value. Hope that helps.


Regards,

 

[Dhamo]

Thanks Faseeh. Actually I knew this PT option and I had used this earlier for some other file.


I will be creating dynamic chart with the output data, Since I am not sure PT option will help on this.

 

[GeorgeF211]

Is the "Output" table fixed/manually populated, or do you want it to just show all unique values on its own?

 

[Dhamo]

Hi George, I want to show all unique values on its own code.

 

[Debraj]

Hi Dhamo,


Can you please try the below code..

[pre]

Sub MacroToSum()
Dim oc As Range
' Set your Destination here
Set oc = Range("G1")
DR = Cells(1).CurrentRegion
For i = 1 To UBound(DR)
If InStr(d01 & ",", "," & DR(i, 1) & "|" & DR(i, 2) & ",") = 0 Then
d01 = d01 & "," & DR(i, 1) & "|" & DR(i, 2)
End If
Next i

deb = Split(d01, ",")

For j = 2 To UBound(deb)
raj = Split(deb(j), "|")
oc.Offset(j - 1, 0) = raj(0)
oc.Offset(j - 1, 1) = CDate(raj(1))
oc.Offset(j - 1, 2) = WorksheetFunction.SumIfs(Range("C2:C" & UBound(DR)), _
Range("A2:A" & UBound(DR)), oc.Offset(j - 1, 0), _
Range("B2:B" & UBound(DR)), oc.Offset(j - 1, 1))
Next j
End Sub

[/pre]

Reards,

Deb.

 

[GFC]

I would make a helper column concatanating Column a and b, and make a sumif on this helper column