How to merge and sum using excel vba

Question

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?

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
– ashleedawg, Commented Apr 17, 2018 at 4:40
@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
– jeffreyweir, Commented Apr 17, 2018 at 5:17
I have tried using pivot but i cant get the exact result. And I also need to automate it. — Ray
– Ray, Commented Apr 17, 2018 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
– Ralph, Commented Apr 17, 2018 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
– Luuklag, Commented Apr 17, 2018 at 8:21

Xabier · Accepted Answer · 2018-04-17 08:41:37Z

How about the following, this will give you the desired result by using a SumIf formula then replacing values in column D and removing duplicates:

Sub foo()
Dim ws As Worksheet: Set ws = Sheets("Sheet1")
'declare and set your worksheet, amend as required
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
'get the last row with data on Column A

ws.Range("E2").FormulaR1C1 = "=SUMIFS(C[-1],C[-4],RC[-4],C[-3],RC[-3],C[-2],RC[-2])"
'add the sumif formula
ws.Range("E2").AutoFill Destination:=Range("E2:E" & LastRow), Type:=xlFillDefault
'fill the formula down to last cell
ws.Columns("E:E").Copy
ws.Columns("E:E").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'copy and paste as values
ws.Range("D2:D" & LastRow).Delete Shift:=xlToLeft
'remove column D's values and bring values from Column E into it
ws.Range("$A$1:$G$" & LastRow).RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7), Header:=xlNo
'remove duplicates
End Sub

Collectives™ on Stack Overflow

How to merge and sum using excel vba

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related