Sum if / Group by VBA Module

[lpw0806]

Hi All,

I have a module written to pull data from one sheet (Raw Data) and into another (Opportunities). But I am having trouble with the last piece. I THINK I need to group by Column B in Raw Data - but I still want to sum by Column C in Raw Data.

Not sure if that is correct, but I was able to create what I wanted in a pivot table (example pasted below). I am trying to figure out how to apply this to my module. Any help or insight would be much appreciated!

Thank you!!

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 0ff]Raw Data info for reference:[/COLOR]
Account Name = Column B
Opportunity Name = Column C
R Type = Column M
CM Prod = Column Q


[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 0cd]Pivot table creation:[/COLOR]

[TABLE="width: 515"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Sum of CM PROD[/TD]
[TD][/TD]
[TD]R Type[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account Name[/TD]
[TD]Opportunity Name[/TD]
[TD]Carryover[/TD]
[TD]New[/TD]
[TD]Renewal[/TD]
[/TR]
[TR]
[TD]Account 123[/TD]
[TD]Opportunity 123[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$218,446.00[/TD]
[/TR]
[TR]
[TD]Account 123b[/TD]
[TD]Opportunity 123[/TD]
[TD="align: right"]$81,548.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account 125[/TD]
[TD]Opportunity 125[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$91,536.00[/TD]
[/TR]
[TR]
[TD]Account 126[/TD]
[TD]Opportunity 126[/TD]
[TD="align: right"]$242,450.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account 127[/TD]
[TD]Opportunity 127[/TD]
[TD="align: right"]$43,630.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account 128[/TD]
[TD]Opportunity 128[/TD]
[TD][/TD]
[TD="align: right"]$62,120.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account 129[/TD]
[TD]Opportunity 129[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$105,682.00[/TD]
[/TR]
</tbody>[/TABLE]


[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 0ff]Module currently built:[/COLOR]

Sub MoveDataToOpportunitiesSheet()
Dim rowCount, count, destinationCount, probability As Integer
Dim selectedPOD, rowPOD, rType As String
Set sourceSheet = ActiveWorkbook.Sheets("Raw Data")
Set currentSheet = ActiveWorkbook.Sheets("Opportunities")
' Clear Data from Sheet
currentSheet.Rows("6:" & currentSheet.Rows.count).ClearContents
' get the total rows present
rowCount = sourceSheet.UsedRange.Row + sourceSheet.UsedRange.Rows.count - 1
count = 2
destinationCount = 6
selectedPOD = cmbPOD2.Value
For i = count To rowCount
probability = sourceSheet.Cells(i, 11).Value
rowPOD = sourceSheet.Cells(i, 19).Value
rType = sourceSheet.Cells(i, 13).Value
If probability >= 75 And probability <= 100 And rowPOD = selectedPOD Then
currentSheet.Cells(destinationCount, 1).Value = sourceSheet.Cells(i, 2).Value
currentSheet.Cells(destinationCount, 2).Value = sourceSheet.Cells(i, 3).Value
currentSheet.Cells(destinationCount, 3).Value = sourceSheet.Cells(i, 1).Value
currentSheet.Cells(destinationCount, 4).Value = sourceSheet.Cells(i, 12).Value
If rType = "New" Then
currentSheet.Cells(destinationCount, 5).Value = sourceSheet.Cells(i, 17).Value
currentSheet.Cells(destinationCount, 5).NumberFormat = "$#,##0.00"
Else
currentSheet.Cells(destinationCount, 5).Value = ""
End If

If rType = "Renewal" Then
currentSheet.Cells(destinationCount, 6).Value = sourceSheet.Cells(i, 17).Value
currentSheet.Cells(destinationCount, 6).NumberFormat = "$#,##0.00"
Else
currentSheet.Cells(destinationCount, 6).Value = ""
End If

If rType = "Carryover" Then
currentSheet.Cells(destinationCount, 7).Value = sourceSheet.Cells(i, 17).Value
currentSheet.Cells(destinationCount, 7).NumberFormat = "$#,##0.00"
Else
currentSheet.Cells(destinationCount, 7).Value = ""
End If
destinationCount = destinationCount + 1
End If
Next i
Range("A6:G6").Sort key1:=Range("B6"), order1:=xlAscending, Header:=xlNo
Columns("A:G").Select
Selection.EntireColumn.AutoFit
End Sub

 

[lpw0806]

Hi All --

Is there someone who can help me answer this?

Thank you in advance!