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.

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.

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)

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)),""),"")

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:

Download the Practice Workbook

Merge Rows Based on Criteria.xlsx

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

Method 1 – Using the Consolidate Option

Method 2 – Combine TEXTJOIN and IF Functions

Method 3 – Using a Pivot Table

Method 4 – Using the COUNTIF Function

Further Readings

Leave a reply Cancel reply