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: https://www.extendoffice.com/documents/excel/3153-excel-concatenate-if-same-value.html)
Note: To use the below formulas successfully, the same values in column A must be continuous.
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.
Then select cell C2, and drag the Fill Handle down to cells you need to concatenate.
Enter formula =IF(A2<>A3,CONCATENATE(A2,",""",C2,""""),"") into cell D2, and drag Fill Handle down to the rest cells.
Select cell D1, and click Data > Filter. See screenshot:
Click the drop-down arrow in cell D1, uncheck the (Blanks) box, and then click the OK button.
You can see the cells are concatenated if the first column values are same.