Stephen has attended:
Excel Intermediate course
Excel Advanced course
Excel - List of multiple dates , then match latest to other tab
Hi
A new 'conundrum' if you guys can assist ?
I have a list of codes , with multiple 'Effective Dates' against each code . Some codes have multiple effective dates , what I need to do is somehow (pivot?)the data so I can extract the last applicable effective date first .
I then need to match that code / effective date against another sheet ?
Example below , so for DY , I need to first take the last effective date , 02Dec16 , and disregard all the others
tab [Code History]
CODE EFFECTIVE DATE
DY 24NOV16
DY 25NOV16
DY 28NOV16
DY 29NOV16
DY 30NOV16
DY 01DEC16
DY 02DEC16
E9 23OCT15
E9 08JAN16
E9 11JAN16
E9 11FEB16
E9 22APR16
E9 23MAY16
GN 17FEB15
HE 26OCT15
HE 10MAR16
HJ 26OCT15
Then I need to match off this data , against another tab 'Index Rates' , which has a 'Swing Code' column , but I then need to add in the effective date
Something like the below
TAB [Index Rates]
Swing Code Effective Date
DY Needs to match / add
E9 ie as below
GN 17FEB15
HE 10MAR16
HJ 26OCT15
This is for 1000s of rows of data , so it is essentially a latest date compression exercise first , followed by the MATCH / add to the 2nd sheet .
tks in advance
Stephen
RE: Excel - List of multiple dates , then match latest to other
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
