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.
- 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
- 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
- After that, the following sorted data will appear.
- 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)
- After that, you have to enter the formula and drag it down all the way to Formula 1 . Then the following table will appear.
- 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.
- After that, enter the formula and drag it down all the way to copy the formula in Formula 2
- After that, You have to filter Formula 2 column and select the filter option 0 like below.
- 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.
- First You have to follow Insert >> Table>>PivotTable
- Next, a Dialog Box will appear where you will have to select the data range in the Table/ Range
- Then the following new sheet will appear.
- 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.
- 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
- After entering the formula and dragging up it all the way to the Combined column the following result will appear.
- 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)),””),””)
- 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.
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!
I just figured it out by myself!
Thanks again. Have a good day!