How to Combine (Concatenate) Data from Multiple Rows into One Cell

Cleaning Up the Helper Columns and Finalizing the List

Now we have everything we need to make our final comma-separated list, but first we need to clean up our helper columns.

The Models and Check columns are currently dependent upon the order of the rows, so we need to lock the formula output with a Paste Values command. Paste Values replaces the formulas in each cell with the current output of the formulas.

To do so, select columns C and D and press CTRL+C to copy the cell contents.

Then click the Paste button from the Home menu tab and choose Paste Values as shown below:

Paste Values

Now we can safely re-sort our data to identify the complete lists. Select all of the columns from A to D and choose Sort from the Data menu tab.

Data Post Sort

Choose to sort first by the Check column (sorting in Z to A order to bring the Last values to the top), and then by Make using the normal sort direction. When we click OK, the rows marked “Last” will rise to the top.

Now, we can safely delete the Model column, leaving only the Models. Select column B, right-click the selection, and click Delete from the menu.

Delete Column

We can also eliminate all the partial lists that aren’t marked as “Last”. Select rows 44 (in our example data set) to the end of the worksheet, right click the selection and click Delete from the menu.

What we are left with is a single row of comma-separated Models for each Make of vehicle!

Pages: 1 2 3 4 5