Home How to merge and sum using excel vba
Reply: 0

How to merge and sum using excel vba

user9007 Published in September 19, 2018, 9:32 am

Im trying to create vba to merge the text value and sum the amount. For example I have below data

Date         Name            Type       Amount
1 Jan        Test1            A           100
1 Jan        Test1            A           150
2 Jan        Test1            A           110
2 Jan        Test1            A           200
1 Jan        Test1            B           130
1 Jan        Test1            B           110
1 Jan        Test2            B           130
1 Jan        Test2            B           160
1 Jan        Test3            B           180

The result should look like this

Date         Name            Type       Amount
1 Jan        Test1            A           250
2 Jan        Test1            A           310
1 Jan        Test1            B           240
1 Jan        Test2            B           290
1 Jan        Test3            B           180

I have try to use below vba but does not seem to work How to SUM / merge similar rows in Excel using VBA?

share|improve this question
  • 1
    why use vba if built-in features will be better? also you need to include code indicating what you tried and what doesn't work – ashleedawg Apr 17 at 4:40
  • 2
    @Ray I suggest you use a PivotTable. Google will tell you everything you need to know about how to set them up, and how to use them. – jeffreyweir Apr 17 at 5:17
  • I have tried using pivot but i cant get the exact result. And I also need to automate it. – Ray Apr 17 at 6:20
  • A pivot table is semi-automatic. All you need to do afterwards is to right-click the pivot-table and select "refresh". If that's not sufficiently automated or you need another method then you can also consider using PowerQuery M. If you want to automate the "refresh" process then you can do so using VBA (with macro recorder). If this still doesn't provide the desired outcome then please elaborate. – Ralph Apr 17 at 7:33
  • @Ray, just record a macro whilst doing the following: 1. copy the columns Date, Name and Type into a new column range, and remove duplicates over them. Then in the column to the right enter a sumifs() formula and drag it down by double clicking on the crosshairs. That should give you a pretty decent idea where to start writing your VBA. – Luuklag Apr 17 at 8:21

1 Answer 1

active oldest votes
up vote 0 down vote
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.321789 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO