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]

	M	N	O
3	20	1025478.51 <tbody> </tbody>	C
4	53	443085.62 <tbody> </tbody>	D
5	400	489817.56 <tbody> </tbody>	C
6	500	1072210.45 <tbody> </tbody>	D
7
8		1515296.07 <tbody> </tbody><colgroup><col></colgroup>	C
9		1515296.07 <tbody> </tbody><colgroup><col></colgroup>	D
10		0 <tbody> </tbody>	T
11
12		<tbody> </tbody>
13		<tbody> </tbody>
14

<tbody>
</tbody>

 

[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 !