Excel Pivot Table Display Data Custom

Not really sure where to look and pretty new to Pivot Tables but here goes.

This is how the data lays out in our spreadsheet.

Name | Address | Date | Check # | Amount

I need to create a pivot table that lays out as follows.  There are some fields where we don't have info (i've showed those with null)

is this possible?

Name      | Address      | Date 1      | Date 2       | Date 3
person 1  | address 1  | amount$  | Amount$ | Amount$
person 2  | address 2  | amount$  | null           | null
person 3  | null             | null            | null          | Amount$
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Can you post a sample workbook that contains a sample of your data then it is no sweat to move it from that point so the solution is meaningful to you.
Rob HensonFinance AnalystCommented:
Follow these steps to create a Pivot Table:

Select the data table and choose Insert Pivot on Insert tab
This will start the wizard and go through various steps, the most important being ensuring the data selected is correct and the placement of the Pivot.

When the Wizard finishes you will have an empty Pivot table image on the sheet with a list of fields available in a list on the right hand side. At the bottom of that list there will be four areas for the four elements of the Pivot table:


From the list at the top drag the fields as follows:

Name to Rows
Address to Rows
Date to Columns
Amount to Values

Once you get that far let us know what tweaks are needed to fully solve the issue.

Rob H
axessJoshAuthor Commented:
The data is currently in 12 spreadsheets.  1 for each month.

Do I need to condense that all to 1 sheet before proceeding?
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Rob HensonFinance AnalystCommented:
Ideally the data would be on one sheet but I know the more recent versions of Excel can do the consolidation bit for you but I am not up to speed with these yet.

I would end up going about it "longhand". Create a pivot for each month and then create a summary pulling data from each pivot using the GETPIVOTDATA function. The Pivot for each month can be on the same sheet as the data if you want if you know that the data is not going to expand into more columns. Alternatively, the 12 pivots can all be on one sheet if the number of columns for each pivot is not going to increase.

axessJoshAuthor Commented:
yea, I can make the data available which is fine.

I think the bigger issue I'm running into is how it is laying out, especially since I eventually need to mail merge this into a letter.

I have setup the mail merge for a single worksheet and setup as you suggested.  That works great.

However, the issue is in the address, and this solution will be HUGE!

Right now, with how you suggested, it shows like this:

a5 Adams, Smith
a6 his address
a7 Anderson, Kirsten
a8 her address

When i try to mail merge that, I cannot single out the address.

I think I need the address to show up in Column B.

           A                        B                       C                D...
5 Adams, Smith   His Address         Date 1      date 2
6 Anderson            her address

Am I pushing my luck?
Rob HensonFinance AnalystCommented:
Right click on pivot and choose Pivot table options. Choose the display tab and tick the box for classic display. That will put name and adress in separate columns.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
axessJoshAuthor Commented:
You just saved me hours of time.  Thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.