Building the Concatenation Helper Column
To combine the rows for each make of vehicle, we are going to build a few columns that help us stitch together the multiple rows, but first we need to make sure the list is sorted. All the vehicles of the same make need to be next to each other for this technique to work.
First, we select all the cells, and choose Sort from the Data menu tab.
We want to sort first by Make and then by Model as the screen shot above shows.
Now that we are sure that the list is in the proper order, we can build our helper columns.
In the next available column (column C in our example), title the first helper column Models. This is where our combined list of vehicle models will end up.
In the first row of data (C2 in this example), write the following formula:
This IF function compares the value in the Make column of the current row to the Make cell in the previous row. If they are the same, it stitches together the contents of the previous Models cell with the new content from the current Model cell. If they are different, it starts a new list by adding the current row’s Model to the Models cell.
In other words, If the Make is the same, add this Model to the other Models in the list. If the Make is different, start a new list of Models.
Once the first formula is entered, select its cell and drag from the lower right corner down to the end of the worksheet. This will copy the formula down the sheet, changing the cell references inside the formula along the way.
The end result will be a series of lists of vehicle Models that grow with each row, until the Make changes, and a new list is started.
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.
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