Building the List Check Helper Column
Now we have a set of lists for each Make, but we need to know which one has all the Model items inside. It will always be the one in the last row of each Make, but how do we look for this? Using another helper column, of course!
In the next available column (column D in our example), title the second helper column Check. This is where we will find out which lists are complete.
In the first row of data (D2 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 next row. If they are the different, it marks the cell as “Last” to indicate that it is the complete list for that Make. If they are the same, it leaves the cell blank.
Once the formula is entered, select its cell and drag from the lower right corner down to the end of the worksheet. This will again copy the formula down the sheet, changing the cell references inside the formula along the way.
The end result will be a column of blank cells with the last (complete) row of each Make marked “Last“.
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