Code to consolidate table/Range after adding totals?

[Coyotex3]

Good afternoon guys, hoping you guys could bail me out once again.

I will have a table/Range in columns A:C. Sometimes the table will have duplicate accounts such as rows 4:8, 15:20, 21:22, 24:34, 35:38 below. Ideally I would like for this macro to only keep 1 row for each account, but to give me the grand sum.

Hopefully this illustration below explains things a bit better.


I will have a table/Range similar to this one(This will always be the format, but the length may vary):

Building1
Repairs	1	50.00
Windows	2	10.00
Light	3	5.00
Light	3	7.00
Light	3	9.00
Light	3	10.00
Light	3	12.00
Parking	4	200.00
Water	5	300.00
Sewage	6	400.00
Drainage	7	50.00
Misc fee	8	60.00
Security	9	70.00
Weather	10	80.00
Weather	10	80.00
Weather	10	85.00
Weather	10	85.00
Weather	10	85.00
Weather	10	85.00
Cell	11	7.00
Cell	11	6.00
Mail	12	15.00
Auto	13	25.00
Auto	13	20.00
Auto	13	20.00
Auto	13	20.00
Auto	13	20.00
Auto	13	20.00
Auto	13	20.00
Auto	13	20.00
Auto	13	20.00
Auto	13	20.00
Auto	13	20.00
Marketing	14	55.00
Marketing	14	55.00
Marketing	14	55.00
Marketing	14	55.00
Banks	15	5,000.00
management	16	500.00
fees	17	100.00

Ideally I would like for the end result to look like this :

Building1
Repairs	1​	50​
Windows	2​	10​
Light	3​	43​
Parking	4​	200​
Water	5​	300​
Sewage	6​	400​
Drainage	7​	50​
Misc fee	8​	60​
Security	9​	70​
Weather	10​	500​
Cell	11​	13​
Mail	12​	15​
Auto	13​	225​
Marketing	14​	220​
Banks	15​	5000​
Management	16​	500​
fees	17​	100​

Sorry for not providing an example using XL2BB(Work Computer)

 

[JEC]

With a dictionary

Sub jec()
 Dim ar, a, i As Long
 ar = Cells(1).CurrentRegion
 
 With CreateObject("scripting.dictionary")
   For i = 2 To UBound(ar)
     If Not .exists(ar(i, 1)) Then
       .Item(ar(i, 1)) = Array(ar(i, 1), ar(i, 2), ar(i, 3))
     Else
       a = .Item(ar(i, 1))
       a(2) = a(2) + ar(i, 3)
       .Item(ar(i, 1)) = a
     End If
   Next
   Cells(1, 8).Resize(.Count, 3) = Application.Index(.items, 0, 0)
 End With
End Sub

It's easier if you don't need column 2

Sub jec()
 Dim ar, i As Long
 ar = Cells(1).CurrentRegion
 
 With CreateObject("scripting.dictionary")
   For i = 2 To UBound(ar)
     .Item(ar(i, 1)) = .Item(ar(i, 1)) + ar(i, 3)
   Next
   Cells(1, 8).Resize(.Count, 2) = Application.Transpose(Array(.keys, .items))
 End With
End Sub

 

[Coyotex3]

With a dictionary

Sub jec()
 Dim ar, a, i As Long
 ar = Cells(1).CurrentRegion
 
 With CreateObject("scripting.dictionary")
   For i = 2 To UBound(ar)
     If Not .exists(ar(i, 1)) Then
       .Item(ar(i, 1)) = Array(ar(i, 1), ar(i, 2), ar(i, 3))
     Else
       a = .Item(ar(i, 1))
       a(2) = a(2) + ar(i, 3)
       .Item(ar(i, 1)) = a
     End If
   Next
   Cells(1, 8).Resize(.Count, 3) = Application.Index(.items, 0, 0)
 End With
End Sub

It's easier if you don't need column 2

Sub jec()
 Dim ar, i As Long
 ar = Cells(1).CurrentRegion
 
 With CreateObject("scripting.dictionary")
   For i = 2 To UBound(ar)
     .Item(ar(i, 1)) = .Item(ar(i, 1)) + ar(i, 3)
   Next
   Cells(1, 8).Resize(.Count, 2) = Application.Transpose(Array(.keys, .items))
 End With
End Sub

Absolutely brilliant.

Column 2 will always have the G/L accounts and although Column 2 isn't neccessary for the second table, it helps me know which G/L account to book my journal entry into.

 

[JEC]

Ok! You have two options now

 

[Coyotex3]

Ok! You have two options now

More than I could ever ask for! Thanks again

 

[JEC]

No problem!