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

We’ll use the following dataset consisting of 3 columns named Author, Books, Price, and 9 rows contained in the Cell range B4:D12. We’ll merge rows where Book Names and Prices will be combined based on the criteria Author.

Merge Rows in Excel Based on Criteria


Method 1 – Using the Consolidate Option

STEPS:

  • Select Data > Data Tools > Consolidate.

Use Consolidate Option to Merge Duplicate Values in Excel

  • The Consolidate dialog box will appear.
  • Select the whole data range as Reference.
  • Click Add to insert the range in the All references box.

Use Consolidate Option to Merge Duplicate Values in Excel

  • Check Left Column.
  • Press OK.

  • The duplicate cells of the Author column will be merged and their respective Price values will be summed.

Now let’s calculate the Number of Books per Author, as opposed to the Sum of their Prices.

STEPS:

  • Select Count Numbers from the Function list in the Consolidate dialogue box.

  • Press OK.
  • The Number of Books per Author are displayed.

Read More: How to Combine Rows with Same ID in Excel


Method 2 – Combine TEXTJOIN and IF Functions

STEPS:

  • Select the Author column in the dataset.
  • Go to Home > Editing > Sort & Filter > Sort A to Z.

Combine TEXTJOIN & IF Functions to Merge Same Texts Based on Criteria

  • Select Expand the selection and click on Sort in the Sort Warning window that opens.

  • The following sorted data is returned:

  • Insert the Author names in the range E5:E8 under the Criteria column.

  • Enter the following formula in Cell F5:
=TEXTJOIN(", ",TRUE,IF($B$5:$B$12=E5,$C$5:$C$12,""))

Combine TEXTJOIN & IF Functions to Merge Same Texts Based on Criteria

The TEXTJOIN function combines the texts from the range C5:C12. Then, the IF function states the condition where $B$5:$B$12=E5 and the TRUE function determines that the condition is true.

  • Press Enter.
  • Drag the Fill Handle down to Autofill the other cells in the column.
  • Merged rows of Book names per Author are returned.

Merge Rows in Excel Based on Criteria

Read More: Excel Merge Rows with Same Value


Method 3 – Using a Pivot Table

We’ll merge rows by Author and get the sum of the Prices of their Books.

STEPS:

  • Go to Insert, then go to PivotTable and select From Table/Range.

Use Pivot Table to Merge Rows Based on Criteria

  • The PivotTable from table or range dialog box will appear.
  • The selected range is visible in the Table/Range box.
  • Select New Worksheet as the location for the Pivot Table.

  • Click OK, and you will be directed to a new worksheet.
  • Drag Author to the Rows field and Price to the Values field.

  • The following table will appear where the same rows have been merged in the Author column, and the Prices have been tallied.


Method 4 – Using the COUNTIF Function

We will join all of the Books with respect to each Author.

STEPS:

  • Insert this formula in cell E5:
=B5&" "&COUNTIF($B$5:$B5,B5)

Insert COUNTIF Function to Merge Rows in Excel

The COUNTIF function counts cells based on the condition given in cell B5.

  • Drag the Fill Handle down to fill the rest of the cells in the Combined column.

  • Enter the following function in Cell F5:
=IF($B5&" "&1=E5, INDEX(C4:C12,MATCH($B5&" "&1,E4:E12,0))&" "&IFERROR(INDEX(C4:C12,MATCH($B5&" "&2,E4:E12,0)),"")&" "&IFERROR(INDEX(C4:C12,MATCH($B5&" "&3,E4:E12,0)),"")&" "&IFERROR(INDEX(C4:C12,MATCH($B5&" "&4,E4:E12,0)),""),"")

Insert COUNTIF Function to Merge Rows in Excel

Formula Breakdown

  • $B5&” “&1=E5: We state the condition – B5=E5.
  • IFERROR(INDEX(C4:C12,MATCH($B5&” “&2,E4:E12,0)): The INDEX function returns the value of B5 among cell range C4:C12 and the MATCH function matches it with the range E4:E12. The last argument 0 specifies an exact match. Lastly, the IFERROR function finds and omits any errors. This part of the formula is used 3 times because we found Jane Austen 3 times using the criteria “Author“.
  • &” “&: The Ampersand (&) and Quotation Marks (“ “) with a Space create spaces in each text output.
  • Press Enter.
  • The first value of the merged rows is returned.

  • Use Autofill to return all the Merged Book Names as follows:

Read More:How to Merge Rows with Comma in Excel


Download the Practice Workbook


Further Readings


<< Go Back to Merge Rows in Excel  | Merge in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

3 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!

    • Hello EXCELLEARNER, Thank you so much for your compliment. Hope you will stay with our site Exceldemy always.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo