VBA summing values in column B grouping sums by value in column A saving values in column C

[dougkale]

I have a sheet that looks something like this:

A B
1 15
1 10
1 5
2 1
2 3
2 0

I want my result to look like:

A B C D
1 15 1 20
1 10 2 9
1 5
2 1
2 3
2 0


I do not know how many distinct values will be in column A and for each distinct value I do not know how many records will exist.

Any assistance will be greatly appreciated. I am trying scrub and balance some sheets within my group. This is the last step and while it seems simple I am not really sure how to approach it.

Thanks in advance

 

[Peter_SSs]

So, for this latest sample data
- what is the expected result?
- why is it the expected result?
- where should the result go?
- what is the significance of the rows that have the C/D in column N instead of column O?
- what is the significance of the rows that don't have C/D at all?

 

[dougkale]

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]20[/TD]
[TD][TABLE="class: cms_table, width: 64"]
<tbody>[TR]
[TD="align: right"]1025478.51[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]53[/TD]
[TD][TABLE="class: cms_table, width: 64"]
<tbody>[TR]
[TD="align: right"]443085.62[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]400[/TD]
[TD][TABLE="class: cms_table, width: 64"]
<tbody>[TR]
[TD="align: right"]489817.56[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]500[/TD]
[TD][TABLE="class: cms_table, width: 64"]
<tbody>[TR]
[TD="align: right"]1072210.45[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][TABLE="width: 74"]
<tbody>[TR]
[TD="align: right"]1515296.07[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][TABLE="width: 74"]
<tbody>[TR]
[TD="align: right"]1515296.07[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][TABLE="class: cms_table, width: 64"]
<tbody>[TR]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD]T[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][TABLE="class: cms_table, width: 64"]
<tbody>[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][TABLE="class: cms_table, width: 64"]
<tbody>[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[dougkale]

What I am trying to achieve is N8 in this case would be the sum of all the values that have C next to them (It could be any number of rows) N9 would be the sum of all values that have D next to them. N10 would be N8-N9 (Balancing Debits and credits in this case)

I am so sorry I've made this a bit confusing. Greatly appreciate your help!

 

[Peter_SSs]

Try this slight modification now that we know (I think) the start row number and which columns the values are actually in.

Sub DebitsAndCredits_v2()
  Dim d As Object
  Dim a As Variant
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  d.comparemode = 1
  d("C") = 0
  d("D") = 0
  a = Range("N3", Range("O" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    d(a(i, 2)) = Round(d(a(i, 2)) + a(i, 1), 2)
  Next i
  d("T") = Round(d("C") - d("D"), 2)
  Range("N" & Rows.Count).End(xlUp).Offset(2).Resize(3, 2).Value = Application.Transpose(Array(d.items, d.keys))
End Sub

 

[dougkale]

That worked perfectly! Thank you very much!!

 

[Peter_SSs]

That worked perfectly! Thank you very much!!

Got there at last !