axessJosh
asked on
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$
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$
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The data is currently in 12 spreadsheets. 1 for each month.
Do I need to condense that all to 1 sheet before proceeding?
Do I need to condense that all to 1 sheet before proceeding?
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
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
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You just saved me hours of time. Thanks!
gowflow