VBA SUMIF with conditions

[briguyUSA]

Hi,

I need assitance with a SUMIF in vba based on a condition.

This is an example of what the data is like. I have multiple of account numbers that is split up into types (apple, pear) with values.

Account Number	Type	Value
123	Apple	12
456	Apple	13
234	Apple	45
123	Apple	6
123	Apple	22
456	Apple	45
123	Apple	78
123	Pear	4
123	Pear	45
456	Pear	7
234	Pear	9
987	Pear	3

In the end, I want to get something like this. I hope to split the result into two that is separated by the Type. The problem i have is that there are similar account numbers that is both an apple type and pear type.

Account Number (Apple)	Total Value		Account Number (Pear)	Total Value
123	109		123	49
456	58		456	7
234	45		234	9
			987	3

 

[Fluff]

Roughly how many rows of data do you have?

 

[briguyUSA]

Roughly how many rows of data do you have?

I have more than 7000

 

[Fluff]

Ok how about

Sub briguyUSA()
   Dim Ary As Variant
   Dim i As Long
   Dim Dic As Object
   
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("Master")
      Ary = .Range("A2:C" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
   End With
   
   For i = 1 To UBound(Ary)
      If Not Dic.Exists(Ary(i, 2)) Then Dic.Add Ary(i, 2), CreateObject("scripting.dictionary")
      Dic(Ary(i, 2))(Ary(i, 1)) = Dic(Ary(i, 2))(Ary(i, 1)) + Ary(i, 3)
   Next i
   With Sheets("Sheet2")
      For i = 0 To Dic.Count - 1
         .Cells(1, i * 3 + 1).Value = Dic.Keys()(i)
         .Cells(2, i * 3 + 1).Resize(Dic.Items()(i).Count, 2).Value = Application.Transpose(Array(Dic.Items()(i).Keys, Dic.Items()(i).Items))
      Next i
   End With
End Sub

 

[briguyUSA]

Ok how about

Sub briguyUSA()
   Dim Ary As Variant
   Dim i As Long
   Dim Dic As Object
  
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("Master")
      Ary = .Range("A2:C" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
   End With
  
   For i = 1 To UBound(Ary)
      If Not Dic.Exists(Ary(i, 2)) Then Dic.Add Ary(i, 2), CreateObject("scripting.dictionary")
      Dic(Ary(i, 2))(Ary(i, 1)) = Dic(Ary(i, 2))(Ary(i, 1)) + Ary(i, 3)
   Next i
   With Sheets("Sheet2")
      For i = 0 To Dic.Count - 1
         .Cells(1, i * 3 + 1).Value = Dic.Keys()(i)
         .Cells(2, i * 3 + 1).Resize(Dic.Items()(i).Count, 2).Value = Application.Transpose(Array(Dic.Items()(i).Keys, Dic.Items()(i).Items))
      Next i
   End With
End Sub

I thought a SUMIF function would be enough. may i ask why you decided to use a dictionary?

 

[Fluff]

Because it's quicker & simpler than using worksheet functions in VBA.

 

[briguyUSA]

worked like a charm!! do you have any recommendations on VBA sites /materials to study from?

 

[Fluff]

You're welcome & thanks for the feedback.

Not really, I've learnt most of what I know from this site.