We help IT Professionals succeed at work.

Need to filter one record from a group of same names

Working in Excel I am trying to filter the most recent dated line. Have a spreadsheet of all my employees with the dates of pay increases. I want to only see the row with the most recent pay increase. In my attached picture it would be the row with the red lines.

Watch Question

Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

You can prepare that report with the help of Pivot Table as shown in the attached file.

To do so follow these steps...

  1. Convert your data into an Excel Table. To do so, click inside your data and press Ctrl+T, this will convert your data into an Excel Table.
  2. Now click inside the Table --> Go to Insert Tab --> Click on Pivot Table and choose a destination either a New worksheet or Existing Worksheet. In case of Existing Worksheet, choose a cell on the existing worksheet.
  3. Once the Pivot Table is inserted on desired location, follow the steps below.
  4. Drag the Name into the Rows area from the field list.
  5. Drag the Pay Increase field into the Values Area.
  6. Right click in any of the date cell in the pivot table and click on Summarize Values By and choose Max.
  7. Right click again on any date (which are shown as a number underneath the date) and click on Number Format and choose the Date number format.
  8. Drag the Pay Increase field into the Values area and follow the same step 6 to choose Summarize Values By Max and set the number format to currency.
  9. Rename the column headers in the pivot table as per your requirement.

Refer to the attached for more details.


You could also transform the list into a table Ctrl-T, and use the Date Column to filter on.  There are many options on what to filter.