Code to consolidate table/Range after adding totals?

Coyotex3

Active Member
Joined
Dec 12, 2021
Messages
496
Office Version
  1. 365
Platform
  1. Windows
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
Repairs150.00
Windows210.00
Light35.00
Light37.00
Light39.00
Light310.00
Light312.00
Parking4200.00
Water5300.00
Sewage6400.00
Drainage750.00
Misc fee860.00
Security970.00
Weather1080.00
Weather1080.00
Weather1085.00
Weather1085.00
Weather1085.00
Weather1085.00
Cell117.00
Cell116.00
Mail1215.00
Auto1325.00
Auto1320.00
Auto1320.00
Auto1320.00
Auto1320.00
Auto1320.00
Auto1320.00
Auto1320.00
Auto1320.00
Auto1320.00
Auto1320.00
Marketing1455.00
Marketing1455.00
Marketing1455.00
Marketing1455.00
Banks155,000.00
management16500.00
fees17100.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)
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
With a dictionary

VBA Code:
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

VBA Code:
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
 
Upvote 0
Solution
With a dictionary

VBA Code:
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

VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top