0

enter image description here

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

  • What have you tried? Can you post an image (with anonymised data of course) to imgur.com and paste the link? – Andi Mohr Oct 12 '15 at 15:37
  • @AndiMohr I've tried adding a calculated item but I'm getting an error "References to multiple item names per field cannot be included in PivotTable formulas". I edited my post to include an image. – Alexander John Oct 12 '15 at 15:51
0

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:

enter image description here

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

enter image description here

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)

enter image description here

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"))

enter image description here

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.

enter image description here

| improve this answer | |
  • Just remembered you're on Excel 2003 and IFERROR() won't work for you. I've adjusted the formula in the data table accordingly. – Andi Mohr Oct 12 '15 at 16:30
  • Brilliant work around. It works perfectly with my data. – Alexander John Oct 12 '15 at 18:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.