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.
Method 1 – Using the Consolidate Option
STEPS:
- Select Data > Data Tools > Consolidate.
- The Consolidate dialog box will appear.
- Select the whole data range as Reference.
- Click Add to insert the range in the All references box.
- 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.
- 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,""))
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.
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.
- 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)
- 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)),""),"")
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
- How to Merge Rows and Columns in Excel
- How to Combine Multiple Rows into One Cell in Excel
- How to Merge Rows Without Losing Data in Excel
- How to Merge Two Rows in Excel
- How to Convert Multiple Rows to a Single Column in Excel
- How to Convert Multiple Rows to Single Row in Excel
<< Go Back to Merge Rows in Excel | Merge in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
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!
Hello EXCELLEARNER, Thank you so much for your compliment. Hope you will stay with our site Exceldemy always.