How to Merge Rows in Excel Based on Criteria (Easiest Ways)

If You are looking for some easiest ways to merge rows in Excel based on criteria, then You are in the right place. Here I will try to show the ways to merge rows in Excel based on criteria.

Download Excel Workbook

Easiest Methods to Merge Rows in Excel Based on Criteria

Let’s get into the data table first. Here, I have taken a dataset consisting of 3 columns named Author, Books, Price(Tk), and 9 rows. Sometimes based on criteria multiple rows will be merged here using different types of functions.

Method-1: Using Consolidate Option to merge duplicate values

Suppose you have to merge rows on the basis of the same authors and then with respect to their name the sum of the price of their book will be added.

1. To fulfill this criterion you have to first follow the procedure Data tab>>Data Tools>>Consolidate

2.  Then a Consolidate dialog box will appear where You have to select the whole data range as Reference and then click on Add on All references and then click on Left Column and finally press OK.

3.  After that, the duplicate cells of the Author column will be merged and their respective Price(Tk) values will be added.

In the above scenario, I have tried to use the SUM function of the Consolidate option. But based on other criteria like counting the number of repetitions of the author names against the merged author name.

4. To solve this you have to just select the Count Numbers function as below.

5. After pressing OK the following table will appear.

Read more: Merge Duplicate Rows in Excel

Method-2: Using Logical Function to merge same texts

1. Here, suppose another criteria is to add all the books in a cell with respect to the same author. To solve this problem I will use the IF function here. At first, you have to select the Author column and then follow the Home tab >> Editing>> Sort & Filter>> Sort A to Z

2. After that, the following sorted data will appear.

3. Then, use the IF function as shown below

=IF( logical test, value if true, value if false)

Here, the logical test is  B3=B4 (to check if the writers are the same)

value if true is to join the books of all the same writers

value if false is to show up the book name for a writer which is not duplicated further.

=IF(B3=B4, E3& “” &C4,C4)

4. After that, you have to enter the formula and drag it down all the way to Formula 1 . Then the following table will appear.

5. Then the IF function is used again in the Formula 2

Here, =IF(B5<>B4,0,1)

This means if the following two cells are not equal then it will return 0 otherwise it will return 1.

6. After that, enter the formula and drag it down all the way to copy the formula in Formula 2

7. After that, You have to filter Formula 2 column and select the filter option 0 like below.

8. After pressing OK, you will get the following table where the same books will be merged against the same author as below

Method 3: Using PivotTable to merge rows

In this section, to merge rows on the basis of the same authors and then with respect to their name the sum of the price of their book the PivotTable option will be used.

1. First You have to follow  Insert >> Table>>PivotTable 

2. Next, a Dialog Box will appear where you will have to select the data range in the Table/ Range

3. Then the following new sheet will appear.

4. Then you will have to drag Author to the Rows field and Sum of Price (Tk) to the Values After that, the following table will appear where the same rows will be merged in the Author column, and with respect to the authors, the prices will be added up.

Method-4: Using COUNTIF to Merge Rows

In this section, I want to join all of the Books with respect to each Author. 

1. At first, you have to use the following function. Here, AMPERSAND and COUNTIF have been used.
   =B4&” “&COUNTIF($B$4:$B4,B4)
   =COUNTIF(criteria range, criteria)
   Here & will add these two Texts and Space

2. After entering the formula and dragging up it all the way to the Combined column the following result will appear.

3. After that you have to use the following logical function.
   =IF($B4&” “&1=E4, INDEX(C:C,MATCH($B4&” “&1,E:E,0))&” “&IFERROR(INDEX(C:C,MATCH($B4&” “&2,E:E,0)),””)&” “&IFERROR(INDEX(C:C,MATCH($B4&” “&3,E:E,0)),””)&” “&IFERROR(INDEX(C:C,MATCH($B4&” “&4,E:E,0)),””),””)

4. After entering the function and dragging it through the Merged row of Books name column the following merged rows on this criterion will appear.

Conclusion:

Hope that this article will help you to merge rows based on criteria easily. These are the easiest ways I tried to cover. Feel free to comment if anything seems difficult to understand. If You have any other ways feel free to share with us.

Further Readings

• How to Merge Rows and Columns in Excel (2 Ways)
• Excel Merge Rows with Same Value (4 Ways)
• How to Combine Rows with Same ID in Excel (3 Quick Methods)
• How to Combine Multiple Rows into One Cell in Excel
• Combine Duplicate Rows and Sum the Values in Excel