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:
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.
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.
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!
Get the latest Excel tips and tricks by joining the newsletter!
Andrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it. You can read more of his writing on his personal blog at NapkinMath.io.
Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!
Other posts in this series...
- VLOOKUP with Multiple Values or Criteria Using INDEX and MATCH (How To)
- Faster Multiple Criteria Lookups with VLOOKUP and CONCATENATE
- Extract a List of Values Filtered by Criteria with Sub-Arrays
- How to Combine (Concatenate) Data from Multiple Rows into One Cell
- How to Unpivot Columns into Rows in Excel Using Power Query