How to quickly condense rows based on same value in Excel?
Condense rows based on value with PivotTable
Condense rows based on value with Kutools for Excel
Condense rows based on value with formulas
Condense rows based on value with PivotTable
In Excel, there is a function – PivotTable, you can use it to quickly condense rows based on value and calculate values in other column.
1. Select the data range you want to work with, and click "Insert" > "PivotTable". Now the "Create PivotTable" dialog pops out, please check "New Worksheet" or "Existing Worksheet" option as you need in the "Choose where you want the PivotTable report to be placed" section, and click "OK". See screenshot:
2. Then drag the "Order" from the "Field" list to the "Rows" section, and drag the "Sales" field to the "Values" section in the "PivotTable Fields" pane, and the PivotTable has been created. See screenshot:
Tip: If you want to do other calculations in Sale field, you can click at down arrow beside "Sum of Sales" in the "Values" section, and choose "Value Field Settings" to change the calculation in the prompt dialog. See screenshot:
Condense rows based on value with Kutools for Excel
"Kutools for Excel" offers a powerful solution to quickly condense rows based on identical values, saving you time and effort. With just a few clicks, you can combine duplicate rows, summarize data, and streamline your spreadsheets like never before. Whether you're managing large datasets or simplifying reports, "Kutools for Excel" makes the process seamless and efficient.
1. Select the data range you want to condense, and click "Kutools" > "Merge & Split" > "Advanced Combine Rows".
2. In the prompt "Advanced Combine Rows" dialog, specify the following operations:
- Click the column header ( the column includes duplicate values), and choose "Primary Key" to mark it as key column,
- Then click the column header ( the column you do calculation) and click the drop down from the "Operation" field, and choose one calculation you need from the list under the "Calculate" section.
- At last, click "OK" button.
3. Now, the select range has been condensed rows based on values and do calculation. See screenshot:
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now
Condense rows based on value with formulas
Also, here are some formulas can help you to condense rows based on the same values.
1. Select a blank cell next to the data range you use, D2 for instance, and enter this formula, and press "Shift" + "Ctrl" + "Enter" to get to correct result.
=INDEX($A$2:$A$12,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$12),0))
Note: In this formula, "A2:A12" is the column list you want to condense based on its duplicates, "D1" is the first cell of the column you apply the formula. You can change them as you need.
2. Then click D2, the formula cell, and drag the autofill handle down until an error value displays. See screenshot:
3. Delete the error values and go to next cell to the first formula you use, E2 for instance, enter the following formula, press "Enter" to get the result, and then drag fill handle down to apply this formula to cells.
=SUMIF($A$2:$A$12,D2,$B$2:$B$12)
Note: In this formula, "A2:A12" is the list you want to condense based on, and "D2" is the cell you apply the first formula, "B2:B12" is the list you want to sum up based on duplicates. You can change them as you need.
Summary
When working with Excel data, merging rows based on the same values is a common yet crucial task. Depending on your needs and tools, you can choose from several methods to achieve this goal:
- Using PivotTables: PivotTables are a powerful tool, ideal for quickly summarizing and analyzing large datasets. They automatically merge rows with the same values and offer flexible summarization options, making them perfect for scenarios requiring dynamic data analysis.
- Using Kutools for Excel: Kutools provides a simple and efficient solution, especially for users who need to quickly merge rows while maintaining data integrity. Its intuitive interface and rich features make data processing easier and more efficient.
- Using Formulas: For users who need to merge data without altering the data structure, using formulas like SUMIF is a flexible option. Although it requires manual setup, formulas offer a high degree of customization, making them suitable for handling complex data merging tasks.
Regardless of the method you choose, you can significantly improve the efficiency and accuracy of your data processing. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials to help you master Excel.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in