Most recent date for ID in Excel 2003 Pivot Tables

Question

I'm trying to automate a process in our office with pivot tables. I have multiple customer numbers per vin. For my process to work I need a 1:1 ratio of customer numbers to vin numbers. Only the most recent deal date is relevant for my purpose. I'm working with about 25k rows. This is where the trouble starts.

How can I get only the most recent deal date and display that customer number associated with the vin number. Any suggestions would be greatly appreciated.

-Alex

Answer 1

Try this. We're going to use your PivotTable to work out which rows you don't want, then use that to create a filter column in your data table, then use our new filter column in your PivotTable.

Firstly remove the subtotals from your VIN column so you get something like this:

Then order your DEAL DATE column descending - so the most recent date is on top for each VIN.

Now in an adjacent column (I'm putting this in F4) enter this formula. This creates a unique identifier for the row:

=IF(B4="","",B4&"-"&C4&"-"&D4)

The IF() ignores all the old deals, and for recent deals we create our concatenated identifier, composed of VIN, DEAL DATE and CUSTOMER NUMBER.

Now go to your main data table. Add a new column and concatenate the columns for VIN, DEAL DATE and CUSTOMER NUMBER (in the same way we did in our formula above).

Add another column to your data table which we'll use as a filter. Enter this formula (change the worksheet name to wherever your Pivot Table is):

=IF(ISERROR(IF(MATCH(F2,YourPivotTableWorksheet!F:F,0),"Recent Deal")),"Old Deal",IF(MATCH(F2,YourPivotTableWorksheet!F:F,0),"Recent Deal"))

Adjust your PivotTable range to add in these 2 new columns, and use your new filter column to exclude the rows you don't need.