Analistable

How to combine duplicate rows in Excel

By the Analistable team · Updated · 5 min read

To combine duplicate rows into one row per value, list the unique values with =UNIQUE(A2:A100) and add them up with =SUMIFS(C:C, A:A, E2) — or use a pivot table. To join the text from duplicate rows instead of adding numbers, use =TEXTJOIN(", ", TRUE, FILTER(B$2:B$100, A$2:A$100=E2)). Power Query Group By does both and refreshes.

Part of our guide: How to compare two spreadsheets for differences and matches

The example

Orders with repeated customers
CustomerProductAmount
Bakery LuneDesk120
Hart & CoLamp40
Bakery LuneChair80
Nordic SupplyDesk75
Hart & CoDesk120
Combined: one row per customer
CustomerProductsTotal
Bakery LuneDesk, Chair200
Hart & CoLamp, Desk160
Nordic SupplyDesk75

Formulas (Microsoft 365)

  1. In E2: =UNIQUE(A2:A6) lists each customer once.
  2. In F2: =TEXTJOIN(", ", TRUE, FILTER($B$2:$B$6, $A$2:$A$6=E2)) joins their products. Fill down.
  3. In G2: =SUMIFS($C$2:$C$6, $A$2:$A$6, E2) adds their amounts. Fill down.

Newer Microsoft 365 builds also have GROUPBY: =GROUPBY(A2:A6, C2:C6, SUM) returns the customers and totals in one formula.

Pivot table (any version)

Select the data, choose Insert → PivotTable, drag Customer to Rows and Amount to Values. Excel sums duplicates automatically. Pivot tables can't join text, so use TEXTJOIN or Power Query for the Products column.

Power Query Group By (repeatable, text and numbers)

Load the table with Data → From Table/Range, choose Home → Group By, select Advanced, group by Customer and add a Sum of Amount. Then edit the formula bar so the products are joined:

= Table.Group(Source, {"Customer"}, {
    {"Products", each Text.Combine([Product], ", "), type text},
    {"Total", each List.Sum([Amount]), type number}
})

Close & Load. When new orders are added to the source table, click Refresh.

Merge duplicates without losing data

Data → Remove Duplicates keeps the first row of each group and deletes the rest, including any different values in other columns. Combine first with one of the methods above, then remove the originals. To merge two separate lists and drop repeated rows, see merging two spreadsheets and removing duplicates.

Duplicates that look identical but aren't grouped usually differ by a trailing space or capitalisation. Clean the key with =TRIM(PROPER(A2)) first.

Which method should you use?

  • Need numbers only, quickly → pivot table.
  • Need text and numbers together, live → UNIQUE + TEXTJOIN + SUMIFS (Microsoft 365).
  • Same job every month, or large data → Power Query Group By.
  • Just need to delete repeats and keep one row → Data → Remove Duplicates, after checking nothing in the other columns differs.

Combine by two or more columns

Sometimes a duplicate means the same customer and the same month. Stack the key columns in UNIQUE and add a criterion per column in SUMIFS:

E2: =UNIQUE(A2:B100)
G2: =SUMIFS($C$2:$C$100, $A$2:$A$100, E2, $B$2:$B$100, F2)

UNIQUE returns two columns (E and F), one row per customer-month pair. In a pivot table, put both fields in Rows; in Power Query Group By, add both columns under Group by in the Advanced view.

Keep the first, last, largest or a count

Other ways to collapse duplicates
You wantFormula next to the UNIQUE listPower Query aggregation
Number of rows per key=COUNTIF($A$2:$A$100, E2)Count Rows
Largest amount=MAXIFS($C$2:$C$100, $A$2:$A$100, E2)Max
Latest date=MAXIFS($D$2:$D$100, $A$2:$A$100, E2)Max (on the date column)
Distinct products only=TEXTJOIN(", ", TRUE, UNIQUE(FILTER($B$2:$B$100, $A$2:$A$100=E2)))Text.Combine(List.Distinct([Product]), ", ")
First product listed=XLOOKUP(E2, $A$2:$A$100, $B$2:$B$100)List.First([Product])

MAXIFS needs Excel 2019 or later. For the distinct-products version, a customer who bought two desks shows “Desk” once instead of “Desk, Desk”.

Excel 2016 and earlier

Without UNIQUE, TEXTJOIN or FILTER, use this route: copy the key column to a new place, choose Data → Remove Duplicates on the copy to get the unique list, then add =SUMIF($A$2:$A$100, E2, $C$2:$C$100) for totals. TEXTJOIN arrived in Excel 2019, so joining text in older versions is easiest with Power Query (available as a free add-in for Excel 2010 and 2013, and built in from 2016) or the pivot table route for numbers only.

Check the result

  1. Compare the grand totals: =SUM(C2:C100) on the original data should equal =SUM(G2:G4) on the combined table. In the example both are 435.
  2. Compare the counts: =COUNTA(UNIQUE(A2:A100)) should equal the number of rows in the combined table (3 here).
  3. Spot-check one key with a filter on the original data.

If the totals differ, some amounts are probably stored as text (SUMIFS ignores them) or some keys differ by spacing, so they formed extra groups.

Troubleshooting

Problems when combining duplicates
SymptomCauseFix
Same customer appears twiceTrailing space or different caseClean with TRIM; note UNIQUE ignores case
TEXTJOIN returns #VALUE!Result is longer than 32,767 charactersJoin fewer values or use Power Query
Totals are too lowNumbers stored as textConvert with VALUE or Text to Columns
#SPILL! errorCells below the UNIQUE formula are not emptyClear the spill range

Combine duplicates every month

When a new export arrives each month with the same columns, keep the raw data in an Excel table and point the combining step at the table, not a fixed range. With Power Query, paste or load the new rows and click Data → Refresh All; the Group By step re-runs on everything. With formulas, use table references such as =UNIQUE(Orders[Customer]) and =SUMIFS(Orders[Amount], Orders[Customer], E2#); the # refers to the whole spilled list, so new customers get a total without filling formulas down. Check the grand total against the raw data after each refresh.

Frequently asked questions

How do I merge duplicate rows and sum the values in Excel?
Use a pivot table with the key in Rows and the number in Values, or =UNIQUE() with SUMIFS, or GROUPBY in newer Microsoft 365.
How do I combine duplicate rows and keep all the text?
Use =TEXTJOIN(", ", TRUE, FILTER(text_column, key_column=key)) next to a UNIQUE list of keys, or Power Query Group By with Text.Combine.
Does Remove Duplicates merge rows?
No. It keeps the first row of each duplicate group and deletes the others, so data in the deleted rows is lost.
Can I combine duplicate rows in Google Sheets?
Yes. =QUERY(A1:C, "select A, sum(C) where A is not null group by A", 1) sums by key, and TEXTJOIN with FILTER joins the text, as in Excel.
How do I count how many duplicates each value has?
Next to a UNIQUE list of keys, use =COUNTIF(A:A, E2). In a pivot table, put the key in both Rows and Values and set the value to Count.

Related guides