Merging Subjects in Multiple Rows to One Row (Excel Trick)

With the below formulas, you can concatenate corresponding cell contents if another column contains same value in Excel. (source:
Note: To use the below formulas successfully, the same values in column A must be continuous.

  1. Select a blank cell besides the second column (here we select cell C2), enter formula =IF(A2<>A1,B2,C1 & "," & B2) into the formula bar, and then press the Enter key.

  2. Then select cell C2, and drag the Fill Handle down to cells you need to concatenate.

  3. Enter formula =IF(A2<>A3,CONCATENATE(A2,",""",C2,""""),"") into cell D2, and drag Fill Handle down to the rest cells.

  4. Select cell D1, and click Data > Filter. See screenshot:

  5. Click the drop-down arrow in cell D1, uncheck the (Blanks) box, and then click the OK button.

  6. You can see the cells are concatenated if the first column values are same.