Excel Pivot Table Display Data Custom

axessJosh
axessJosh used Ask the Experts™
on
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$
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
gowflow
Rob HensonFinance Analyst
Commented:
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:

Page
Rows
Columns
Values

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.

Thanks
Rob H

Author

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

Do I need to condense that all to 1 sheet before proceeding?
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Rob HensonFinance Analyst

Commented:
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.

Thanks
Rob

Author

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?
Finance Analyst
Commented:
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.

Author

Commented:
You just saved me hours of time.  Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial