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

merge rows in Excel based on criteria.xlsx

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.

dataset

 

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

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.

Dialog box of consolidate option

 

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

result of consolidate option

 

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.

count number option

 

5. After pressing OK the following table will appear.

merge rows in Excel based on criteria

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

sorting data

 

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

sorted data

  1. 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)

using logical condition

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

result of IF function

 

  1. 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.

using logical function

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

result of IF function

 

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

filtering number

 

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

merge rows in Excel based on criteria

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 

pivot table

 

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

pivot table dialog box

 

  1. Then the following new sheet will appear.

pivot table

 

  1. 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.

merge rows in Excel based on criteria

 

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

Countif function

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

result of countif function

 

  1. 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)),””),””)

using IF and IFERROR function

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

merge rows in Excel based on criteria

 

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

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

2 Comments
  1. Hi! This article has been very helpful! Method 4 is working almost perfectly for me but I have a question: how would I have to change the logical function you provide when I am working with numbers that I want to add instead of words that get concatenated in your example.
    For example your version gives me “12 1” instead of “13”.
    Maybe you can help me, I am a beginner in excel… 🙂
    Thank you very much!

  2. I just figured it out by myself!
    Thanks again. Have a good day!

Leave a reply

ExcelDemy
Logo