In this article we would learn how to combine data from multiple rows into one single string in Excel There is no specific function in Excel to concatenate data from multiple rows into one field. However, we can do this by using IF() and concatenate functions.
Let’s figure out what we actually want to do by below given data set.
Dataset that we have:
Data we want:
Step 1: As we need to group data by column A so we need to sort data by column A.
Step 2: Enter the following formula in column C at C2 cell and drag this to the end of data range in column C. This will prepare data in comma separated format, if you want to use any other data separator instead of comma, enter the same in formula.
=IF(A2<>A1,B2,C1&”,”&B2)
Step 3: Enter the following formula in column D at D2 cell and drag this to the end of data range in column D. It will find final rows.
=A2<>A3
Step 4: Copy data in column C:D and paste special as values. You will get this.
Step 5: Now apply the auto filter and filter FALSE in column D. Delete the rows having FALSE. Now remaining data from Column A to C is exactly what we require finally.
Final Group by Data in Excel
