Thread: Pivot Problems - combine data in ONE row

Hello gurus!

I'm having a bit of an issue with a pivot table (file attached). I have data in one column with various dates (VALID ON) and another column with values (REASON CODE). I'm filtering the pivot table on reason code "ZPNG" and want to display the results (values) based on the last date in a year. The whole thing is formatted tabular. The Year goes from Oct to Sep.

What I'm trying to get is:

Last Date in 2009
Last Date in 2010
Reason code (values)

I've tried adding new columns with IF statements to clean up the tabular pivot, but I can't get this to work right without manipulating the data two or three times. And even then, Excel displays blank cells as 1/0/1900 when it should stay blank.

The purpose of this is to show me when was the last change made in 2009 and when in 2010 (Oct was the first month of the new fiscal year). Ideally I want both dates (if there are two!) to display in the same row. I can only get it to show up in two rows.

Can someone point me in the right direction please? I'm hoping there is a simpler and more elegant solution to this problem.

Thanks in advance for your help!