Excel - List of multiple dates , then match latest to other tab

Hi Stephen

Thanks for your question.
As you suggest, your conundrum can indeed be solved with a Pivot Table.

Here are the steps if you are fairly new to Pivot Tables.

1. Select a cell in the data on the Code History tab.
2. Choose Insert, PivotTable and press OK to create a new sheet.
3. From the Pivot Table field list drag Code to Row labels and Effective Date to Values. This creates the Pivot Table.
4. You may need to format the dates. If so, Right click one one of the dates in the PivotTable and choose Number Format, custom and type dd-mmm-yy to match your example.
5. Now you need to change the value to the latest date:
Right click a date, choose Value Field Setting and choose Max.
6. Finally remove the Grand total by selecting Design, Grand Totals, Off for Rows and Columns.


Row Labels Max of EFFECTIVE DATE
DY 02-Dec-16
E9 23-May-16
GN 17-Feb-15
HE 10-Mar-16
HJ 26-Oct-15

You can change the heading to Latest Effective Date if you wish.

Hope that works for your data? If more data is added to the Code History tab then you will need to choose Options, Refresh.
Also you will need to increase the data range with Options, Change Data Source if more rows are appended to the Code History tab. (Not needed if you create the Code History list as a Table with Home, Format as Table)

Regards
Doug Dunn
STL