How to quickly merge rows based on one column value then do some calculations in Excel?

 How to quickly merge rows based on one column value then do some calculations in Excel?

For example, you have a range of data and one column has duplicates, now you want to merge rows bases the column A (has duplicates) then do some calculations to another column based on the merged rows as screenshot shown:

In Excel, there is no quick method to merge rows based on one column value, but here I introduce the
tricks that can help you merge duplicate rows then sum or do other calculations on another column.

Original Data                     Merge and Sum

1. Press F11 + Alt keys to enable Microsoft Visual Basic for Applications window, then click Insert > Module and copy and paste below VBA code to the new Module window.

VBA: Merge rows based on one column value

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

Sub MG30Nov12() 'Updateby20150519 Dim Rng As Range Dim InputRng As Range Dim nRng As Range Set InputRng = Application.Selection Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8) Set InputRng = InputRng.Parent.Range(InputRng.Columns(1).Address) With CreateObject("scripting.dictionary") .CompareMode = vbTextCompare For Each Rng In InputRng     If Not .Exists(Rng.Value) Then         .Add Rng.Value, Rng.Offset(, 1)     Else         .Item(Rng.Value).Value = .Item(Rng.Value).Value + Rng.Offset(, 1)             If nRng Is Nothing Then                 Set nRng = Rng             Else                 Set nRng = Union(nRng, Rng)             End If     End If Next If Not nRng Is Nothing Then     nRng.EntireRow.Delete End If End With End Sub

2. Press F5 or click Run button to run the VBA, and a dialog pops out for selecting a data range to work. see screenshot:

3. After selecting a working range, click OK. Now the data has been merged by first column and sum the values in second column.

Note: This VBA code just can correctly work when merging based on the first column and sum values in the second column.