Google Sheets doesn’t have an analogous Consolidate feature, but you can consolidate data manually or by using Paste Special as explained here. The result is total sales summarized by product, starting from cell E1.Ĭombine and Sum Duplicate Rows in Google Sheets If the data set doesn’t have a header row, you don’t need to select Top row. In the Consolidate window, check Top row, and Left column and click OK.Select the data range you want to consolidate (e.g., B1:C17), and press ENTER.In the Consolidate window, leave the default Function ( Sum), and click on the Reference icon to select the range for consolidation.Select the cell where you want to get a new data range (e.g., E1), and in the Ribbon, go to Data > Consolidate.In the following example, you have products in Column B (including duplicates), and their sales amounts in Column C.Ĭombine the data and get a total sales amount for every product in a separate range, next to the original one. Here you pick the combination of the main columns with duplicates, which is. If you prefer to keep the original table, choose to create a 'backup copy', and follow to the next step. You will see how it selects the entire range but you can change right in this field or by using the icon next to it.

For example, if you have sales by product in a sheet (as shown below), you may want to summarize sales for every product. Go to Ablebits Data tab and click Combine Rows. In Excel, there is often a need to combine duplicate rows in a range and sum them in a separate column.

If Not nRng Is Nothing Then tutorial demonstrates how to combine duplicate rows by summing them in Excel. Load the Excel worksheet file containing the duplicate rows you need to remove. If Not nRng Is Nothing Then Įnd WithĮnd SubRegards Mick Step 3: Now, select the rows we want to combine. Step 2: After clicking the anchor, get the Clipboard panel.

If nRng Is Nothing Then Set nRng = _ĭn Else Set nRng = Union(nRng, Dn) We can easily merge the rows utilizing the Clipboard tool in Excel.

With CreateObject('scripting.dictionary')įor Each Dn In Rng Set Rng = Range(Range('A2'), Range('A' & Rows.Count).End(xlUp)) With Sheets.Add().Cells(1).Resize(n, UBound(a, 2))Īgain, any help would really be appreciatedĭim Rng As Range, Dn As Range, n As Long, nRng As Range With CreateObject('Scripting.Dictionary')įor i = 2 To UBound(a, 1) Dim a, i As Long, ii As Long, n As LongĪ = Sheets('STO REV').Cells(1).CurrentRegion.Value

How do i merge duplicate rows in excel